Forum Discussion
alchem_rj
Sep 01, 2020Copper Contributor
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...
alchem_rj
Sep 04, 2020Copper Contributor
Hi Noa Kuperberg ,
One more help is it possible to filter count based on the month using
the bin(TimeGenerated, 30day) will help?
Tablename | July count | August Count |
table_1 | 3 | 2 |
table_2 | 2 | 10 |
Noa Kuperberg
Microsoft
Sep 06, 2020alchem_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_rjSep 07, 2020Copper Contributor
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
- Rafi_RaboSep 07, 2020
Microsoft
Hi alchem_rj ,
Try to run it without summarizing before pivot:
someCustomLogs | parse-where LogText with * "from " TableName | extend month=getmonth(TimeGenerated) | project TableName, month | evaluate pivot(month)
The reason that you've got the error in the link is that the 'let' statement which defines the data table somehow wasn't complete, try it again: Go to Log Analytics and run query
Rafi