Comparing Arrays in KQL

[DavidSho]

Hello Community,
I am trying to create Azure Sentinel rule in KQL,
The rule is based on data OfficeActivity table :
Rule logic: Whenever a user is using a new user agent, that wasn't previously used, create an alert.

Now I can see the array with already used user agents in the past.

| where TimeGenerated between (ago(90d) .. ago(1d))

for example

["Microsoft Office Excel/16.0.12527.20260 (Windows/10.0; Desktop x64; en-IE; Desktop app; HP/HP EliteBook Folio 1040 G3)","RavenPoint 7.0.0.45159","Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.149 Safari/537.36","MSWAC","","Microsoft Office Word/16.0.12527.20260 (Windows/10.0; Desktop x64; en-IE; Desktop app; HP/HP EliteBook Folio 1040 G3)","MSOCS","Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Teams/1.3.00.4461 Chrome/69.0.3497.128 Electron/4.2.12 Safari/537.36","Substrate Search 1.0","Microsoft SkyDriveSync 19.232.1124.0010 ship; Windows NT 10.0 (18363)","Mozilla/4.0 (compatible; ms-office; MSOffice 16)","OneDriveMpc-Transform_Thumbnail/1.0","Mozilla/4.0 (compatible; ms-office)","Microsoft Office Excel/16.0.12624.20348 (Windows/10.0; Desktop x64; en-IE; Desktop app; HP/HP EliteBook Folio 1040 G3)","Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.163 Safari/537.36","OneDriveMpc-Transform_Zip/1.0","Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 10.0; Win64; x64; Trident/7.0; Microsoft Outlook 16.0.12527)","OfficeWordCA","OfficePPTCA","Microsoft SkyDriveSync 19.232.1124.0012 ship; Windows NT 10.0 (18363)","Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 10.0; Win64; x64; Trident/7.0; Microsoft Outlook 16.0.12527; Microsoft Outlook 16.0.12527)","OfficeWordWRS"]

And today's same user used agent/clients
| where TimeGenerated between (ago(1d) .. now())

["RavenPoint 7.0.0.45159","Microsoft Office Excel/16.0.12624.20424 (Windows/10.0; Desktop x64; en-IE; Desktop app; HP/HP EliteBook Folio 1040 G3)","Microsoft SkyDriveSync 19.232.1124.0012 ship; Windows NT 10.0 (18363)","Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 10.0; Win64; x64; Trident/7.0; Microsoft Outlook 16.0.12624)","Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.113 Safari/537.36","MSWAC","OneDriveMpc-Transform_Thumbnail/1.0","Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Teams/1.3.00.8663 Chrome/69.0.3497.128 Electron/4.2.12 Safari/537.36"]

My question is, how can I check if all today used agents have already been used before.
How can I compare in KQL these two arrays to check if one array is included in the other array?

Thanks

I have done that with 2 data tables, one for old user agents and one for today.
I'm open-minded for a different approach

[#2 · Accepted solution]

@DavidSho ,

Without getting into the details of agent/client, you can compare the arrays using the set_intersect function, which returns distinct array values that appear in all given arrays.

To simplify, I use in the below example only a subset of the arrays you've published, and the output in this case is ["RavenPoint 7.0.0.45159"] :

print arr = set_intersect(
dynamic(["Microsoft Office Excel/16.0.12527.20260 (Windows/10.0; Desktop x64; en-IE; Desktop app; HP/HP EliteBook Folio 1040 G3)","RavenPoint 7.0.0.45159","Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.149 Safari/537.36","MSWAC"]),
dynamic(["RavenPoint 7.0.0.45159","Microsoft Office Excel/16.0.12624.20424 (Windows/10.0; Desktop x64; en-IE; Desktop app; HP/HP EliteBook Folio 1040 G3)"]))

HTH,

Noa