How to identify table to query in Azure Log Anlytics

Question

Newbie to Azure.Let me give some background to my question. I work in a SaaS environment. I have to log into Azure to pull metrics after a job has run successfully. Each job has 2 tasks associated to it. One tracks when the job started and when it completed. I pull the start and end time so i can calculate duration. Second task sends a notification to a distribution list confirming the job ran successfully or failed. I want to be able to use log analytics to run a query to automate this.Pull a report with the jobid, start and end times of the job.Problem is i cannot seem to identify the table that has all the information I need.Any pointers on how to do this would be great. I can see a task table when i go to log analytics but i do not see the start/end times fields.

Answer 1

Log analytics as some limitations to it's KQL, E.g. .show database schema is not supported, however, I have a few tricks up my sleeve :-)

For all populated tables, for the chosen time period

Get number of records per table

union withsource=mytable *
| summarize count() by mytable

E.g.

mytable	count_
ADAssessmentRecommendation	244
SigninLogs	1892
KubePodInventory	125317
AppServiceHTTPLogs	84578
SecurityAlert	1

Get info of all columns

union *
| getschema 

E.g.

ColumnName	ColumnOrdinal	DataType	ColumnType
TenantId	0	System.String	string
SourceSystem	1	System.String	string
TimeGenerated	2	System.DateTime	datetime
Computer	3	System.String	string
Origin	4	System.String	string
Namespace	5	System.String	string
Name	6	System.String	string
Val	7	System.Double	real
Tags_string	8	System.String	string
Tags_dynamic	9	System.Object	dynamic

Get columns' info per table

This is done in 2 steps.

Execute the following query

union withsource=mytable *
| summarize by mytable
| serialize 
| summarize query = strcat_array(make_list(strcat(case(row_number() == 1, "", "| union "), "(", mytable, " | getschema | extend mytable = '", mytable, "')"))," ")

E.g.

query
(AppServiceAppLogs | getschema | extend mytable = 'AppServiceAppLogs') | union (ADAssessmentRecommendation | getschema | extend mytable = 'ADAssessmentRecommendation') | union (InsightsMetrics | getschema | extend mytable = 'InsightsMetrics')

Copy-Paste the results and execute

E.g.

|    ColumnName     | ColumnOrdinal |    DataType     | ColumnType |          mytable           |
|-------------------|---------------|-----------------|------------|----------------------------|
| TenantId          |             0 | System.String   | string     | AppServiceAppLogs          |
| TimeGenerated     |             1 | System.DateTime | datetime   | AppServiceAppLogs          |
| Level             |             2 | System.String   | string     | AppServiceAppLogs          |
| Host              |             3 | System.String   | string     | AppServiceAppLogs          |
| ResultDescription |             4 | System.String   | string     | AppServiceAppLogs          |
| TenantId          |             0 | System.String   | string     | ADAssessmentRecommendation |
| MG                |             1 | System.String   | string     | ADAssessmentRecommendation |
| SourceSystem      |             2 | System.String   | string     | ADAssessmentRecommendation |
| TenantId          |             0 | System.String   | string     | InsightsMetrics            |
| SourceSystem      |             1 | System.String   | string     | InsightsMetrics            |
| TimeGenerated     |             2 | System.DateTime | datetime   | InsightsMetrics            |
| Computer          |             3 | System.String   | string     | InsightsMetrics            |