SOLVED
Home

Create query with "where" clause that targets multiple accounts.

%3CLINGO-SUB%20id%3D%22lingo-sub-206289%22%20slang%3D%22en-US%22%3ECreate%20query%20with%20%22where%22%20clause%20that%20targets%20multiple%20accounts.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-206289%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20query%20multiple%20account's%20for%20the%20same%20event%20ID.%20I%20tried%20the%20syntax%20below%2C%20and%20it%20doesn't%20give%20me%20a%20syntax%20error%2C%20but%20when%20I%20test%20it%20there%20are%20no%20results.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecurityEvent%3CBR%20%2F%3E%7C%20where%20EventID%20in%20(4723%2C%204724)%3CBR%20%2F%3E%7C%20where%20TargetAccount%20%3D%3D%20%22Domain%5C%5CAdministrator%22%20or%3C%2FP%3E%3CP%3ETargetAccount%20%3D%3D%20%22Domain%5C%5CServiceAccount%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20the%20correct%20syntax%20to%20use%20%22or%22%20with%20multiple%20accounts%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEven%20better%2C%20is%20it%20possible%20to%20use%20the%20%22where%22%20clause%20with%20OUs%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-206289%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207011%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20query%20with%20%22where%22%20clause%20that%20targets%20multiple%20accounts.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207011%22%20slang%3D%22en-US%22%3E%3CP%3EUnfortunately%20I%20have%20no%20SecurityEvent%20entries%20in%20my%20workspace%20(we%20only%20have%20custom%20logs).%3C%2FP%3E%3CP%3EI%20used%20the%20datatable%20operator%20to%20simulate%20a%20similar%20input.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207006%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20query%20with%20%22where%22%20clause%20that%20targets%20multiple%20accounts.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207006%22%20slang%3D%22en-US%22%3E%3CP%3EActually%20nevermind%2C%20I%20think%20I%20understand.%20Do%20you%20know%20if%20it's%20possible%20to%20target%20Active%20Directory%20OUs%3F%20Like%20for%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecurityEvent%3CBR%20%2F%3E%7C%20where%20EventID%20in%20(4723%2C%204724)%3CBR%20%2F%3E%7C%20where%20TargetOU%20%3D%3D%20%22CN%3DServiceAccounts%2COU%3DCompany%2COU%3Dcom%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20would%20make%20my%20life%20a%20lot%20easier.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207003%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20query%20with%20%22where%22%20clause%20that%20targets%20multiple%20accounts.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207003%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20tried%20using%20%22or%22%20on%20the%20same%20line%20but%20it%20still%20doesn't%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20explain%20the%20following%20part%20a%20little%20further%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CSPAN%3Edatatable%20(EventID%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Eint%3C%2FSPAN%3E%3CSPAN%3E%2C%20TargetAccount%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Estring%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%5B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E4723%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22Domain%5C%5CAdministrator%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E4711%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22Domain%5C%5CAdministrator%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E4711%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22Domain%5C%5CServiceAccount%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E4724%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22Domain%5C%5CServiceAccount%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E4723%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22Domain%5C%5CServiceAccount%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E4724%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22foo.bar%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%3CSPAN%3EDo%20you%20use%20this%20in%20conjunction%20with%20the%20%22where%22%20statements%20at%20the%20end%3F%26nbsp%3BOr%20is%20it%20just%20another%20way%20to%20word%20it%20to%20get%20the%20same%20result%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3EFor%20example%2C%20will%20this%3A%3C%2FSTRONG%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3ESecurityEvent%26nbsp%3B(EventID%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Eint%3C%2FSPAN%3E%3CSPAN%3E%2C%20TargetAccount%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Estring%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%5B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E4723%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22Domain%5C%5CAdministrator%22%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E4724%2C%20%22Domain%5C%5CServiceAccount%22%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%5D%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSTRONG%3EReturn%20the%20same%20results%20as%20this%3A%3C%2FSTRONG%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3ESecurityEvent%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BEventID%20in%20(%3C%2FSPAN%3E%3CSPAN%3E4723%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E4724%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BTargetAccount%20in%20(%3C%2FSPAN%3E%3CSPAN%3E%22Domain%5C%5CAdministrator%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22Domain%5C%5CServiceAccount%22%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-206951%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20query%20with%20%22where%22%20clause%20that%20targets%20multiple%20accounts.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-206951%22%20slang%3D%22en-US%22%3E%3CP%3EI%20assume%20that%20you%20only%20need%20to%20have%20the%20the%20or%20statement%20in%20the%20same%20line%20with%20the%20where%20clause%20and%20it%20should%20work.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EHowever%2C%20I%20would%20prefer%20the%20following%20approach%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Edatatable%20(EventID%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Eint%3C%2FSPAN%3E%3CSPAN%3E%2C%20TargetAccount%3C%2FSPAN%3E%3CSPAN%3E%3A%3C%2FSPAN%3E%3CSPAN%3Estring%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%5B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E4723%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3E%22Domain%5C%5CAdministrator%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E4711%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3E%22Domain%5C%5CAdministrator%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E4711%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3E%22Domain%5C%5CServiceAccount%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E4724%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3E%22Domain%5C%5CServiceAccount%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E4723%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3E%22Domain%5C%5CServiceAccount%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E4724%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3E%22foo.bar%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%5D%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3EEventID%20in%20(%3C%2FSPAN%3E%3CSPAN%3E4723%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3E4724%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3ETargetAccount%20in%20(%3C%2FSPAN%3E%3CSPAN%3E%22Domain%5C%5CAdministrator%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22Domain%5C%5CServiceAccount%22%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
Ivan Koshkin
New Contributor

I would like to query multiple account's for the same event ID. I tried the syntax below, and it doesn't give me a syntax error, but when I test it there are no results.

 

SecurityEvent
| where EventID in (4723, 4724)
| where TargetAccount == "Domain\\Administrator" or

TargetAccount == "Domain\\ServiceAccount"

 

What is the correct syntax to use "or" with multiple accounts?

 

Even better, is it possible to use the "where" clause with OUs?

 

4 Replies
Solution

I assume that you only need to have the the or statement in the same line with the where clause and it should work.


However, I would prefer the following approach:

 

datatable (EventID:int, TargetAccount:string)
[
4723, "Domain\\Administrator",
4711, "Domain\\Administrator",
4711, "Domain\\ServiceAccount",
4724, "Domain\\ServiceAccount",
4723, "Domain\\ServiceAccount",
4724, "foo.bar",
]
| where EventID in (4723, 4724)
| where TargetAccount in ("Domain\\Administrator","Domain\\ServiceAccount")

I've tried using "or" on the same line but it still doesn't work.

 

Can you explain the following part a little further?

 

datatable (EventID:int, TargetAccount:string)
[
4723"Domain\\Administrator",
4711"Domain\\Administrator",
4711"Domain\\ServiceAccount",
4724"Domain\\ServiceAccount",
4723"Domain\\ServiceAccount",
4724"foo.bar",
 
Do you use this in conjunction with the "where" statements at the end? Or is it just another way to word it to get the same result?
 
For example, will this:
 
SecurityEvent (EventID:int, TargetAccount:string)
[
4723"Domain\\Administrator",
4724, "Domain\\ServiceAccount",
]
 
Return the same results as this:
 
SecurityEvent
where EventID in (47234724)
where TargetAccount in ("Domain\\Administrator","Domain\\ServiceAccount")

Actually nevermind, I think I understand. Do you know if it's possible to target Active Directory OUs? Like for example:

 

SecurityEvent
| where EventID in (4723, 4724)
| where TargetOU == "CN=ServiceAccounts,OU=Company,OU=com"

 

This would make my life a lot easier.

Unfortunately I have no SecurityEvent entries in my workspace (we only have custom logs).

I used the datatable operator to simulate a similar input.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies