I have a search query which gives me the following information in the table:
Device | MsgType | TimeStamp
A |MSG1 | 2016 11 08
Also, I have a kvstore which has for the sake of my question, let us consider the following fields with the following entries:
Device | MsgType |timestamp
A |MSG1 |2016 11 07
A |MSG1 |2016 11 06
A |MSG1 |2016 11 05
A |MSG2 |2016 11 07
B |MSG1 |2016 11 07
What I want to do is compare, how many times, MSG1 has occurred on Device A in the last two days (for 11/6 and 11/7 in the above example). So in the above solution, my resultant table should be the following :
Device | MsgType | TimeStamp | 2DayHistory
A |MSG1 | 2016 11 08 | 2
What about using JOIN?
your search|join max=99 Device,MsgType [search |inputlookup your kvlookup_table|rename timestamp as sub_timestamp]|eval wk_timestamp=relative_time(strptime(timestamp, "%Y %m %d"),"-2d@d") |eval wk_sub_timestamp=strptime(sub_timestamp, "%Y %m %d")|where wk_timestamp<=wk_sub_timestamp|stats count by Device,MsgType
Device | MsgType | TimeStamp
A |MSG1 | 2016 11 08
Device | MsgType |timestamp
A |MSG1 |2016 11 07
A |MSG1 |2016 11 06
A |MSG1 |2016 11 05
A |MSG2 |2016 11 07
B |MSG1 |2016 11 07
JOIN
Device | MsgType | TimeStamp | sub_timestamp
A |MSG1 | 2016 11 08|2016 11 07
A |MSG1 | 2016 11 08|2016 11 06
A |MSG1 | 2016 11 08|2016 11 05
↓
WHERE
Device | MsgType | TimeStamp | sub_timestamp
A |MSG1 | 2016 11 08|2016 11 07
A |MSG1 | 2016 11 08|2016 11 06
What about using JOIN?
your search|join max=99 Device,MsgType [search |inputlookup your kvlookup_table|rename timestamp as sub_timestamp]|eval wk_timestamp=relative_time(strptime(timestamp, "%Y %m %d"),"-2d@d") |eval wk_sub_timestamp=strptime(sub_timestamp, "%Y %m %d")|where wk_timestamp<=wk_sub_timestamp|stats count by Device,MsgType
Device | MsgType | TimeStamp
A |MSG1 | 2016 11 08
Device | MsgType |timestamp
A |MSG1 |2016 11 07
A |MSG1 |2016 11 06
A |MSG1 |2016 11 05
A |MSG2 |2016 11 07
B |MSG1 |2016 11 07
JOIN
Device | MsgType | TimeStamp | sub_timestamp
A |MSG1 | 2016 11 08|2016 11 07
A |MSG1 | 2016 11 08|2016 11 06
A |MSG1 | 2016 11 08|2016 11 05
↓
WHERE
Device | MsgType | TimeStamp | sub_timestamp
A |MSG1 | 2016 11 08|2016 11 07
A |MSG1 | 2016 11 08|2016 11 06
Thanks a lot. This works somewhat in a manner I required it to. However, a quick question :
There might be an issue with using join. I am getting the following informational message - "[subsearch]: Search Processor: Subsearch produced 53099 results, truncating to maxout 50000.". Is it because my kvstore has more than 50000 events? If yes, is there any way to return all events?
Please refer to the manual.
Each of the sub search result and the JOIN result has a default value. Please change.
http://docs.splunk.com/Documentation/Splunk/latest/Admin/Limitsconf?ac=partner_smt