Table count from custom log

Thanks Rafi_Rabo!

The query itself is valid.

Is it possible that the inegested data has a different format that what you (customer) used on your original question? TimeGenerated should be in UTC. A different formatting may cause "getmonth" to fail or return a wrong value. 

Hi Rafi_Rabo ,

Thanks a lot, it worked fine.

We are trying to extend this query to include a rawdata that contains the string "from" two times?

An example SQL log snippet is given below.

2020-08-31 16:15:38  SqlTrigger [INFO] sql fired is select * from value(sum 5s) as from table_1
2020-08-31 16:15:38  SqlTrigger [INFO] sql fired is select * from value(sum 5s) as from table_1
2020-08-31 16:15:38  SqlTrigger [INFO] sql fired is select * from value(sum 5s) as from table_1
2020-08-31 16:15:50  SqlTrigger [INFO] sql fired is select * from table_2

Expected output

Thank

Rinshad

alchem_rj 

You can use the extract() function to identify the desired pattern ("table_" + <number> in this case), and just filter out the rows without match.

Link to documentation: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/extractfunction

let TotalCount = someCustomLogs
| extend table_name = (extract ("table_([0-9]+)", 0, LogText))
| where isnotempty(table_name)
| summarize total = count() by table_name;
someCustomLogs 
| extend table_name = (extract ("table_([0-9]+)", 0, LogText)), month=(getmonth(TimeGenerated))
| where isnotempty(table_name)
| 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

Hi Rafi_Rabo,

How we can do a string regression. For example, if the table name is as follows then how we can include it in the query.

1. abc_3val_xyz_dd

2. txt_1data_abcd

Thanks

Hi alchem_rj ,

I can think of several options:

1. Look for more patterns in the the extract() function (You may use more complex RegExp to match more tables). Note that this is not scalable solution since you'll always need to add more patterns/ table names to the expression.

| extend table_name = (extract ("(table_[0-9]+)|(abc_3val_xyz_dd)|(txt_1data_abcd)", 0, LogText)), month=(getmonth(TimeGenerated))

2. Add a 'TableName' column to your custom logs, with the table name in the relevant rows. Easily you'll be able to filter out logs which are not related to tables, and summarize according to 'TableName'.

3. Write the message in your logs with a unique pattern for table, for example

Hi Rafi_Rabo,

Do we have RegExp similar to below?

1. abc_[0-9]val_* (all string or word )after this?

Expected table names

abc_1val_dd

abc_3val_xx

abc_2val_extra_cc

Thanks

Hi alchem_rj ,

The following should work:  abc_[0-9]+val_.+

Here is a link to the regular expression syntax supported by Kusto:   https://github.com/google/re2/wiki/Syntax 

Rafi

Hi Rafi_Rabo ,

When I use the Regex you have provided I am getting all the value in the RawData along with the table name. I am not able to filter the exact table name from the RawData.

Hi alchem_rj ,

Try running the following: Go to Log Analytics and run query

I tweaked the regexp to: 

abc_[0-9]+val_[0-9A-Za-z_]+

If you still have issues, please share the data and query you are trying to run.

Rafi

Hi Rafi_Rabo 

Thanks a lot for your help.

I also notice that if I use a similar table name in the extend operator am getting same result.

extend table_name = (extract ("(abc_8val_yy)", 0, RawData))

The output (count of table name) of above query and below query is same

extend table_name = (extract ("(abc_8val_yy_vw)", 0, RawData))