SOLVED

Comparing Arrays in KQL

%3CLINGO-SUB%20id%3D%22lingo-sub-1330070%22%20slang%3D%22en-US%22%3EComparing%20Arrays%20in%20KQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1330070%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Community%2C%3CBR%20%2F%3EI%20am%20trying%20to%20create%20Azure%20Sentinel%20rule%20in%20KQL%2C%3CBR%20%2F%3EThe%20rule%20is%20based%20on%20data%20OfficeActivity%20table%20%3A%3CBR%20%2F%3ERule%20logic%3A%20Whenever%20a%20user%20is%20using%20a%20new%20user%20agent%2C%20that%20wasn't%20previously%20used%2C%20create%20an%20alert.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20I%20can%20see%20the%20array%20with%20already%20used%20user%20agents%20in%20the%20past.%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20between%20(ago(%3C%2FSPAN%3E%3CSPAN%3E90%3C%2FSPAN%3E%3CSPAN%3Ed)%20..%20ago(%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Ed))%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3Efor%20example%3CBR%20%2F%3E%3CTABLE%20width%3D%221875%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%221875%22%3E%5B%22Microsoft%20Office%20Excel%2F16.0.12527.20260%20(Windows%2F10.0%3B%20Desktop%20x64%3B%20en-IE%3B%20Desktop%20app%3B%20HP%2FHP%20EliteBook%20Folio%201040%20G3)%22%2C%22RavenPoint%207.0.0.45159%22%2C%22Mozilla%2F5.0%20(Windows%20NT%2010.0%3B%20Win64%3B%20x64)%20AppleWebKit%2F537.36%20(KHTML%2C%20like%20Gecko)%20Chrome%2F80.0.3987.149%20Safari%2F537.36%22%2C%22MSWAC%22%2C%22%22%2C%22Microsoft%20Office%20Word%2F16.0.12527.20260%20(Windows%2F10.0%3B%20Desktop%20x64%3B%20en-IE%3B%20Desktop%20app%3B%20HP%2FHP%20EliteBook%20Folio%201040%20G3)%22%2C%22MSOCS%22%2C%22Mozilla%2F5.0%20(Windows%20NT%2010.0%3B%20Win64%3B%20x64)%20AppleWebKit%2F537.36%20(KHTML%2C%20like%20Gecko)%20Teams%2F1.3.00.4461%20Chrome%2F69.0.3497.128%20Electron%2F4.2.12%20Safari%2F537.36%22%2C%22Substrate%20Search%201.0%22%2C%22Microsoft%20SkyDriveSync%2019.232.1124.0010%20ship%3B%20Windows%20NT%2010.0%20(18363)%22%2C%22Mozilla%2F4.0%20(compatible%3B%20ms-office%3B%20MSOffice%2016)%22%2C%22OneDriveMpc-Transform_Thumbnail%2F1.0%22%2C%22Mozilla%2F4.0%20(compatible%3B%20ms-office)%22%2C%22Microsoft%20Office%20Excel%2F16.0.12624.20348%20(Windows%2F10.0%3B%20Desktop%20x64%3B%20en-IE%3B%20Desktop%20app%3B%20HP%2FHP%20EliteBook%20Folio%201040%20G3)%22%2C%22Mozilla%2F5.0%20(Windows%20NT%2010.0%3B%20Win64%3B%20x64)%20AppleWebKit%2F537.36%20(KHTML%2C%20like%20Gecko)%20Chrome%2F80.0.3987.163%20Safari%2F537.36%22%2C%22OneDriveMpc-Transform_Zip%2F1.0%22%2C%22Mozilla%2F5.0%20(compatible%3B%20MSIE%2010.0%3B%20Windows%20NT%2010.0%3B%20Win64%3B%20x64%3B%20Trident%2F7.0%3B%20Microsoft%20Outlook%2016.0.12527)%22%2C%22OfficeWordCA%22%2C%22OfficePPTCA%22%2C%22Microsoft%20SkyDriveSync%2019.232.1124.0012%20ship%3B%20Windows%20NT%2010.0%20(18363)%22%2C%22Mozilla%2F5.0%20(compatible%3B%20MSIE%2010.0%3B%20Windows%20NT%2010.0%3B%20Win64%3B%20x64%3B%20Trident%2F7.0%3B%20Microsoft%20Outlook%2016.0.12527%3B%20Microsoft%20Outlook%2016.0.12527)%22%2C%22OfficeWordWRS%22%5D%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%3EAnd%20today's%20same%20user%20used%20agent%2Fclients%3CBR%20%2F%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20between%20(ago(%3C%2FSPAN%3E%3CSPAN%3E1%3C%2FSPAN%3E%3CSPAN%3Ed)%20..%20now())%3C%2FSPAN%3E%3CBR%20%2F%3E%3CTABLE%20width%3D%221875%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%221873.64px%22%20height%3D%2284px%22%3E%5B%22RavenPoint%207.0.0.45159%22%2C%22Microsoft%20Office%20Excel%2F16.0.12624.20424%20(Windows%2F10.0%3B%20Desktop%20x64%3B%20en-IE%3B%20Desktop%20app%3B%20HP%2FHP%20EliteBook%20Folio%201040%20G3)%22%2C%22Microsoft%20SkyDriveSync%2019.232.1124.0012%20ship%3B%20Windows%20NT%2010.0%20(18363)%22%2C%22Mozilla%2F5.0%20(compatible%3B%20MSIE%2010.0%3B%20Windows%20NT%2010.0%3B%20Win64%3B%20x64%3B%20Trident%2F7.0%3B%20Microsoft%20Outlook%2016.0.12624)%22%2C%22Mozilla%2F5.0%20(Windows%20NT%2010.0%3B%20Win64%3B%20x64)%20AppleWebKit%2F537.36%20(KHTML%2C%20like%20Gecko)%20Chrome%2F81.0.4044.113%20Safari%2F537.36%22%2C%22MSWAC%22%2C%22OneDriveMpc-Transform_Thumbnail%2F1.0%22%2C%22Mozilla%2F5.0%20(Windows%20NT%2010.0%3B%20Win64%3B%20x64)%20AppleWebKit%2F537.36%20(KHTML%2C%20like%20Gecko)%20Teams%2F1.3.00.8663%20Chrome%2F69.0.3497.128%20Electron%2F4.2.12%20Safari%2F537.36%22%5D%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20question%20is%2C%20how%20can%20I%20check%20if%20all%20today%20used%20agents%20have%20already%20been%20used%20before.%3CBR%20%2F%3EHow%20can%20I%20compare%20in%20KQL%20these%20two%20arrays%20to%20check%20if%20one%20array%20is%20included%20in%20the%20other%20array%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20done%20that%20with%202%20data%20tables%2C%20one%20for%20old%20user%20agents%20and%20one%20for%20today.%3CBR%20%2F%3EI'm%20open-minded%20for%20a%20different%20approach%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1330070%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1435798%22%20slang%3D%22en-US%22%3ERe%3A%20Comparing%20Arrays%20in%20KQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1435798%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F579140%22%20target%3D%22_blank%22%3E%40DavidSho%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3EWithout%20getting%20into%20the%20details%20of%20agent%2Fclient%2C%20you%20can%20compare%20the%20arrays%20using%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fsetintersectfunction%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Eset_intersect%3C%2FA%3E%20function%2C%20which%20returns%20distinct%20array%20values%20that%20appear%20in%20all%20given%20arrays.%3C%2FP%3E%0A%3CP%3ETo%20simplify%2C%20I%20use%20in%20the%20below%20example%20only%20a%20subset%20of%20the%20arrays%20you've%20published%2C%20and%20the%20output%20in%20this%20case%20is%26nbsp%3B%5B%22RavenPoint%207.0.0.45159%22%5D%20%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Eprint%20arr%20%3D%20set_intersect(%0Adynamic(%5B%22Microsoft%20Office%20Excel%2F16.0.12527.20260%20(Windows%2F10.0%3B%20Desktop%20x64%3B%20en-IE%3B%20Desktop%20app%3B%20HP%2FHP%20EliteBook%20Folio%201040%20G3)%22%2C%22RavenPoint%207.0.0.45159%22%2C%22Mozilla%2F5.0%20(Windows%20NT%2010.0%3B%20Win64%3B%20x64)%20AppleWebKit%2F537.36%20(KHTML%2C%20like%20Gecko)%20Chrome%2F80.0.3987.149%20Safari%2F537.36%22%2C%22MSWAC%22%5D)%2C%0Adynamic(%5B%22RavenPoint%207.0.0.45159%22%2C%22Microsoft%20Office%20Excel%2F16.0.12624.20424%20(Windows%2F10.0%3B%20Desktop%20x64%3B%20en-IE%3B%20Desktop%20app%3B%20HP%2FHP%20EliteBook%20Folio%201040%20G3)%22%5D))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHTH%2C%3C%2FP%3E%0A%3CP%3ENoa%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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
1 Reply
Highlighted
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