Splunk Search

Multiple match in lookup File

mamohta
New Member

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

0 Karma
1 Solution

HiroshiSatoh
Champion

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

View solution in original post

0 Karma

HiroshiSatoh
Champion

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

0 Karma

mamohta
New Member

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?

0 Karma

HiroshiSatoh
Champion

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...