Table count from custom log

[alchem_rj]

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 name	count
table_1	2
table_2	1

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

[#2]

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))

[#3]

Hi @alchem_rj , 

This behavior is expected since the string "abc_8val_yy" is a sub-string of "abc_8val_yy_vw".

Follow the extract operator documentation for more details: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/extractfunction

Rafi

[#4]

Hi @Rafi_Rabo,

Is it possible to fetch the exact matching string from Rawdata using "extract" operator? or any other command?

[#5]

Hi @alchem_rj.,

You can tweak the regular expression, using the operator $ which means end of match.

In your case:

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

Reference for regular expressions supported in Kusto: https://github.com/google/re2/wiki/Syntax

Rafi