Splunk Search

Need Assistance with Search Construction

jodros
Builder

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:

Mac Address (provided by WC)| User Name (provided by AS)| Count

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.

1 Solution

sideview
SplunkTrust
SplunkTrust

Although the 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 stats command.

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 "wireless_controller" and "authentication_server" 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.


UPDATE

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.

View solution in original post

sideview
SplunkTrust
SplunkTrust

Although the 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 stats command.

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 "wireless_controller" and "authentication_server" 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.


UPDATE

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.

jodros
Builder

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.

0 Karma

sideview
SplunkTrust
SplunkTrust

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 failure_event", then the only other error I see is that you have to quote literals in eval expressions, specifically the sourcetype==cisco_wlc. As written that would compare the sourcetype field with the cisco_wlc field. So that clause should be | eval normalizedMacAddr=if(sourcetype=="cisco_wlc",mac_upper,Caller_ID)

jodros
Builder

(sourcetype="cisco_wlc" %DOT1X-3-MAX_EAP) OR sourcetype="cisco_acs" | eval mac_dash=replace(client_mac, ":", "-") | eval mac_upper=upper(mac_dash) | eval normalizedMacAddr = if(sourcetype==cisco_wlc,mac_upper,Caller_ID) | rex field=_raw "%(?DOT1X-3-MAX_EAP)" | eval isFailedWirelessEvent=if(failure_event="DOT1X-3-MAX_EAP",1,0) | stats sum(isFailedWirelessEvent) as failedCount values(User_Name) by normalizedMacAddr

0 Karma

jodros
Builder

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.

0 Karma

sideview
SplunkTrust
SplunkTrust

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.

jodros
Builder

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.

0 Karma

_d_
Splunk Employee
Splunk Employee

Jodros, see if the join command will assist you in your case:

http://docs.splunk.com/Documentation/Splunk/4.2.4/SearchReference/Join

- please upvote if you find this answer useful

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 ...