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 [I...
azure monitor
Log Analytics
Noa Kuperberg's avatar
Noa Kuperberg
Icon for Microsoft rankMicrosoft

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
Sep 04, 2020

Thanks a lot, Noa Kuperberg for the suggestion

  • alchem_rj's avatar
    alchem_rj
    Copper Contributor
    Sep 04, 2020

    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

     

     

    • Noa Kuperberg's avatar
      Noa Kuperberg
      Icon for Microsoft rankMicrosoft
      Sep 06, 2020

      alchem_rj yes, but it will be displayed a bit differently. Easiest is to just add a month column - Run this to play with it (I'm assuming the datetime information is ingested to the TimeGenerated field, which is typically what happens). The query text is:

      ...
| parse-where LogText with * "from " table_name
| extend month=getmonth(TimeGenerated)
| summarize count() by month, table_name

      and in ithis case the results would be:

      month table_name count_
      8 table_1 2
      8 table_2 1

      To create a column for each month, append "| evaluate pivot(month)" to the query and you'll get:

      table_name count_ 8 7
      table_1 2 1 ...
      table_2 1 1 ...

       Of course with more data you'll have additional columns for the other months.

      • alchem_rj's avatar
        alchem_rj
        Copper Contributor
        Sep 07, 2020

        Thanks Noa Kuperberg ,

         

        Yes,  the datetime information is ingested to the TimeGenerated field,

        I tried with "evaluate pivot(month)" function but am getting some random value like "1" and "0" as output.

         

         

        | parse RawData with * "from (" from "from " Tablename "where" clause
| extend month=getmonth(TimeGenerated)
| summarize Count=count()by month, Tablename 
| evaluate pivot(month)
| order by Count desc

         

         

        Also, the link you provided to play with am getting below error.

         

        Query could not be parsed at ',' on line [1,21] Token: , Line: 1 Position: 21

         

        Thanks

Resources