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.
@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:
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
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
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"
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, 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:
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
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
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"
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.
This is what I was looking for. Thank you @niketnilay
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
@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.
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.