topic Re: date conversion in Splunk Search

date conversion

Jananee_iNautix — Fri, 03 Jan 2014 09:17:23 GMT

Hi,
There's a problem in displaying abbreivated month and year when using the below search query

source="RSBA_LOGS2" | rex ":(?\S+)\s" | eval n=strptime(timestamp,"%b%Y") | table n

Input:
2013-12-09 18:11:34

Desired output :
Dec 12 2013

Please advise.

Re: date conversion

linu1988 — Fri, 03 Jan 2014 11:08:14 GMT

Hello,
You would need to query like this.

source="RSBA_LOGS2" | rex field=_raw "(?i)\.log:(?P<timestamp>[^,]+)" | eval m=strptime(timestamp,"%Y-%m-%d")|eval timestamp=strftime(m,"%B %Y")|table timestamp

Thanks

Re: date conversion

Jananee_iNautix — Fri, 03 Jan 2014 11:34:39 GMT

I am getting no results after running this search query.Can you please resolve

Re: date conversion

kristian_kolb — Fri, 03 Jan 2014 13:40:55 GMT

What linu1988 describes is the correct method. However, there is a slight error in the rex statement, where the backslashes are missing, probably through a copy-paste error. The following is probably more correct;

rex ":(?<timestamp>\S+)\s"

But you should also verify that the rex actually extracts the correct part of your events - otherwise the strptime/strftime functions won't work.

Re: date conversion

Jananee_iNautix — Mon, 06 Jan 2014 07:49:33 GMT

i tried with what you said kristian.it's extracting the corect part of timestamp from the log.But timestamp=strftime(m,"%b %d %Y")is not working properly i think and so i am getting no results.

Re: date conversion

linu1988 — Mon, 06 Jan 2014 09:27:51 GMT

So are you getting the correct strptime?

Re: date conversion

Jananee_iNautix — Mon, 06 Jan 2014 10:15:36 GMT

No i have problem with the statement timestamp=strftime(m,"%b %d %Y") i couldn't derive both strptime(timestamp,"%Y-%m-%d") and strftime(m,"%b %d %Y") so i m getting no result

Re: date conversion

linu1988 — Mon, 06 Jan 2014 11:05:24 GMT

could you provide us a sample log file?
we could see the extraction derive the timestamp.

Re: date conversion

Jananee_iNautix — Mon, 06 Jan 2014 12:31:04 GMT

20131209.dbg-11-trc-0.log:2013-12-09 17:52:04,021 [13771377] SUCCESS: Scan successful
I want the result
timestamp
november 2013

Re: date conversion

linu1988 — Mon, 06 Jan 2014 13:10:07 GMT

In your case splunk should automatically retrieve the timestamp from the log details. Even if you want to get it manually the search should be like the above one. I have updated the query in the answer. I am not aware if you are dividing the events or not, the timestamp can also be extracted from the log itself to assign it to the event time.

Re: date conversion

Jananee_iNautix — Mon, 06 Jan 2014 13:41:33 GMT

Thank you it is working.Can you explain search query completely.

Re: date conversion

linu1988 — Mon, 06 Jan 2014 14:37:27 GMT

You can create your own regex statement or you can use splunk's exact field option to get the same. I have used it to get the value.

Please mark it as an answer if it solved your problem.

Re: date conversion

Jananee_iNautix — Tue, 07 Jan 2014 05:02:11 GMT

Can you explain the part rex field=_raw "(?i).log:(?P[^,]+)" of regex

Re: date conversion

Jananee_iNautix — Fri, 17 Jan 2014 09:55:14 GMT

can you suggest a way on how to convert timestamp of the below log
Input :2013-12-09 18:11:34
Input :13-12-09 18:11:34
I want a common regex to convert the above format to the below format
Output:Thu December 2013 12 18:11:34.
I tried with the regex you told previously as | eval m=strptime(timestamp,"%Y-%m-%d")|eval timestamp=strftime(m,"%B %Y")|table timestamp
But it is converting only the timestamp with the format 2013-12-09 18:11:34 and not 13-12-09 18:11:34.