Splunk Search

Spanning event between start and end time and displaying it on one chart

New Member

Hi,
I need some help displaying events on a time chart.
In each event, I have a start time and end time field in epoch time.
I need to display these events on one time chart even if the times overlap.
For example:
A sample dataset (CSV):
average_packet_len, src_ip, dest_ip, start_time,end_time

These are the fields. I want to display the average_packet_len between start_time and end_time for multiple events on one time chart.

0 Karma

Legend

@vitalysim87, sorry for the delay in my response. With your original post with average packet length I was under impression that you do not have duration field available to plot. Based on the details provided, as stated earlier, Timeline Custom Visualization can be used to plot the duration on x-axis with average_packet_length on y-axis.

Following is the run anywhere dashboard example (PS: I have sorted average_packet_length in Timeline Custom Visualization using counter prefix as the visualization will apply default ascending sort otherwise.)

alt text

Following is the run anywhere Simple XML Dashboard code:

  <row>
    <panel>
      <viz type="timeline_app.timeline">
        <search>
          <query>| makeresults
| eval data="54,89.212.67.202,56.10.10.45,20/12/2017 13:00,20/12/2017 13:02;52,110.20.40.100,56.10.10.45,20/12/2017 13:01,20/12/2017 13:05;1500,45.20.200.35,56.10.10.45,20/12/2017 13:03,20/12/2017 13:08;1000,202.200.78.90,56.10.10.45,20/12/2017 13:00,20/12/2017 13:09"
| makemv data delim=";"
| mvexpand data
| eval data=split(data,",")
| eval average_packet_len=mvindex(data,0), src_ip=mvindex(data,1), dest_ip=mvindex(data,2), start_time=mvindex(data,3),end_time=mvindex(data,4)
| eval _time=strptime(start_time,"%d/%m/%Y %H:%M") 
| eval end_time=strptime(end_time,"%d/%m/%Y %H:%M") 
| eval duration = (end_time - _time) * 1000
| sort - average_packet_len
| streamstats count
| eval average_packet_len=count." - ".average_packet_len
| table _time average_packet_len duration</query>
          <earliest>1513753200</earliest>
          <latest>1513756800</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
        <option name="timeline_app.timeline.axisTimeFormat">SECONDS</option>
        <option name="timeline_app.timeline.colorMode">categorical</option>
        <option name="timeline_app.timeline.maxColor">#DA5C5C</option>
        <option name="timeline_app.timeline.minColor">#FFE8E8</option>
        <option name="timeline_app.timeline.numOfBins">6</option>
        <option name="timeline_app.timeline.tooltipTimeFormat">SECONDS</option>
        <option name="timeline_app.timeline.useColors">0</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </viz>
    </panel>
  </row>

Please try out and confirm!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Legend

@vitalysim87, Timeline Custom Visualization is used to plot duration by category based on time on one axis. Your data is to be plotted is not duration but average packet length. If you want to plot the same on timechart with span of 1 minute, you can use column or line chart instead. Please find below the run anywhere dashboard which runs on static time based on your data between 12/20 12:30 PM to 12/20:1:30 pm. Also the search query has commands till | eval average_packet_len... to generate sample data as per your question. You can put your current base search instead.

 <YourBaseSearch>
| eval _time=strptime(start_time,"%d/%m/%Y %H:%M") 
| timechart span=1m max(average_packet_len) as average_packet_len

PS: Since your data ingested itself has average_packet_len per minute(not absolute packet length every minute), I have used max() statsistical function. You can also pick last() instead, but you should be careful in picking up most other functions like avg(), stdev() etc, since the data is already aggregated average.

<dashboard>
  <label>Plotting Timechart</label>
  <row>
    <panel>
      <chart>
        <search>
          <query>| makeresults
| eval data="54,89.212.67.202,56.10.10.45,20/12/2017 13:00,20/12/2017 13:02;52,110.20.40.100,56.10.10.45,20/12/2017 13:01,20/12/2017 13:05;1500,45.20.200.35,56.10.10.45,20/12/2017 13:03,20/12/2017 13:08;1000,202.200.78.90,56.10.10.45,20/12/2017 13:00,20/12/2017 13:09;"
| makemv data delim=";" 
| mvexpand data
| eval data=split(data,",")
| eval average_packet_len=mvindex(data,0), src_ip=mvindex(data,1), dest_ip=mvindex(data,2), start_time=mvindex(data,3),end_time=mvindex(data,4)
| fields - data
| eval _time=strptime(start_time,"%d/%m/%Y %H:%M") 
| timechart span=1m max(average_packet_len) as average_packet_len</query>
          <earliest>1513753200</earliest>
          <latest>1513756800</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <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.abbreviation">none</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.abbreviation">none</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.abbreviation">none</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">line</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.showDataLabels">none</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">none</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.mode">standard</option>
        <option name="charting.legend.placement">right</option>
        <option name="charting.lineWidth">2</option>
        <option name="refresh.display">progressbar</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </chart>
    </panel>
  </row>
</dashboard>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Legend

@vitalysim87, were you able to try out this example?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

New Member

@niketnilay, I tried your example, but it's not what I'm looking for.
Please see the screenshot (The bottom graph)

0 Karma

New Member

Here is a sample data set in a CSV format (the timestamp in the real data set is in ephoc time):

average_packet_len, src_ip, dest_ip, start_time,end_time
54,89.212.67.202,56.10.10.45,20/12/2017 13:00,20/12/2017 13:02
52,110.20.40.100,56.10.10.45,20/12/2017 13:01,20/12/2017 13:05
1500,45.20.200.35,56.10.10.45,20/12/2017 13:03,20/12/2017 13:08
1000,202.200.78.90,56.10.10.45,20/12/2017 13:00,20/12/2017 13:09

The expected output is a timeline graph that shows the total average sum of packet len in each minute:

alt text

0 Karma

Revered Legend

Can you post some sample data and corresponding expected output?

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!