CODEWITHSUNDEEP
azure-log-analyticskqlazure-sentinel
shearlynot
shearlynot

Reputation: 81

Take output from query and use in subsequent KQL query

I'm using Azure Log Analytics to review certain events of interest.

I would like to obtain timestamps from data that meets a certain criteria, and then reuse these timestamps in further queries, i.e. to see what else occurred around these times.

The following query returns the desired results, but I'm stuck at how to use the interestingTimes var to then perform further searches and show data within X minutes of each previously returned timestamp.

let interestingTimes = 
Event
| where TimeGenerated between (datetime(2021-04-01T11:57:22) .. datetime('2021-04-01T15:00:00'))
| where EventID == 1
| parse EventData with * '<Data Name="Image">' ImageName "<" *
| where ImageName contains "MicrosoftEdge.exe"
| project TimeGenerated
;

Any pointers would be greatly appreciated.

Upvotes: 0

Views: 4815

Answers (2)

Matt Small
Matt Small

Reputation: 2275

Once you get a dataset returned from a query, you can reuse that dataset by invoking its name in a further query.

The below example uses your original dataset as the table for the subsequent query with an additional where clause that narrows the results.

let interestingTimes = (
Event
| where TimeGenerated between (datetime(2021-04-01T11:57:22) .. datetime('2021-04-01T15:00:00'))
| where EventID == 1
| parse EventData with * '<Data Name="Image">' ImageName "<" *
| where ImageName contains "MicrosoftEdge.exe"
| project TimeGenerated
);
interestingTimes
| where TimeGenerated between (datetime(2021-04-01T12:0:0) .. datetime('2021-04-01T12:01:00'))

Upvotes: 0

Slavik N
Slavik N

Reputation: 5308

interestingTimes will only be available for use in the query where you declare it. You can't use it in another query, unless you define it there as well.

By the way, you can make your query much more efficient by adding a filter that will utilize the built-in index for the EventData column, so that the parse operator will run on a much smaller amount of records:

let interestingTimes = 
Event
| where TimeGenerated between (datetime(2021-04-01T11:57:22) .. datetime('2021-04-01T15:00:00'))
| where EventID == 1
| where EventData has "MicrosoftEdge.exe" // <-- OPTIMIZATION that will filter out most records
| parse EventData with * '<Data Name="Image">' ImageName "<" *
| where ImageName contains "MicrosoftEdge.exe"
| project TimeGenerated
;

Upvotes: 0

Related Questions