Splunk Search

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

mayerda
Engager

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

0 Karma

jeffland
SplunkTrust
SplunkTrust

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
Engager

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...