Forum Discussion

alchem_rj's avatar
alchem_rj
Copper Contributor
Sep 01, 2020

Table count from custom log

Hello,

 

I would like to get the count of table name reference in my custom log which is pushed to the Log analytics. The custom log structure is given below.

 

020-08-31 16:15:38 ProxyEngine [INFO] api invoked by user abcd with db user as default api user
2020-08-31 16:15:38 JdbcTemplateService [DEBUG] getting basic api user userapi
2020-08-31 16:15:38  SqlTrigger [INFO] sql fired is select * from table_1
2020-08-31 16:15:39  SqlTrigger [INFO] total records found 301
2020-08-31 16:15:39  SqlTrigger [INFO] done fetching of data

2020-08-31 16:15:50 ProxyEngine [INFO] api invoked by user xyz with db user as default api user
2020-08-31 16:15:50 JdbcTemplateService [DEBUG] getting basic api user userapi
2020-08-31 16:15:50  SqlTrigger [INFO] sql fired is select * from table_2
2020-08-31 16:15:51  SqlTrigger [INFO] total records found 305
2020-08-31 16:15:51  SqlTrigger [INFO] done fetching of data

2020-08-31 16:16:02 ProxyEngine [INFO] api invoked by user abcd with db user as default api user
2020-08-31 16:16:02 JdbcTemplateService [DEBUG] getting basic api user userapi
2020-08-31 16:16:02  SqlTrigger [INFO] sql fired is select * from table_1
2020-08-31 16:16:34  SqlTrigger [INFO] total records found 301
2020-08-31 16:16:34  SqlTrigger [INFO] done fetching of data

 

The expected output 

 

Table namecount
table_12
table_21

 

Can someone provide the KQL query to get the above output? thanks in advance.

 

 

  • Hi alchem_rj,

    You can use "parse-where" like I did here. The short syntax is 

    ...| parse-where LogText with * "from " table_name
    | summarize count() by table_name​

    where LogText is the name of the field you analyze.

    The command is documented here.

      • alchem_rj's avatar
        alchem_rj
        Copper Contributor

        Hi Noa Kuperberg ,

         

        One more help is it possible to filter count based on the month using 

         
        the bin(TimeGenerated, 30day) will help?

         

        TablenameJuly countAugust Count
        table_132
        table_2210

         

         

Resources