vineri, 21 ianuarie 2022

Configure Zipkin with Mysql in Docker

If you are here, you know what Zipkin is and what it's good at, but if needed, check out its homepage OpenZipkin · A distributed tracing system.

 It was a bit of a challenge for me to persist the data with mysql, otherwise every time the docker containers exits, everything is cleared ( after all it has an in memory persistence by default ).

First of all we need the yml files to get the containers up and running. I will paste their content here

 1. docker-compose-mysql.yml

#
# Copyright 2015-2020 The OpenZipkin Authors
#
# Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except
# in compliance with the License. You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software distributed under the License
# is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express
# or implied. See the License for the specific language governing permissions and limitations under
# the License.
#

# This file uses the version 2 docker-compose file format, described here:
# https://docs.docker.com/compose/compose-file/#version-2
#
# This runs the zipkin and zipkin-mysql containers, using docker-compose's
# default networking to wire the containers together.
#
# Note that this file is meant for learning Zipkin, not production deployments.


version: '2.4'

services:
  storage:
    image: ghcr.io/openzipkin/zipkin-mysql:${TAG:-latest}
    container_name: mysql
    # Uncomment to expose the storage port for testing
    ports:
       - 3306:3306

  # Use MySQL instead of in-memory storage
  zipkin:
    extends:
      file: docker-compose.yml
      service: zipkin
    # slim doesn't include MySQL support, so switch to the larger image
    image: ghcr.io/openzipkin/zipkin:${TAG:-latest}
    environment:
      - STORAGE_TYPE=mysql
      - MYSQL_HOST=storage
      # Add the baked-in username and password for the zipkin-mysql image
      - MYSQL_USER=zipkin
      - MYSQL_PASS=zipkin
    depends_on:
      - storage

  dependencies:
    extends:
      file: docker-compose-dependencies.yml
      service: dependencies
    environment:
      - STORAGE_TYPE=mysql
      - MYSQL_HOST=storage
      # Add the baked-in username and password for the zipkin-mysql image
      - MYSQL_USER=zipkin
      - MYSQL_PASS=zipkin
    depends_on:
      - storage

2. docker-compose.yml

#
# Copyright 2015-2020 The OpenZipkin Authors
#
# Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except
# in compliance with the License. You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software distributed under the License
# is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express
# or implied. See the License for the specific language governing permissions and limitations under
# the License.
#

# This file uses the version 2 docker-compose file format, described here:
# https://docs.docker.com/compose/compose-file/#version-2
#
# This runs the zipkin slim container, using docker-compose's default networking
# to wire other containers together.
#
# Note that this file is meant for learning Zipkin, not production deployments.


version: '2.4'

services:
  # The zipkin process services the UI, and also exposes a POST endpoint that
  # instrumentation can send trace data to.

  zipkin:
    image: ghcr.io/openzipkin/zipkin-slim:${TAG:-latest}
    container_name: zipkin
    # Environment settings are defined here https://github.com/openzipkin/zipkin/blob/master/zipkin-server/README.md#environment-variables
    environment:
      - STORAGE_TYPE=mem
      # Point the zipkin at the storage backend
      - MYSQL_HOST=mysql
      # Uncomment to enable self-tracing
      # - SELF_TRACING_ENABLED=true
      # Uncomment to increase heap size
      # - JAVA_OPTS=-Xms128m -Xmx128m -XX:+ExitOnOutOfMemoryError

    ports:
      # Port used for the Zipkin UI and HTTP Api
      - 9412:9411
    # Uncomment to enable debug logging
    # command: --logging.level.zipkin2=DEBUG

 

Note: for some reason port 9411 was taken on my machine. I tried to kill the process using it but eventually it didn't work. That is why 9412 is present here. 

3. docker-compose-dependencies.yml

#
# Copyright 2015-2020 The OpenZipkin Authors
#
# Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except
# in compliance with the License. You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software distributed under the License
# is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express
# or implied. See the License for the specific language governing permissions and limitations under
# the License.
#


version: '2.4'

