Multiple match in lookup File

[mamohta]

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

[HiroshiSatoh · Accepted solution]

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

[mamohta]

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?

[HiroshiSatoh]

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