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...
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_rj
Sep 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
- alchem_rjSep 07, 2020Copper Contributor
Hi Rafi_Rabo
Thanks a lot for your help.
The query you have provided is working fine. But totalcount and Ordering is not working. Is it possible to include Totalcount and Descending order in the query?
Thanks
- Rafi_RaboSep 07, 2020
Microsoft
Here is a straight-forward way of doing it:
1. Sort columns with project-reorder operator (had to manipulate the months to be from type string in order to enable sorting properly).
project-reorder documentation: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/projectreorderoperator
2. Use join to combine the results from total aggregation, and the part where you split by month (I believe that there is a simpler whey to implement this)
join documentation: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/joinoperator?pivots=azuredataexplorer
let TotalCount = someCustomLogs | parse-where LogText with * "from " table_name | summarize total = count() by table_name; someCustomLogs | parse-where LogText with * "from " table_name | extend month=(getmonth(TimeGenerated)) | project table_name, month_str = iif(month<10, strcat('0',tostring(month)), tostring(month)) // Manipulating month into string to sort it with project-reorder | evaluate pivot(month_str) | join (TotalCount) on table_name // Adding total count column - combine tables according to table_name | project-away table_name1 | project-reorder table_name, total, * asc // Sort columns | order by table_name asc // Sort rows
Rafi