services:
  # Adds a cron to process spans since midnight every hour, and all spans each day
  # This data is served by http://192.168.99.100:8080/dependency
  #
  # For more details, see https://github.com/openzipkin/docker-zipkin-dependencies

  dependencies:
    image: ghcr.io/openzipkin/zipkin-dependencies
    container_name: dependencies
    entrypoint: crond -f
    # environment:
      # Uncomment to see dependency processing logs
      # - ZIPKIN_LOG_LEVEL=DEBUG
      # Uncomment to adjust memory used by the dependencies job
      # - JAVA_OPTS=-verbose:gc -Xms1G -Xmx1G

 


While having all this files in the same folder, use docker-compose to bring them up.

docker-compose -f docker-compose-mysql.yml up

We are not yet ready. The tables which will hold the data regarding our spans are not ready.

Establish a connection to the db using MySql Workbench. As noted above, user zipkin and password zipkin.

Create a new query and create the tables:


CREATE TABLE IF NOT EXISTS zipkin_spans (
`trace_id_high` BIGINT NOT NULL DEFAULT 0 COMMENT 'If non zero, this means the trace uses 128 bit traceIds instead of 64 bit',
`trace_id` BIGINT NOT NULL,
`id` BIGINT NOT NULL,
`name` VARCHAR(255) NOT NULL,
`parent_id` BIGINT,
`debug` BIT(1),
`start_ts` BIGINT COMMENT 'Span.timestamp(): epoch micros used for endTs query and to implement TTL',
`duration` BIGINT COMMENT 'Span.duration(): micros used for minDuration and maxDuration query'
) ENGINE=InnoDB CHARACTER SET=utf8 COLLATE utf8_general_ci;
ALTER TABLE zipkin_spans ADD UNIQUE KEY(trace_id_high, trace_id, id) COMMENT 'ignore insert on duplicate';

ALTER TABLE zipkin_spans ADD INDEX(trace_id_high, trace_id, id) COMMENT 'for joining with zipkin_annotations';
ALTER TABLE zipkin_spans ADD INDEX(trace_id_high, trace_id) COMMENT 'for getTracesByIds';
ALTER TABLE zipkin_spans ADD INDEX(name) COMMENT 'for getTraces and getSpanNames';
ALTER TABLE zipkin_spans ADD INDEX(start_ts) COMMENT 'for getTraces ordering and range';

CREATE TABLE IF NOT EXISTS zipkin_annotations (
trace_id_high BIGINT NOT NULL DEFAULT 0 COMMENT 'If non zero, this means the trace uses 128 bit traceIds instead of 64 bit',
trace_id BIGINT NOT NULL COMMENT 'coincides with zipkin_spans.trace_id',
span_id BIGINT NOT NULL COMMENT 'coincides with zipkin_spans.id',
a_key VARCHAR(255) NOT NULL COMMENT 'BinaryAnnotation.key or Annotation.value if type == -1',
a_value BLOB COMMENT 'BinaryAnnotation.value(), which must be smaller than 64KB',
a_type INT NOT NULL COMMENT 'BinaryAnnotation.type() or -1 if Annotation',
a_timestamp BIGINT COMMENT 'Used to implement TTL; Annotation.timestamp or zipkin_spans.timestamp',
endpoint_ipv4 INT COMMENT 'Null when Binary/Annotation.endpoint is null',
endpoint_ipv6 BINARY(16) COMMENT 'Null when Binary/Annotation.endpoint is null, or no IPv6 address',
endpoint_port SMALLINT COMMENT 'Null when Binary/Annotation.endpoint is null',
endpoint_service_name VARCHAR(255) COMMENT 'Null when Binary/Annotation.endpoint is null'
) ENGINE=InnoDB CHARACTER SET=utf8 COLLATE utf8_general_ci;


ALTER TABLE zipkin_annotations ADD UNIQUE KEY(trace_id_high, trace_id, span_id, a_key, a_timestamp) COMMENT 'Ignore insert on duplicate';
ALTER TABLE zipkin_annotations ADD INDEX(trace_id_high, trace_id, span_id) COMMENT 'for joining with zipkin_spans';
ALTER TABLE zipkin_annotations ADD INDEX(trace_id_high, trace_id) COMMENT 'for getTraces/ByIds';
ALTER TABLE zipkin_annotations ADD INDEX(endpoint_service_name) COMMENT 'for getTraces and getServiceNames';
ALTER TABLE zipkin_annotations ADD INDEX(a_type) COMMENT 'for getTraces';
ALTER TABLE zipkin_annotations ADD INDEX(a_key) COMMENT 'for getTraces';
ALTER TABLE zipkin_annotations ADD INDEX(trace_id, span_id, a_key) COMMENT 'for dependencies job';


