Splunk Search

How do I make a line graph with my timechart search?

alanxu
Communicator

alt text

This is a table I created using the timechart command. Now, I am trying to make a line graph with this information with date on the x-axis and completion time for the y-axis. If I am able to solve that, I would love to add extra lines to the line graph.

<searchString>host=... source = ... Position="Finished" AND Report  | timechart latest(TIME) as Completion_Time</searchString>
<earliestTime>-168h</earliestTime>
<latestTime>$timetoken.latest$</latestTime>

This is inside my dashboard atm. I have a drop down for time at the top.

Tags (3)
0 Karma
1 Solution

somesoni2
Revered Legend

The Completion_Time field is string value and as far as I know, strings can't be plotted in Y-axis (you need numerical values). Workaround, you can convert your Completion_Time to decimal number like HH.MM and plot them.

View solution in original post

sideview
SplunkTrust
SplunkTrust

What you'd be better off using here is the convert command. Convert has a dur2sec function that is specifically designed for turning strings like "02:34:59" into an integer number of seconds.

host=... source = ... Position="Finished" AND Report | convert dur2sec(TIME) | timechart max(TIME)

http://docs.splunk.com/Documentation/Splunk/6.2.5/SearchReference/Convert

to format it as a line chart you need to edit the visualization. Here it looks like you're in a dashboard panel so make sure you're logged in as an admin, or as someone with edit rights to the dashboard, and click "Edit > Edit Panels". When the panels reload with little edit icons in their top right, the middle icon is the Visualization one, and in that menu there's a "Line" option.

Otherwise if/when you're in the generic Search UI you would look under the "visualization" tab. In both cases setting the visualization to Line chart is pretty straightforward.

alanxu
Communicator

Im not sure if I would want it in seconds. But ill see if there are other commands

0 Karma

sideview
SplunkTrust
SplunkTrust

If seconds are no good just use an eval to divide by 60 to get minutes etc.. | eval minutes=Time/60

0 Karma

alanxu
Communicator

How about if I want hours and minutes?

0 Karma

tskinnerivsec
Contributor

you should be using timechart on _time field

timechart latest(_time) as "Completion_Time" | convert ctime("Completion_Time") | . . .

the convert ctime converts _time to a human readable format for you.

alanxu
Communicator

Ill try that out right now. Would you know how to do the line graph?

0 Karma

alanxu
Communicator

Oh and the _time field wont work well because I extracted TIME from the log file.

0 Karma

tskinnerivsec
Contributor

The _time field will always be there. It's one of those fields, like _raw, that Splunk always extracts.

0 Karma

somesoni2
Revered Legend

The Completion_Time field is string value and as far as I know, strings can't be plotted in Y-axis (you need numerical values). Workaround, you can convert your Completion_Time to decimal number like HH.MM and plot them.

View solution in original post

alanxu
Communicator

Thank you somesoni2! I got the graph!

0 Karma

sideview
SplunkTrust
SplunkTrust

Note - using the convert command (see my answer here) or at least moving the eval command to being before the timechart command, (and having the eval operate on the TIME field instead of the Completion_Time of course) will be necessary if you want to put a "by someOtherField" into your timechart to chart multiple series. See my answer for details about convert.

alanxu
Communicator

How about if I want to put multiple lines... Like 25?

0 Karma

sideview
SplunkTrust
SplunkTrust

That's just a matter of setting limit right in the timechart command. I'd advise setting it to a very high number like 100 or 500. You can however turn off the limit entirely with limit=0 but in the extremely unlikely event where you're search starts generating 1000 series values, the report would die with a weird error message instead of rendering suddenly with an OTHER" column.

alanxu
Communicator

So I am trying to create the graph now... my search is...

host=... source = "..." SP Position="Finished" | timechart limit=0 latest(TIME) as Completion_Time by FinishedName |  eval Completion_Time=tonumber(replace(Completion_Time,"(\d+):(\d+):(\d+)","\1.\2"))

So I get the x axis having dates, y axis with numbers, and a legend including all the FinishedNames. However, there are no lines on graph. Is it because something is not a number, but a string?

0 Karma

sideview
SplunkTrust
SplunkTrust

Again, whether you use convert or eval you are going to need to move it before the timechart command. See my other answer, or my earlier comment on this answer. After the timechart command, there is no field called Completion_Time. Each command just passes rows of key value pairs (fields) to the next command. Therefore if you look at the tabular (aka "statistics") output of the timechart command, those are the exact rows of key value pairs that you're sending to the eval. It's failing because Completion_Time was removed by timechart and transformed into fields that are _time and then one field for each value of FinishedName. Move the eval (or convert) command to before the timechart and it'll be able to do its work properly.

alanxu
Communicator

Hm I moved eval lines are still not coming up

0 Karma

alanxu
Communicator

Okay I will try now thanks sideview

0 Karma

alanxu
Communicator

Would you know how to add a second line?

0 Karma

somesoni2
Revered Legend

What you want to show in the second line?

alanxu
Communicator
 host=.. source =.. SOMESTRINGTOLOOKFOR   | timechart latest(TIME) as Completion_Time | eval Completion_Time=tonumber(replace(Completion_Time,"(\d+):(\d+):(\d+)","\1.\2"))
0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!