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.
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.
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.
Im not sure if I would want it in seconds. But ill see if there are other commands
If seconds are no good just use an eval to divide by 60 to get minutes etc.. | eval minutes=Time/60
How about if I want hours and minutes?
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.
Ill try that out right now. Would you know how to do the line graph?
Oh and the _time field wont work well because I extracted TIME from the log file.
The _time field will always be there. It's one of those fields, like _raw, that Splunk always extracts.
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.
Thank you somesoni2! I got the graph!
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.
How about if I want to put multiple lines... Like 25?
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.
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?
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.
Hm I moved eval lines are still not coming up
Okay I will try now thanks sideview
Would you know how to add a second line?
What you want to show in the second line?
host=.. source =.. SOMESTRINGTOLOOKFOR | timechart latest(TIME) as Completion_Time | eval Completion_Time=tonumber(replace(Completion_Time,"(\d+):(\d+):(\d+)","\1.\2"))