CREATE TABLE IF NOT EXISTS zipkin_dependencies (
day DATE NOT NULL,
parent VARCHAR(255) NOT NULL,
child VARCHAR(255) NOT NULL,
call_count BIGINT
) ENGINE=InnoDB CHARACTER SET=utf8 COLLATE utf8_general_ci;

ALTER TABLE zipkin_dependencies ADD UNIQUE KEY(day, parent, child);

ALTER TABLE zipkin_dependencies add `error_count` BIGINT
ALTER TABLE zipkin_spans ADD `remote_service_name` VARCHAR(255);


Having all this, Zipkin started to work and maintain its state after restarts.

 


miercuri, 18 ianuarie 2017

AOT query date ranges

In LogisticsPostalAddress table i had to add a range on ValidFrom and ValidTo fields in order to get only the active addresses.

Having an AOT query, i did the following:



Got the addresses for a customer with a query like this:


SELECT ct.ACCOUNTNUM
, dpt.NAME
, dpl.ISPOSTALADDRESS
, dpl.ISPRIMARY
, ll.DESCRIPTION
, ll.RECID
, lpa.ADDRESS
, lpa.VALIDFROM
, lpa.VALIDTO
FROM CUSTTABLE AS ct

JOIN DIRPARTYTABLE AS dpt
ON ct.PARTY = dpt.RECID

JOIN DIRPARTYLOCATION AS dpl
ON dpl.PARTY = dpt.RECID

JOIN LOGISTICSLOCATION AS ll
ON dpl.LOCATION = ll.RECID

JOIN LOGISTICSPOSTALADDRESS AS lpa
ON lpa.LOCATION = ll.RECID

WHERE ct.ACCOUNTNUM = 'C00000010'




I've just created this address which shows up in my AX view. So, .. today() acts like less then or equal.

marți, 10 ianuarie 2017

Get SalesLines with Invoice but without associated PackingSlip

First of all, I would like to display all the sales lines with a relation in CustInvoiceTrans via InventTransId field. So .. fully or partially invoiced, doesn't matter.
 - Further more, I want to check only those invoices which were not posted from a PackingSlip and for this, i am using CustInvoicePackingSlipQuantityMatch table.

