How to compare a list of unique MAC addresses from one search to filter matching MAC addresses in another search?

[mayerda]

Hello everyone,

I am currently trying to get a list of mac addresses that can't authenticate within the cisco ise.
I already created a search String for those events.

search tag="authentication" eventtype="cisco-ise" "NOTICE Failed-Attempt: Authentication failed" | dedup EndPointMACAddress

This search string works fine so far, but here comes the problem. It's possible that a client can't authenticate itself during the bootup (generating a fail event). After the bootup, the authentication succeeds (generating success event). Those clients are erroneously in the failed authentications event list.

So I would create two searches:

tag="authentication" eventtype="cisco-ise" "NOTICE Passed-Authentication: Authentication succeeded" | dedup EndPointMACAddress
tag="authentication" eventtype="cisco-ise" "NOTICE Failed-Attempt: Authentication failed" | dedup EndPointMACAddress

Now i need to check every unique mac from the failed list in comparison to the succeeded list, but i've absolutely now clue how to realize this. I hope you can give me some helpful tips. I'm sorry for my bad english. Hopefully the problem is clear.

greetings

[jeffland]

Hi,

there is more than one way (and probably better ones than mine) to do this, but for starters I would suggest the following straightforward method:

tag="authentication" eventtype="cisco-ise" "NOTICE Failed-Attempt: Authentication failed" NOT 
[search tag="authentication" eventtype="cisco-ise" "NOTICE Passed-Authentication: Authentication succeeded" | dedup EndPointMACAddress | fields EndPointMACAddress | format]
| dedup EndPointMACAddress 

If you run the subsearch on its own, you'll see what happens there. The mac addresses that have passed authentication are simply placed in a long list like (mac=mac1 OR mac=mac2 OR mac=mac3), so the NOT before that list ensures your final results do not include any of those mac addresses.

I believe that there are smarter solutions than the above, as this runs over your data twice. At the moment, I can only come up with this kinda weird way to do it (you will need to replace the field "notice" in the stats command with wherever your "NOTICE Failed-Attempt... stems from, if it is not from a field you'll need to rex it - feel free to ask if you're unsure about that):

tag="authentication" eventtype="cisco-ise" "NOTICE Passed-Authentication: Authentication succeeded" OR "NOTICE Failed-Attempt: Authentication failed"
| dedup EndPointMACAddress | stats values(notice) as statuses dc(notice) as count by EndPointMACAddress
| where statuses="NOTICE Failed-Attempt: Authentication failed" and count=1

This looks for events which have either failed or successful authentification attempts, places the corresponding macs in a table alongside a count and the values of their statuses and from that table selects those which have only failed attempts.

Cheers

[mayerda]

Thank your very much for your reply. I will give it a try later on that day and give you some feedback. Your search terms seems logical so far.

Cheers