- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- Multiple match in lookup File
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
More Ways To Control Your Costs With Archived Metrics | Register for Tech Talk
.conf24 | Personalize your .conf experience with Learning Paths!
Threat Hunting Unlocked: How to Uplevel Your Threat Hunting With the PEAK Framework ...
