Splunk Search

Strptime and mktime don't want to modify time string

iKate
Builder

Hi!
I have a lookup table with time srings like this: 2013.11 and I want splunk to understand it is a time and make it use it in timechart but can't make it work properly.

Here's a piece of csv file:

"uid",date,"type"
1010174,"2013.11",MIX
1014625,"2013.11",MIX

Here's how I try to do it:

| inputlookup file.csv
| eval date=strptime(date, "%Y.%m") 
| eval _time=date
| timechart count

and got nothing

OR

| inputlookup file.csv
| convert timeformat="%Y.%m" mktime(date)
| eval _time=date
| timechart count

Treats all entries like they were in 1970th, so it couldn't parse it and assigned epochtime 0.

Where is the mistake?

Thanks in advance

0 Karma
1 Solution

sowings
Splunk Employee
Splunk Employee

I've had some weirdness with strptime when it's not capturing complete sub groups. That is, if you consider a day as "day month year" in any combination, and a time as "hours minutes seconds" in any combination, it seems that Splunk's strptime wants to find all three parts of a tuple when it parses. I started here:


| stats count | eval foo="2013.11" | eval date=strptime(foo, "%Y.%m") | table foo, date

I got foo as the literal string, and date as null. Testing my hunch, I did some string math on the left, and added a %d to the parsing spec:


| stats count | eval foo="2013.11" + ".01" | eval date=strptime(foo, "%Y.%m.%d") | table foo, date

Now I got an epoch time in the date field, and when I convert()ed that, I saw that it matched the first of November.

This gives you a programmatic way to get to something you can parse.

View solution in original post

sowings
Splunk Employee
Splunk Employee

I've had some weirdness with strptime when it's not capturing complete sub groups. That is, if you consider a day as "day month year" in any combination, and a time as "hours minutes seconds" in any combination, it seems that Splunk's strptime wants to find all three parts of a tuple when it parses. I started here:


| stats count | eval foo="2013.11" | eval date=strptime(foo, "%Y.%m") | table foo, date

I got foo as the literal string, and date as null. Testing my hunch, I did some string math on the left, and added a %d to the parsing spec:


| stats count | eval foo="2013.11" + ".01" | eval date=strptime(foo, "%Y.%m.%d") | table foo, date

Now I got an epoch time in the date field, and when I convert()ed that, I saw that it matched the first of November.

This gives you a programmatic way to get to something you can parse.

iKate
Builder

@sowings btw) Maybe this question can be as easy for you as one above:) It will help us greatly
http://answers.splunk.com/answers/110399/db-connect-app-execution-of-input-successfalse-continuemoni...

0 Karma

iKate
Builder

@sowings cool, thank you!
I complemented my date string like this and it worked finally:
...
| eval date=date.".".01
| convert timeformat="%Y.%m.%d" mktime(date) as date
...
You're right it's weird thing, I expected splunk could cope with time strings of any completion..

0 Karma

MuS
SplunkTrust
SplunkTrust

Hi iKate,

have you tried using chart instead of timechart?
timechart count is basically the same as chart count by _time, so just use that with a different field instead of _time.

hope this helps ...

cheers, MuS

0 Karma

iKate
Builder

Hi @MuS, the issue isn't in final charting. Before chart/timechart command results go with wrong time: 1/1/70 3:33:33.110 AM
instead of November 2013

0 Karma

lukejadamec
Super Champion

Try changing the new field so it is different from the evaluated field:

eval newdate=strptime(date, "%Y.%m")
0 Karma

iKate
Builder

I've already tried it) no result

0 Karma

MuS
SplunkTrust
SplunkTrust

wow - we answered at the same time:
lukejadamec answered 22 secs ago
MuS answered 22 secs ago

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