I created a search that is part of a view called dhcp-MAC-lookup.
When you pull up this view you are prompted to enter a MAC address, then the search runs. This works great until someone pastes in a MAC address obtained from some other application. My logs contained MAC addresses like 00:14:6a:ac:23:6e but users of this view are pasting-in 00 14 6a ac 23 6e and expecting that to work.
I know I could write a python program to rationalize what they enter so that it would be like what is in the logs, and I know that lookup supports python, but I can't figure out how I would use it to fix what they entered into a variable in the search. Any ideas about that?
I should mention that the search will be run from the Search Views menu and created through Manager User Interface, stored as a form. It has a token field for entering in the MAC address, and the usual TimePicker "time" input menu.
You can use the search language to update the user provided value before searching. One way to do that would be to use the eval's match command to replace spaces with colons at the beginning of the search:
sourcetype=logs_with_MACs [ | stats count | eval dest_mac=replace(dest_mac,"\s",":") | return dest_mac] | stats count by dest_mac
revision based on additional information:
sourcetype=logs_with_MACs [ | stats count | eval dest_mac=replace(dest_mac,"\s|,|-|_|\.",":") | return dest_mac] | stats count by dest_mac
applying to your search...
index="dhcp" sourcetype="DhcpLog" [ | stats count | eval MAC=replace("$MAC$","\s|,|-|_|\.",":") | return MAC]
| stats first(_time) as first, last(_time) as last range(_time) as range values(DevName) as Device by IPls
| convert ctime(first) AS Last_Time ctime(last) AS First_Time
| eval Duration=floor(range/3600).":".floor((range%3600)/60).":".((range%3600)%60)
| fields - first, last, range
Doing a hybrid of your two approaches with a bit of a different SEDCMD, maybe something like:
index="dhcp" sourcetype="DhcpLog"
[
* | head 1 | eval MAC=$MAC$
| rex mode=sed field=dest_mac "s/([0-9a-fA-F]{2})[ .:-]?([0-9a-f]{2})[ .:-]?([0-9a-f]{2})[ .:-]?([0-9a-f]{2})[ .:-]?([0-9a-f]{2})[ .:-]?([0-9a-f]{2})/\1:\2:\3:\4:\5:\6/"
| fields MAC
]
| stats first(_time) as first, last(_time) as last range(_time) as range values(DevName) as Device by IPls
| convert ctime(first) AS Last_Time ctime(last) AS First_Time
| eval Duration=floor(range/3600).":".floor((range%3600)/60).":".((range%3600)%60)
| fields - first, last, range
This sed command correctly normalizes all of your example cases to a canonical xx:xx:xx:xx:xx:xx format. Putting it inside a subsearch lets Splunk evaluate it, reformat it, and stuff it back into the main search.
The * | head 1
is a dirty hack I could not find a way around.
I had been trying the 'eval MAC=$MAC$' also. But with the 'eval replace' you can drop that by putting $MAC$ in quotes as the first parameter of the replace. I tripped onto that quite by accident. Must be something in the parser that wants either a known field name or a quoted string there.
The " * | head 1" hack is not a bad one, but "| stats count" may give you slightly better results as it doesn't require going to disk.
I tried this suggestion and it does work for the simple case of someone entering in space-delimited MAC address. But I have other cases too.
Here is my actual search, which is a searchTemplate in a view in the User Interface:
index="dhcp" sourcetype="DhcpLog" MAC=$MAC$ | stats first(_time) as first, last(_time) as last range(_time) as range values(DevName) as Device by IPls | convert ctime(first) AS Last_Time ctime(last) AS First_Time |eval Duration=floor(range/3600).":".floor((range%3600)/60).":".((range%3600)%60) | fields - first, last, range
It comes back with a list of all the IPs that have been assigned to this MAC address in whatever the time window is, and lists them along with first seen and last, and duration.
If I insert the [code] from your answer, it gets that one case. But I wanted to be able to take these forms also:
0123456789ab
01 23 45 67 89 ab
01:23:45:67:89:ab
01:23:45:67:89 ab
01-23-45-67-89-ab
0123.4567.89ab
I could not figure out how to do that with eval replace easily, so I decided to try using sed. Here is what I came up with:
| rex mode=sed field=MAC "s/[:. -]//g" | rex mode=sed field=MAC "s/(..)(..)(..)(..)(..)(..)/\1:\2:\3:\4:\5:\6/"
I am inserting this before the 1st '|' in the search that works. Shouldn't this work? But it doesn't.
I updated my original solution with a simple regex to handle a variety of separators
You can use the search language to update the user provided value before searching. One way to do that would be to use the eval's match command to replace spaces with colons at the beginning of the search:
sourcetype=logs_with_MACs [ | stats count | eval dest_mac=replace(dest_mac,"\s",":") | return dest_mac] | stats count by dest_mac
revision based on additional information:
sourcetype=logs_with_MACs [ | stats count | eval dest_mac=replace(dest_mac,"\s|,|-|_|\.",":") | return dest_mac] | stats count by dest_mac
applying to your search...
index="dhcp" sourcetype="DhcpLog" [ | stats count | eval MAC=replace("$MAC$","\s|,|-|_|\.",":") | return MAC]
| stats first(_time) as first, last(_time) as last range(_time) as range values(DevName) as Device by IPls
| convert ctime(first) AS Last_Time ctime(last) AS First_Time
| eval Duration=floor(range/3600).":".floor((range%3600)/60).":".((range%3600)%60)
| fields - first, last, range
These are the formats that pair of replaces handles:
0030652afccb
00:30:65:2a:fc:cb
00-30-65-2a-fc-cb
00 30 65 2a fc cb
0030.652a.fccb
This last one is used in some Cisco logs. People pick up MAC addresses from various tools used here at UCI and paste it in whatever for it is into my dhcp log search form. This should make them happy. 🙂
Regex using sed did not work at all for this. After noticing your updated comment I realized I had read the manual a little too quickly and had missed that you could do regex in the replace. But eval replace works. I extended it a bit further so it takes multiple delimiters or none, and it works just great! Thank you for your help.
Here are the parts after the 'stats count' and before 'return MAC':
| eval MAC=replace("$MAC$","[\s.:-]","")
| eval MAC=replace(MAC,"^(..)(..)(..)(..)(..)(..)$","\1:\2:\3:\4:\5:\6")
$MAC$ because the search is in a form in a User View with an input field.