SOLVED

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

Copper 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
best response confirmed by Ivan Koshkin (Copper Contributor)
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.

1 best response

Accepted Solutions
best response confirmed by Ivan Koshkin (Copper Contributor)
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")

View solution in original post