How to create a line graph in XML that displays the (latest time - earliest time) which would be the duration.

[alanxu]

Background: Every day, I run a script on a log file which filters it into another file. I then indexed the filtered file. So I want to create a line graph that takes the earliest time and latest time (first line and last line of the filtered file) and display it on a line graph.

For example: In the log file I will have...

2013-06-20 03:12:30,001 INFORMATION Begin TITLE OF SCRIPT
 .
 .
 .
. 
 2013-06-20 04:12:30,001 INFORMATION Finished TITLE OF SCRIPT

So the fields I have at the moment are..... Date = 2013-06-20, date_hour=04, date_minute=12, date_second=30, position=finished

What I have so far for my search is...

  <row>
     <panel>
       <chart>
         <title>Completion Time</title>
         <searchString>host=.... source=.... | stats max(_time) AS Latest min(_time) AS Earliest | eval temp=Latest - Earliest | table temp | makemv temp | mvexpand temp | rename temp as _time | eval Series=1</searchString>
         <earliestTime>$timetoken.earliest$</earliestTime>
         <latestTime>$timetoken.latest$</latestTime>
         <search>
           <query>host=.... source=..... | stats max(_time) AS Latest min(_time) AS Earliest | eval temp=Latest - Earliest | table temp | makemv temp | mvexpand temp | rename temp as _time | eval Series=1</query>
           <earliest>$timetoken.earliest$</earliest>
           <latest>$timetoken.latest$</latest>
         </search>
                 <option name="charting.chart">line</option>
                 <option name="charting.axisY2.enabled">false</option>
                 <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
                 <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
                 <option name="charting.axisTitleX.visibility">visible</option>
                 <option name="charting.axisTitleY.visibility">visible</option>
                 <option name="charting.axisTitleY2.visibility">visible</option>
                 <option name="charting.axisX.scale">linear</option>
                 <option name="charting.axisY.scale">linear</option>
                 <option name="charting.axisY2.scale">inherit</option>
                 <option name="charting.chart.bubbleMaximumSize">50</option>
                 <option name="charting.chart.bubbleMinimumSize">10</option>
                 <option name="charting.chart.bubbleSizeBy">area</option>
                 <option name="charting.chart.nullValueMode">zero</option>
                 <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
                 <option name="charting.chart.stackMode">default</option>
                 <option name="charting.chart.style">shiny</option>
                 <option name="charting.drilldown">all</option>
                 <option name="charting.layout.splitSeries">0</option>
                 <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
                 <option name="charting.legend.placement">right</option>
                 <option name="charting.axisTitleY.text">Completion Time</option>
                 <option name="charting.drilldown">all</option>
             </chart>
     </panel>
   </row>

[woodcock]

Try something like this:

... | rex "(?<BeginTime>\d+-\d+-\d+-\s+\d+:\d+:\d+),\d+\s+INFORMATION Begin\s+(?<ScriptTitle>.*)" | rex "(?<EndTime>\d+-\d+-\d+-\s+\d+:\d+:\d+),\d+\s+INFORMATION Finished\s+(?<ScriptTitle>.*)" | reverse | streamstats current=t last(BeginTime) AS BeginTime by ScriptTitle | where isnotnull(EndTime) | eval duration = strptime(EndTime, "%Y-%m-%d %H:%M:%S") - strptime(BeginTime, "%Y-%m-%d %H:%M:%S") | timechart span=1h avg(duration) BY ScriptTitle

[alanxu]

I will try this right now. Just need to change your answer a little because the informaation given above it off.

[woodcock]

How did it work?

[diogofgm]

Might not be the most performant command but, assuming the files have different names and you have that position field you can try:

| transaction source startswith="position=begin" endswith="position=finished"

This will create a duration field.

[diogofgm]

Just |timechart duration or |table _ time duration and you get a chart with all durations over time

[alanxu]

So it will create the duration field would it automatically become a dot on a line graph?