I have firewall logs like this:
Dec 5 14:43:14 SF3D-DC SF: [1:12345:1] "Event Name" [Impact: Currently Not Vulnerable] From "My.Server.local" at Thu Dec 5 14:43:08 2013 UTC
the first time 14:43:14 in the string is the writeTime that the event was put in the IDS database.
The second time 14:43:08 is the eventTime that is the time that the IDS sensor detected the event.
I woulds like to do this:
sourcetype=IDS | eval timeDiff=writeTime - eventTime | stats avg(timeDiff)
This looks like ti should work but I think I am hanging on the strptime. since the time is already formated in the extraction should I still need to convert it to time?
To expand on the explanation given by somesoni2 (assuming that the first timestamp (writeTime) is extracted into the _time
field, i.e. the splunk timestamp for the event);
sourcetype=IDS | rex "at\s(?<eventTime>(\S+\s){5}\S+)$" | eval eventTime = strptime(eventTime, "%a %b %e %H:%M:%S %Y %Z") | eval timeDiff = _time - eventTime | stats avg(timeDiff) as avg_diff
This will give you the average timeDiff
in seconds (avg_diff = 6). If you want to you make avg_diff
"look nicer", you add this to the end;
| eval avg_diff = tostring(avg_diff, "duration")
Now, avg_diff = 00:00:06
Hope this helps,
K
To expand on the explanation given by somesoni2 (assuming that the first timestamp (writeTime) is extracted into the _time
field, i.e. the splunk timestamp for the event);
sourcetype=IDS | rex "at\s(?<eventTime>(\S+\s){5}\S+)$" | eval eventTime = strptime(eventTime, "%a %b %e %H:%M:%S %Y %Z") | eval timeDiff = _time - eventTime | stats avg(timeDiff) as avg_diff
This will give you the average timeDiff
in seconds (avg_diff = 6). If you want to you make avg_diff
"look nicer", you add this to the end;
| eval avg_diff = tostring(avg_diff, "duration")
Now, avg_diff = 00:00:06
Hope this helps,
K
Thanks this will work.
I ended up using the numerical value to get the chart like this
sourcetype=IDS | eval eventTime = strptime(eventTime, "%H:%M:%S") | eval writeTime = strptime(writeTime, "%H:%M:%S") | eval timeDiff = writeTime - eventTime | timechart span=15m avg(timeDiff) as avg_diff
I can use this to see trends and set alert values
Thanks again for your help
Timechart converts values into columns hence the eval avg_diff will not work (not column name present with that name). Your can try this workaround for it.
sourcetype=IDS | eval eventTime = strptime(eventTime, "%H:%M:%S") | eval writeTime = strptime(writeTime, "%H:%M:%S") | eval timeDiff = writeTime - eventTime | bucket _time span=15m | stats avg(timeDiff) as avg_diff by _time| eval avg_diff = tostring(avg_diff, "duration") | timechart span=15m first(avg_diff) as avg_diff
Since the value of avg_diff will be string, you won't be able to see any chart visualization but will work for table.
Kristian, thanks so much this was the answer but if I could ask you one thing. I have extracts for the time's in the data so this is my search.
sourcetype=IDS | eval eventTime = strptime(eventTime, "%H:%M:%S") | eval writeTime = strptime(writeTime, "%H:%M:%S") | eval timeDiff = writeTime - eventTime | stats avg(timeDiff) as avg_diff | eval avg_diff = tostring(avg_diff, "duration")
My question is [I can't seem to use (timechart span=15m) in place of stats?
oops... just realized that this question was rather old. Well, hope that you solved your problem already, or if you didn't - that this helped a bit... 🙂
/K
The field extraction is making writeTime and eventTime as string, so a "-" operation will not work directly. You need to convert it to epoch time for such calculations.