Splunk Search

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)

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

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

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

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

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 time_taken!=TIME_TAKEN.

0 Karma

Explorer

Hi, I tried rex (?\d:\d+:\d+.\d+)? | convert dur2sec(TIME_TAKEN) | timechart sum(TIME_TAKEN) 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

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(TIME_TAKEN) but if I add | timechart max(TIME_TAKEN) (or any other function for that matter) I get no results in the max(TIME_TAKEN) column.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!