Display only events that fulfill all IN conditions

[DanielAmlung]

Hi Team,

i have a search that query's for 4 IN conditions and then list them. The search works fine but i need help with one request. I only want to display the events that fulfill all 4 conditions within the IN statement:

Search:

index=wineventlog EventCode=5145 file_name="\\\\*\\IPC$" RelativeTargetName IN (samr,lsarpc,srvsvc,winreg) src_user!=*$
| stats count by src_user,src_ip,RelativeTargetName,host_fqdn
| stats list(RelativeTargetName) by src_ip, src_user,host_fqdn

Table:

So in only want to see the events that match all 4 RelativeTargetNames not the one that matches only one.

Any help would be appreciated 🙂

[ITWhisperer · Accepted solution]

| stats list(RelativeTargetName) as RelativeTargetName by src_ip, src_user,host_fqdn
| where mvcount(RelativeTargetName) = 4

[PickleRick]

As an additional hint, you could add your all four search term literally to limit the initial search results for a bit of a performance boost.

index=wineventlog EventCode=5145 file_name="\\\\*\\IPC$" RelativeTargetName IN (samr,lsarpc,srvsvc,winreg) src_user!=*$ samr lsarpc srvsvc winreg
| stats count by src_user,src_ip,RelativeTargetName,host_fqdn
| stats list(RelativeTargetName) by src_ip, src_user,host_fqdn

But whether this is significantly beneficial you'd have to see the job inspect page.

Another way to limit your results (as opposed to @ITWhisperer 's solution which works on the summarized data) would be to add all four values explicitly as field values, not with the IN clause.

index=wineventlog EventCode=5145 file_name="\\\\*\\IPC$" RelativeTargetName=samr, RelativeTargetName=lsarpc RelativeTargetName=srvsvc RelativeTargetName=winreg src_user!=*$ 

[DanielAmlung]

Thanks for pointing me in the right direction. I slightly modified the search and it now works:

index=wineventlog EventCode=5145 file_name="\\\\*\\IPC$" RelativeTargetName IN (samr,lsarpc,srvsvc,winreg) src_user!=*$
| stats count by src_user,src_ip,RelativeTargetName,host_fqdn
| stats list(RelativeTargetName) as all by src_ip, src_user,host_fqdn
| where mvcount(all) = 4

[sintjm]

hi

have you tried mvexpand list(RelativeTargetName)

[DanielAmlung]

Hi, thanks for the answer, but i don't want to expand the multi value field. So this is not what iam looking for