Splunk Search
Highlighted

How do I extract a time field and use it as the y axis?

Explorer

Hi,

This is probably a very easy thing to do, but I did not seem to find a suitable answer in the documentation yet. What I am trying to do is extract a time from my events and use that time as the y-axis for a line chart. What I am trying right now is the following:

`rex "\d:\d+:\d+\.\d+(?<TIME_TAKEN>)" | convert dur2sec(TIME_TAKEN) | chart first(TIME_TAKEN) by _time`

for events of the form: 0:13:23.233434

The trouble with this approach is that the "first(TIME_TAKEN)" column is empty. I would also like to keep this as a permanent chart - Following the videos there is a props.conf file to edit but I am not sure which. I am new to splunk.

Thanks for the help.

Tags (3)
Highlighted

Re: How do I extract a time field and use it as the y axis?

SplunkTrust
SplunkTrust

Your starting point should be a timechart command, not chart by _time, because the latter is just a clumsy partial version of the former. Also I think chart doesnt always play nice with first and last, since chart is designed to work only with numeric data, and first is generally used for categorical data. Try this:

rex "\d:\d+:\d+.\d+(?<TIME_TAKEN>)" | convert dur2sec(TIME_TAKEN) | timechart sum(TIME_TAKEN) 

or you might try max() or min() or avg() instead of the sum. Or all of them together.

UPDATE: I think the problem was that your regex wasnt actually extracting your duration field: try this instead:

rex (?<TIME_TAKEN>\d:\d+:\d+.\d+)? | convert dur2sec(TIME_TAKEN) | timechart sum(TIME_TAKEN) 
0 Karma
Highlighted

Re: How do I extract a time field and use it as the y axis?

Explorer

Thanks, I am pretty sure timechart is the way to go, but this still does not work. I get two columns with appropriate result when I only enter: rex "\d:\d+:\d+.\d+(?)" | convert dur2sec(TIMETAKEN) but if I add | timechart max(TIMETAKEN) (or any other function for that matter) I get no results in the max(TIME_TAKEN) column.

0 Karma
Highlighted

Re: How do I extract a time field and use it as the y axis?

Explorer

Hi, I tried rex (?\d:\d+:\d+.\d+)? | convert dur2sec(TIMETAKEN) | timechart sum(TIMETAKEN) but it gives me the same result as before. The duration seems to be extracted as I can get the correct values if I don't type timechart afunction(TIME_TAKEN) . Thanks again for the help.

0 Karma
Highlighted

Re: How do I extract a time field and use it as the y axis?

SplunkTrust
SplunkTrust

I think at this point it's just casing. Fields are case-sensitive but unfortunately when I first wrote my answer I used lowercase in the rex command and uppercase everywhere else. Field names are case-sensitive so I think the only problem remaining is that timetaken!=TIMETAKEN.

0 Karma
Highlighted

Re: How do I extract a time field and use it as the y axis?

Explorer

I thought about that too and modified made sure everything was in small caps, but still no result. Hmm curiouser and curiouser...

0 Karma
Highlighted

Re: How do I extract a time field and use it as the y axis?

SplunkTrust
SplunkTrust

to take a step back and sanity check. Can you run

| rex (?\d:\d+:\d+.\d+)? | table TIME_TAKEN

and post back what the values look like in the table? Im guessing they're all blank which would mean that the regex is still not correct.

0 Karma
Highlighted

Re: How do I extract a time field and use it as the y axis?

Explorer

You are right. The table is empty (I used small caps for both). Here is the result:http://www.flickr.com/photos/63543425@N07/5782440989/
But here is the result for another search: http://www.flickr.com/photos/63543425@N07/5783021226/

0 Karma