CODEWITHSUNDEEP
kql
AUser
AUser

Reputation: 63

KQL, using the output of one Let command to determine another

Can someone please assist with the following, thanks

First of all the goal of the exercise is to create output (rather than null) when IISReset is used to Stop the service but it fails to start again (so I can set up an Alert from said output).

I have put together the following KQL at the moment

KQL code and output

As you can see from the above Results, this will show when ISSReset "was sucessfull" meaning there was a Stop event following by a Start even.

However, I want to know if there was an "IISReset /Stop" event with no subsequent "IISReset /Start" event.

The current KQL above will return blank (no data), when a successful Stop event happened but no subsequent Start event

Now, I understand the reason why on a failed Start (following a successful Stop) it KQL will return nothing. This is because there is no data to Join (line 7) and therefore nothing to summarize etc.

So I am basically trying to say run the let command IISSTOP but only return data if the result of running the PerformIISResetCheck returns null (nothing), because there was no matching Start event

This is proving tricker than I thought, perhaps there is a simple solution (or elegent solution, I do not mind which)

Can someone please assist me in achiving the state goal.

Thanks very much in advance Ernest

Upvotes: 0

Views: 315

Answers (1)

Gyp the Cat
Gyp the Cat

Reputation: 696

Using your existing logic one solution could look like this. Note that the left outerjoin will give you nulls which you can then check against.

let Event = datatable(Source:string, EventLog:string, EventID:string, RenderedDescription:string, Computer:string, TimeGenerated:datetime) [
'Microsoft-Windows-IIS-IISReset', 'System', '3202', 'IIS stop command received', 'IIS01.Global.local', datetime('03/12/2023 12:40:00'),
'Microsoft-Windows-IIS-IISReset', 'System', '3201', 'IIS start command received', 'IIS01.Global.local', datetime('03/12/2023 12:39:00'),
'Microsoft-Windows-IIS-IISReset', 'System', '3202', 'IIS stop command received', 'IIS02.Global.local', datetime('03/12/2023 12:38:00')
];
let iisstop = Event
| where EventID == '3202' and RenderedDescription contains 'IIS stop command received';
let iisstart = Event
| where EventID == '3201' and RenderedDescription contains 'IIS start command received';
let PerformIISResetCheck = iisstop
| join kind=leftouter iisstart on Computer //This join will show nulls
| extend IISRestartOK = iif(isempty(TimeGenerated1), false, true) //Check if the joined column is null
| project IISRestartOK, TimeGenerated, Computer;
PerformIISResetCheck
IISRestartOK TimeGenerated Computer
false 2023-03-12 12:38:00.0000 IIS02.Global.local
true 2023-03-12 12:40:00.0000 IIS01.Global.local

Upvotes: 0

Related Questions