topic Convert string format to time in Splunk Search

Convert string format to time

sgoyal — Thu, 19 Sep 2013 11:29:24 GMT

One log line from LDAP log file

= ==================================

Sep 19 10:08:10 simxxx11 slapd_simxxx11[4274]: conn=3012 fd=52 ACCEPT from IP=10.100.10.102:53530 (IP=0.0.0.0:636)

I can capture the STARTTIME of the LDAP connection with a regular expression, but this gives me back a string. When I capture the STARTTIME using rex "(?P<STARTTIME>\w+\s+\d+\s+\d+:\d+:\d+).+conn=\d+ fd=\d+ ACCEPT.+" then I have the value Sep 19 10:08:12 in variable STARTTIME. I want to convert it to a time format.

I have tried

strptime(STARTTIME,"%Y-%m-%dT%H:%M:%S")

But this does not work.
Another log line from LDAP log file

===================================

Sep 19 10:08:12 simxxx11 slapd_simxxx[4274]: conn=3012 fd=52 closed
"(?P<ENDTIME>\w+\s+\d+\s+\d+:\d+:\d+).+ conn=\d+ fd=\d+ closed"

I need to find the difference between the STARTTIME and ENDTIME.

Re: Convert string format to time

kristian_kolb — Thu, 19 Sep 2013 12:46:05 GMT

If the timestamps you want to use for your calculations are in fact the timestamps that have been used when indexing the events, that information is available in the _time field as an epoch value (which are great for mathematical operations).

There are several ways in which you can achieve this;

With a transaction, assuming that conn is a unique id for this connection (or at least unique within an hour or so). transaction automatically creates a new field called duration

your_base_search | transaction conn maxevents=2 maxspan=1m startswith="ACCEPT" endswith="closed" | table conn duration

With stats. Assumptions as before.

your_base_search | stats min(_time) AS StartTime max(_time) AS EndTime by conn | eval dur = tostring((EndTime - StartTime), "duration")

You could also look at the convert command instead of the eval/tostring.

Some interesting reading:

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Transaction
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Stats
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/CommonStatsFunctions
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Eval
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/CommonEvalFunctions
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Convert

Hope this helps,

Kristian

Re: Convert string format to time

sgoyal — Fri, 20 Sep 2013 11:32:23 GMT

Thank you Kristian. Actually for a transaction based on conn, I can calculate the duration. But I want to calculate the individual BIND delays and SEARCH delays inside the transaction.Each operation inside a transaction has a unique op value. How can I use it to get to the individual delays.

Re: Convert string format to time

kristian_kolb — Fri, 20 Sep 2013 12:31:46 GMT

Sorry, but I don't know the structure of your transactions. LDAP is not my strongest side. Is it something like;

conn_1_start op_1_start op_1_end op_2_start op_2_end conn_1_end

Or is it more like;

conn_1_start op_1_timestamp op_2_timestamp op_3_timestamp conn_1_end

You should probably post a few more sample events, highlighting which timstamps you need to compute durations for.