All Apps and Add-ons

Custom time field for timechart

gaspnico57
Engager

Hello everyone!

I'm tying to build a Dashboard from a db connected to splunk server thanks to dbconnect.
From my query, i don't get event, but only a table from my db.

I would like to create a timechart using a column of my table as time. This column is a UNIX (epoch) time.
So i tried a lot of ways like :

myquery | eval _time=strftime(my_unix_time_column,"%Y-%m-%d %H:%M:%S")| timechart count by another_column

And don't get what i want 😞
I guess i have a problem when i convert my unix time

Do you have any idea?

Thank you!

Gaspard

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

The _time field must be in epoch form. Try myquery | eval _time=my_unix_time_column | timechart count by another_column.

---
If this reply helps you, an upvote would be appreciated.

View solution in original post

FrankVl
Ultra Champion

Skip the conversion. _time must contain an epoch value. Splunk just automatically displays it in a readable format 🙂

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The _time field must be in epoch form. Try myquery | eval _time=my_unix_time_column | timechart count by another_column.

---
If this reply helps you, an upvote would be appreciated.

View solution in original post

gaspnico57
Engager

Thank you (@FrankVl too) !
But do you know why when i choose different value from the timepicker (for exemple "last 30 days") i get result from december?

0 Karma

gaspnico57
Engager

Here is my highest value : 1558539900 and here is my lowest one : 1545145873.
As you can see in the screenshot, even when i choose "last 90 days", i still get a date in 2018 😞
I'm going to ask a new question i guess

alt text

0 Karma

FrankVl
Ultra Champion

Those _time values are the result of | eval _time=my_unix_time_column, I presume?

The timepicker applies to the original _time value, which apparently varies from the my_unix_time_column.

0 Karma

gaspnico57
Engager

Yes it's the right command.
Do you know why could the time _time varie from my_unix_time_column?
The conversion seems right between epoch and readable time...

0 Karma

FrankVl
Ultra Champion

That fully depends on what the original _time field was based on. That will not be based on your my_unix_time_column, otherwise you could have done a straightforward timechart from the start and didn't have to use this alternative field.

To get an answer to that, you need to look into what was used to originally set _time during the ingestion of this data (some other (possibly incorrectly interpreted) timestamp in the event? current time during ingest? ...). And maybe even need to investigate whether maybe some data source has an incorrect clock setting or so.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

It's hard to say without more information. I suspect there's a big difference between _time and my_unix_time_column. Or the time format is mis-interpreted (like dd/mm/yy vs mm/dd/yy).

---
If this reply helps you, an upvote would be appreciated.
0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!