How to show the result only if it is latest than the subsearch result?

[Naren26]

Assume, I have two panels - PanelA, PanelB. I have to show the result in PanelA only if the event for train is more recent than for the same train in PanelB:

Panel A:

Time      Train    CurrentStation
10        TrainA     StationA
10:15     TrainB     StationA

Panel B:

Time     Train   CurrentStation
10:20    TrainB    StationB
10.20    TrainC    StationB

So, in Panel A, I should not show the TrainB record as the most recent event for TrainB is in Panel B.

Can I use the subsearch for this? Like passing the Time and Train fields from subsearch to main search and compare the main search result's time with sub search time? In that case, how my search query will be?

How can I achieve this? Please suggest.

[niketn · Accepted solution]

@Naren26, you can use post-processing to separate results out for Station A and Station B panels.

Since in Splunk events are sorted in reverse chronological order, performing | dedup Train, will give you latest station for specific train. You can create a base search with this query.
PS:

1. I have created base search baseTrainSearch inside a panel just to show the results in example. You can have just <search id="baseTrainSearch">...</search> without creating a separate table panel in a row i.e. just have the following code.

       <search id="baseTrainSearch">
         <query>|  makeresults
   

   | eval data="time=2017/11/21 10:00:00,Train=TrainA,CurrentStation=StationA;time=2017/11/21 10:15:00,Train=TrainB,CurrentStation=StationA;time=2017/11/21 10:20:00,Train=TrainB,CurrentStation=StationB;time=2017/11/21 10:20:00,Train=TrainC,CurrentStation=StationB;"
   | makemv data delim=";"
   | mvexpand data
   | rename data as _raw
   | KV
   | eval _time=strptime(time,"%Y/%m/%d %H:%M:%S")
   | sort - _time
   | fieldformat _time=strftime(_time,"%H:%M")
   | table _time Train CurrentStation
   | dedup Train
   -24h@h
   now
   1
   

2. Above search is a run anywhere search with sample data as per the question. You can fit in your current search instead and just pipe the final | dedup Train command

3. Using post processing i.e. <search base="baseTrainSearch"> get the results from base search and For Panel A search filter only results with | search CurrentStation="StationA"

4. Using post processing i.e. <search base="baseTrainSearch"> get the results from base search and For Panel B search filter only results with | search CurrentStation="StationB"

Following is the complete code for run anywhere dashboard:

<dashboard>
  <label>Train Station Post Processing</label>
  <row>
    <panel>
      <title>Sample Table to show Base Search Results</title>
      <table>
        <search id="baseTrainSearch">
          <query>|  makeresults
|  eval data="time=2017/11/21 10:00:00,Train=TrainA,CurrentStation=StationA;time=2017/11/21 10:15:00,Train=TrainB,CurrentStation=StationA;time=2017/11/21 10:20:00,Train=TrainB,CurrentStation=StationB;time=2017/11/21 10:20:00,Train=TrainC,CurrentStation=StationB;"
|  makemv data delim=";"
|  mvexpand data
|  rename data as _raw
|  KV
|  eval _time=strptime(time,"%Y/%m/%d %H:%M:%S")
|  sort - _time
|  fieldformat _time=strftime(_time,"%H:%M")
|  table _time Train CurrentStation
|  dedup Train</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <title>Station A</title>
      <table>
        <search base="baseTrainSearch">
          <query>|  search CurrentStation="StationA"</query>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
    <panel>
      <title>Station B</title>
      <table>
        <search base="baseTrainSearch">
          <query>|  search CurrentStation="StationB"</query>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>    
  </row>  
</dashboard>

Please try out and confirm.

[Naren26]

This is what I was looking for. Thank you @niketnilay

[gcusello]

Hi Naren26,
I don't know your search so I give you an example to extract you solution:

index=IndexA OR index=indexB 
| eval TimeA=if(index=IndexA,strptime(Time,"your_time_format"),""),TimeB=if(index=IndexB,strptime(Time,"your_time_format"),"")
| stats earliest(TimeA) AS TimeA earliest(TimeB) AS TimeB BY Train
| where TimeA>TimeB
| eval TimeA=strptime(TimeA,"your_time_format"),TimeB=strptime(TimeB,"your_time_format")
| table Train TimeA TimeB

Beware to the format of Time field.

Bye.
Giuseppe

[niketn]

@Naren26, can you give your current search for Panel B, also an explanation as to why Panel B might not have latest results and what is your need for two panels (can they not be in same panel). Basically give us the use case of Panel A and Panel B's significance and separation.

[Naren26]

I have updated my question by adding additional column 'CurrentStation' for much more clarity. I need to show the current status of the trains between specific time range in two different panels (StationA in Panel A and StationB in Panel B). Hence, I cannot show all the results in same panel. In fact, both the panels should have the latest results. But the same train cannot be in both the panels.

I could not able to frame the query, as I do not have an idea of how to approach it.