Introduction
Update: Microsoft Sentinel now has the ability to trigger a playbook when an incident has been updated so this blog post is obsolete!
If you are like me, you feel that one of the holes in Microsoft Sentinel is knowing when something changes. I am hoping that this changes soon (pun intended). In the meantime, I wrote a KQL query that will show you when a specific column changes. In this example, I will show you when the owner changes, but the query could be modified for almost any field.
New KQL Commands
There are a couple of KQL commands that may or may not be new to you. In case they are, I will introduce them here.
The first is serialize. According to the official KQL documentation for this command, available here, the serialize command “Marks that the order of the input row is safe to use for window functions“. And no, we are not talking about Microsoft Windows. Rather, a window command allows you to “operate on multiple rows in row set at a time.” Basically, this means that the row (or table) is frozen in time, and you can go forward and back through it.
The second command is prev() and it can only work on a serialized row set. Its official documentation, available here, states it “returns the value of a specific column in a specified row. The specified row is at a specified offset from the current row in a serialized row set“. So, it will return a column from a row that is X number of rows previous to the current row. If you do not specify an offset, it will use 1 as the defatul offset so it will look at the row immediately previous to this one. There is also a next() command that does the exact opposite.
The KQL
I am going to post the entire KQL command then break it down line by line to show how it works. I cannot find anyway to add line numbers to the code and still make it able to be copied so you may need to count the lines to match the description below.
SecurityIncident | order by TimeGenerated asc | serialize | extend NewOwner = Owner.email | extend PreIncidentNumber = prev(IncidentNumber) | extend PrevEmail = iif (PreIncidentNumber == IncidentNumber, prev(NewOwner), "") | where PrevEmail != NewOwner | order by IncidentNumber, TimeGenerated desc | summarize arg_max(TimeGenerated, *) by IncidentNumber | project TimeGenerated, NewOwner, IncidentNumber, IncidentUrl, Severity
The first two lines should be familiar to anyone who uses KQL. We look at the SecurityIncident table and order it in ascending order using the TimeGenerated column. The reason I do this is to make sure that we get all the rows in the order they were generated with the oldest ones first. I do this to make sure that the first row where the owner has changed will show up.
The serialize command in line 3, we already described. Now we have a table that we can use the prev() command on.
The next line creates a new calculated column that is equal to the email address in the Owner column. I only do this to make it easier to read the code. You can use Owner.email throughout if that works better for you.
In the 5th line, we get the IncidentNumber from the column that is immediately previous to this one. This is used to make sure we are not checking that an owner has changed against different incidents. Again, this line is optional. You can use the prev(IncidentNumber) in the next line if you so choose.
In line 6, I check to see who the previous own was. The iif check is to make sure we are looking in the same incident. If the previous row in the serialized table was a different incident, then I set the PrevEmail to an empty string to signify that we don’t have a previous owner. As with the other lines that use extend, this is optional, and you could just use the iif command in the next line.
The next line just checks to make sure we are not looking at the same owner. Only if the owners do not match do we continue.
Line 8 changes the order of how the rows are displayed. I added this since I needed to know the order that the changes happened, but this line is optional.
Line 9 is used to make sure that only a single row is returned for each incident. If for some reason the owner had changed multiple times between when this query was last run, I only care about the most recent change. If you care about each change, you can remove this line.
Finally, the last line just gives me the columns I care about. Change as you will.
Summary
In this post, I showed you how to check for when the owner of an incident has changed. This an easily be modified to suit your needs. Add it to a Logic App and send an Email to the user when a new incident is assigned to them.
Hope it helps!