We have a wireless controller that provides logs. I am trying to construct a search that would provide the number of times individual mac addresses failed authentication to a wireless controller. This search is relatively simple.
However, I would then like to add an additional piece of information and provides usernames for each mac address. We also have an authentication server that has both mac addresses and usernames. Normally I would just run the search against the authentication server alone. But not every failed attempt to the wireless controller is seen by the authentication server.
To complicate things even more, the format for mac addresses in the wireless controller is nl:nl:nl:nl:nl:nl where n=number and l=lowercase letter. The mac address format in the authentication server is nL-nL-nL-nL-nL-nL where n=number and L=uppercase letter. I am able to successfully reformat the mac address field with the eval command.
My issue is trying to tie the mac addresses returned by the wireless controller search back to the authentication server logs to populate username fields, understanding that not all mac addresses will provide usernames. I don't know if I should use a subsearch or if there is a better method. The final results should look something like this:
11:11:11:11:11:11 | bob | 11
22:22:22:22:22:22 | | 7
33:33:33:33:33:33 | julie | 5
Any assistance with this issue would be appreciated.
Thanks in advance.
join command is very familiar and it's tempting to think about this like you would a SQL query, the Splunk answer here is not to use the
join command but just the
In the interest of clarity I've made some creative assumptions here. I assume there's some field in the wireless controller data that says things are 'failed', and i've used goofy sourcetypes like "wirelesscontroller" and "authenticationserver" that are obviously made up.
To start with a simple example, if the mac addresses appeared in both sourcetypes with the same casing, it would be really quite simple:
( sourcetype=wireless_controller successField="Failed" ) OR sourcetype=authentication_server | eval isFailedWirelessEvent=if(successField=="Failed",1,0) |
stats sum(isFailedWirelessEvent) as failedCount values(username) as users by mac_address
And to do the mac address normalization you basically take the eval that you've worked out on your own, work it into the above before the rows are piped to stats, and you should have what you need.
join is familiar from the SQL world but it's almost always faster to use the
stats command for these cases and
join has some other drawbacks as well.
Generally of course your two sourcetypes dont do you the favor of using exactly the same field, in this case 'mac_address'. Here's an eval clause you can use to normalize them.
eval normalizedMacAddr = if(sourcetype==wireless_controller,wirelessMacAddrField,authServerMacAddrField)
Put that eval into the search anywhere before the stats clause, and modify the stats clause to be
by normalizedMacAddr. Hopefully that makes sense. I forgot to mention that step in my first writeup.
At this time, there isn't a "Failed" field in the wireless controller logs. I am searching for raw characters of event ID's. I could build a custom field extraction if needed.
In this example, do I need to format the mac addresses to match the wireless controller, or authentication server?
Lastly, in the stats line, I am assuming that "mac_address" is a field in the authentication server, but wanted to verify.
Thanks again for the assistance.
You can then use the 'match' or 'searchmatch' functions within the eval command, or if it's a complicated search expression you could just define an eventtype to wrap it all up and then the nice side effect of eventtypes you'd have eventtype=wireless_fail, etc. See updated answer for more details about the two different macAddress fields.
I tried the update you posted. I get something that looks similar to what I need. However the "failedCount" for all is 0. Below is my search string to this point. Please rip it apart if need be.
(sourcetype="ciscowlc" %DOT1X-3-MAXEAP) OR sourcetype="ciscoacs" | eval macdash=replace(clientmac, ":", "-") | eval macupper=upper(macdash) | eval normalizedMacAddr = if(sourcetype==ciscowlc,macupper,CallerID) | rex field=raw "%(?<failureevent>DOT1X-3-MAXEAP)" | eval isFailedWirelessEvent=if(failureevent="DOT1X-3-MAXEAP",1,0) | stats sum(isFailedWirelessEvent) as failedCount values(UserName) by normalizedMacAddr
Hm. Well if that rex command works (and you can test it in the UI by deleting back to that point and tacking on " | stats count by failureevent", then the only other error I see is that you have to quote literals in eval expressions, specifically the sourcetype==ciscowlc. As written that would compare the sourcetype field with the ciscowlc field. So that clause should be | eval normalizedMacAddr=if(sourcetype=="ciscowlc",macupper,CallerID)
That did it. I added a "| where failedCount > 0" to show only mac addresses that had failure attempts. Do you have any suggestions for cleaning up the search string. I am primarily concerned with my "rex field" stanza. Is there a way to remove that?
I really appreciate all of the assistance.