Search filter for field eliminating output if a specific values (out of two) is true

[geoffmoraes]

I want to run a search where if AuthenticationMethod!=x509_PKI even once within 6 hours, it should not show the host (Calling_Station_ID) in the results.

This should work even if the search time range is increased to 48 hours or 7 days, basically only showing hosts that have not had a AuthenticationMethod=x509_PKI and have only had a AuthenticationMethod=Lookup.

With my current search, if I go back 48 hours, it shows the AuthenticationMethod=Lookup even if there was a AuthenticationMethod=x509_PKI within those hours.

index=security sourcetype=cisco:ise CISE_Passed_Authentications HostIdentityGroup="Endpoint Identity Groups:Profiled:Workstation" "Device Type=Device Type#All Device Types#Network Devices#Cisco#Monitor Mode" Protocol!=Tacacs (AuthenticationMethod=Lookup AND AuthenticationMethod!=x509_PKI) 
| fields _time Calling_Station_ID User_Name NetworkDeviceName EndPointMatchedProfile AuthenticationMethod 
| eval t=(now() - _time) 
| eval tm=(t/60) 
| eval Hours_elapsed=round(tm/60,2) 
| where Hours_elapsed > 5.99 
| table Hours_elapsed Calling_Station_ID User_Name NetworkDeviceName EndPointMatchedProfile AuthenticationMethod 

[woodcock · Accepted solution]

Your description is unclear but if what you need is to find and host that has any AuthenticationMethod="Lookup" events that have not had a partner AuthenticationMethod!="x509_PKI" event within 6-hours (on either side), then this will do that:

index="security" AND sourcetype="cisco:ise CISE_Passed_Authentications" AND HostIdentityGroup="Endpoint Identity Groups:Profiled:Workstation" AND "Device Type=Device Type#All Device Types#Network Devices#Cisco#Monitor Mode" AND Protocol!="Tacacs" AND (AuthenticationMethod="Lookup" OR AuthenticationMethod!="x509_PKI")
| streamstats time_window=6h count(eval(AuthenticationMethod="x509_PKI")) AS x509_PKI_count BY host
| search AuthenticationMethod="x509_PKI" AND x509_PCI_count=0

[geoffmoraes]

Had to tweak it a bit, but this helped. Thanks!

[richgalloway]

The base search is looking for events where AuthenticationMethod is both "Lookup" and not "x509_PKI". If it's "Lookup" then it can't be "x509_PKI" so there's no need to include both in the search.
What you need is all events with AuthenticationMethod of "Lookup" OR "x509_PKI". Combine them by calling station then discard those with x509_PKI.

index=security sourcetype=cisco:ise CISE_Passed_Authentications HostIdentityGroup="Endpoint Identity Groups:Profiled:Workstation" "Device Type=Device Type#All Device Types#Network Devices#Cisco#Monitor Mode" Protocol!=Tacacs (AuthenticationMethod=Lookup OR AuthenticationMethod=x509_PKI) 
| fields _time Calling_Station_ID User_Name NetworkDeviceName EndPointMatchedProfile AuthenticationMethod 
| stats values(*) as * by Calling_Station_ID
| where NOT mvfind(AuthenticationMethod, "x509_PKI")
| eval t=(now() - _time) 
| eval tm=(t/60) 
| eval Hours_elapsed=round(tm/60,2) 
| where Hours_elapsed > 5.99 
| table Hours_elapsed Calling_Station_ID User_Name NetworkDeviceName EndPointMatchedProfile AuthenticationMethod

[geoffmoraes]

Thanks richgalloway! Yes, it makes sense to include both "Lookup" OR "x509_PKI" events.

I tried your solution, and got an error...
Error in 'where' command: Typechecking failed. 'XOR' only takes boolean arguments.

So then I changed it to this...

index=security sourcetype=cisco:ise CISE_Passed_Authentications HostIdentityGroup="Endpoint Identity Groups:Profiled:Workstation" "Device Type=Device Type#All Device Types#Network Devices#Cisco#Monitor Mode" Protocol!=Tacacs (AuthenticationMethod=Lookup OR AuthenticationMethod=x509_PKI)
| eval t=(now() - _time) 
| eval tm=(t/60) 
| eval Hours_elapsed=round(tm/60,2) 
| where Hours_elapsed > 5.99 
| fields Hours_elapsed Calling_Station_ID User_Name NetworkDeviceName EndPointMatchedProfile AuthenticationMethod 
| stats values(*) as * by Calling_Station_ID 
| where (AuthenticationMethod!="x509_PKI")

It seems that when piping to "stats", the _time field is taken off, so I did the _time eval before the stats.

Also the "where NOT mvfind" gave no results, so I changed that to | where (AuthenticationMethod!="x509_PKI") and got output with just the Lookup auths.

The output is not accurate though; it still shows Lookup auths for a host even though it has had x509_PKI auths within the given search time period.

Can you tell me why | where NOT mvfind(AuthenticationMethod, "x509_PKI") wasn't producing any output?

[richgalloway]

I guess where does not like integer results. Try | where isnotnull(mvfind(AuthenticationMethod, "x509_PKI"))

[geoffmoraes]

Well, | where isnotnull(mvfind(AuthenticationMethod, "x509_PKI")) shows hosts that have only had x509_PKI auths; just the opposite of what is required.

So I tried | where isnull(mvfind(AuthenticationMethod, "x509_PKI")), which gives the same output as | where (AuthenticationMethod!="x509_PKI"). These show just the Lookup auths but even if there was a x509_PKI for that host within the past 6 hours. So, none of these give the required output.