The relation is simple:


  •   CustInvoiceTrans (SourceDocumentLine
  •   CustInvoicePackingSlipQuantityMatch (InvoiceSourceDocumentLine)


and


  •  CustPackingSlipTrans(SourceDocumentLine
  •  CustInvoicePackingSlipQuantityMatch (PackingSlipSourceDocumentLine)
I have noticed one thing: if a packing slip is created first, a line is added in the CustInvoicePackingSlipQuantityMatch with a reference to that packing slip. If you continue and create the invoice it will be added in the table too. 

But if you only create the invoice, without a packing slip, no record will exist in the table regarding your invoice. 

Here is a query for this:


 SELECT sl.SALESID

, CONVERT(INT, sl.QTYORDERED) AS 'SalesQty'
, sl.INVENTTRANSID
, (SELECT cit1.INVOICEID
FROM CUSTINVOICETRANS AS cit1
WHERE cit1.INVENTTRANSID = sl.INVENTTRANSID
AND cit1.PARTITION = sl.PARTITION
AND cit1.DATAAREAID = sl.DATAAREAID) AS 'InvoiceId'
, cpst.PACKINGSLIPID
, CONVERT(INT, cpst.QTY)  AS 'PackingSlipQty'
, cipsqm.PACKINGSLIPSOURCEDOCUMENTLINE
, cipsqm.INVOICESOURCEDOCUMENTLINE
, cit.INVOICEID
FROM SALESTABLE AS st
JOIN SALESLINE AS sl
ON st.SALESID = sl.SALESID
AND st.PARTITION = sl.PARTITION
AND st.DATAAREAID = sl.DATAAREAID
LEFT JOIN CUSTPACKINGSLIPTRANS AS cpst
ON sl.INVENTTRANSID = cpst.INVENTTRANSID
AND sl.PARTITION = cpst.PARTITION
AND sl.DATAAREAID = cpst.DATAAREAID
LEFT JOIN CustInvoicePackingSlipQuantityMatch AS cipsqm
ON cpst.SOURCEDOCUMENTLINE = cipsqm.PACKINGSLIPSOURCEDOCUMENTLINE
LEFT JOIN CUSTINVOICETRANS AS cit
ON cipsqm.INVOICESOURCEDOCUMENTLINE = cit.SOURCEDOCUMENTLINE
WHERE st.CREATEDDATETIME > '2017-01-10'


And the results:



As you can see, the second sales order is there, its invoice is there but no info was added to CustInvoicePackingSlipQuantityMatch.


Let's get to X++ now :


static void salesPackingSlipsInvoicesTest(Args _args)
{
    Query localQuery;
    QueryBuildDataSource qbdsLocalSalesTable, qbdsLocalSalesLine, qbdsLocalCustInvoiceTrans, qbdsLocalCustInvoicePackingSlipQuantityMatch;
    QueryBuildRange localQbr;
    QueryRun localQueryRun;
    SalesTable salesTable;
    CustInvoiceTrans custInvoiceTrans;
    TransDate fromDate, toDate;
    utcDateTime dateToBeginValue, dateToEndValue;

    DimensionProvider dimensionProvider = new DimensionProvider();


    localQuery = new query();
    qbdsLocalSalesTable = localQuery.addDataSource(tableNum(SalesTable));

    qbdsLocalSalesLine = qbdsLocalSalesTable.addDataSource(tableNum(SalesLine));
    qbdsLocalSalesLine.joinMode(JoinMode::InnerJoin);
    qbdsLocalSalesLine.relations(true);
    //qbdsLocalSalesLine.fetchMode(QueryFetchMode::One2One);

    qbdsLocalCustInvoiceTrans = qbdsLocalSalesLine.addDataSource(tableNum(CustInvoiceTrans));
    qbdsLocalCustInvoiceTrans.joinMode(JoinMode::InnerJoin);
    qbdsLocalCustInvoiceTrans.relations(false);
    qbdsLocalCustInvoiceTrans.addLink(fieldNum(SalesLine, InventTransId),
        fieldNum(CustInvoiceTrans, InventTransId));
    //qbdsLocalCustInvoiceTrans.fetchMode(QueryFetchMode::One2One);

    //Use the junction table to go from packing lines to invoice lines
    qbdsLocalCustInvoicePackingSlipQuantityMatch = qbdsLocalCustInvoiceTrans.addDataSource(tableNum(CustInvoicePackingSlipQuantityMatch));
    qbdsLocalCustInvoicePackingSlipQuantityMatch.joinMode(JoinMode::NoExistsJoin);
    qbdsLocalCustInvoicePackingSlipQuantityMatch.relations(false);
    qbdsLocalCustInvoicePackingSlipQuantityMatch.addLink(fieldNum(CustInvoiceTrans, SourceDocumentLine),
        fieldNum(CustInvoicePackingSlipQuantityMatch, InvoiceSourceDocumentLine));
    //qbdsLocalCustInvoicePackingSlipQuantityMatch.fetchMode(QueryFetchMode::One2One);
    
    dimensionProvider.addAttributeRangeToQuery(localQuery
                  , qbdsLocalSalesLine.name()
                  , fieldStr(SalesLine, DefaultDimension)
                  , DimensionComponent::DimensionAttribute
                  , 'b2b'
                  , "CostCenter"
                  , false);

    fromDate = mkDate(9, 1, 2017);
    toDate = mkDate(10, 1, 2017);


    if (fromDate || toDate)
    {
        dateToBeginValue = datetobeginUtcDateTime(fromDate, 0);
        dateToEndValue   = datetoendUtcDateTime(toDate,  0);

        qbdsLocalSalesTable.addRange(fieldnum(SalesTable, CreatedDateTime)).value(queryRange(dateToBeginValue, dateToEndValue));
    }


    localQueryRun = new QueryRun(localQuery);

    while (localQueryRun.next())
    {
        salesTable = localQueryRun.get(tableNum(SalesTable));
        custInvoiceTrans = localQueryRun.get(tableNum(CustInvoiceTrans));
        info(strFmt("Salesid %1 - InvoiceId %2", salesTable.SalesId, custInvoiceTrans.InvoiceId));
    }
}

 After running this query i obtain the following :


So .. what is this ?

you will get an explanation here.

Magical FetchMode property

Just uncomment the red lines and voila !



The desired line is right here. As i have shown above, this is the one with invoice and without packing slip associated.


 

marți, 15 noiembrie 2016

Get time according to current time zone:
 -  in my case: Timezone::GMTPLUS0200ATHENS_BUCHAREST_ISTANBUL


     utcDateTime dateTime;


    dateTime = DateTimeUtil::applyTimeZoneOffset(DateTimeUtil::getSystemDateTime(),
                Timezone::GMTPLUS0200ATHENS_BUCHAREST_ISTANBUL);
   
    info(strFmt("Time %1", time2StrHMS(DateTimeUtil::time(dateTime))));

Response: Time 16:52:06

miercuri, 5 octombrie 2016

SSRS line and borders highlighting

Changing colors on grouped lines:  

Group lines according to Account value and change the color for the entire group.

=IIF(RunningValue(Fields!Account.Value, CountDistinct, Nothing) MOD 2 = 1, "WhiteSmoke", "White")


 Border style on groupings:

Top: =IIF(Fields!Account.Value = Previous(Fields!Account.Value), "None", "Solid")

-            So, when Account value changes, top line becomes gets a solid border style.

Bottom: =IIF(RowNumber("AccountLines_DS") = CountRows("AccountLines_DS"), "Solid", "None")

-            When we reach the last line, draw a solid border.


No grouping at all:


= IIF(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White")



And something ..more like a personal note.

=FORMAT(Fields!TransDate.Value, "dd.MM.yyyy")

joi, 30 iunie 2016

Get customer's party type

Starting from a LedgerJournalTrans line:

if (ledgerJournalTrans.AccountType == LedgerJournalACType::Cust)
        {
            if (CustTable::find(DimensionStorage::ledgerDimension2AccountNum(
ledgerJournalTrans.LedgerDimension)).partyType() == DirPartyType::Person)
              {
               ........
               }
        }

So, the partyType() method from CustTable will do the job.

Now here is how i did it in Sql:

--DirPerson 2975, DirOrganization 2978, CompanyInfo 41, OMOperatingUnit 2377, OMTeam 5329
SELECT CASE (SELECT dpt.INSTANCERELATIONTYPE
        FROM DIRPARTYTABLE AS dpt
        WHERE dpt.RECID = ct.PARTY)
            WHEN 2975 THEN 'Person'
            WHEN 2978 THEN 'Organization'
        END AS 'Party'
    , ct.AccountNum
FROM CUSTTABLE AS ct

and in order to get only the persons, just add a cte over it:


WITH parties
AS
(
    SELECT CASE (SELECT dpt.INSTANCERELATIONTYPE
            FROM DIRPARTYTABLE AS dpt
            WHERE dpt.RECID = ct.PARTY)
                WHEN 2975 THEN 'Person'
                WHEN 2978 THEN 'Organization'
            END AS 'Party'
        , ct.AccountNum
    FROM CUSTTABLE AS ct
)
SELECT *
FROM parties p
WHERE p.Party = 'Person'

joi, 2 iunie 2016

SSRS - Hide tablix column when all rows are empty

First of all, don't use Hidden property but right click on tablix header, choose Column Visibility




and below "Show or hide based on an expression" write down your expression.

As an example:

 =IIF(Max(Fields!OffsetDimension.Value, "LedgerTransStatementDS")= "", true, false)

So, if maximum of all field values is equal to an empty string, it means we have absolutely no values in this column.

If you would add this expression to the Hidden property then you will end up with a gap in your table.