Splunk Search

Joining more information to the main results

matthewcanty
Communicator

Hello, I hope you can help me refine the following query. So far I am successfully getting the GroupId, Description, Date and count from the initial search and first join statement.

The three joins further down are intended to detect when a source is missing and then if not, print out a "Yes" to the user:

host=*90* Action=Norm* _myCategory
_sourceA OR _sourceB OR _sourceC 
| dedup Source GroupId 
| stats count by GroupId 
| join GroupId [search Action=PublishedGtpRule 
| fields + GroupId Description Date SportId] 
| eval pDate=strptime(Date, "%d/%m/%Y %H:%M:%S")-now() 
| where pDate>0 AND pDate<86400 AND count<3 
| join GroupId [search _sourceA | eval SourceAAvailable="Yes" ] 
| join GroupId [search _sourceB | eval SourceBAvailable="Yes" ] 
| join GroupId [search _sourceC | eval SourceCAvailable="Yes" ] 
| fields GroupId Description Date count SourceAAvailable SourceBAvailable SourceCAvailable

This query has two problems:

  1. Mainly it doesn't work as intended, however I have been trying to piece it together for a while and not quite hitting the mark.
  2. It takes a long time to come up with results and frequently causes the web server to stop responding/restart. I am aware this is probably due to the multiple joins each kicking off a search so if there was a neater way to do this that would be highly appreciated.

I'm trying to find out in a given category, which results have less than 3 sources. Once these have been found display a description and information to determine which sources are missing.

Many thanks to y'all.

Matt

Tags (4)
0 Karma
1 Solution

yannK
Splunk Employee
Splunk Employee

At first sight, I would say that you have too many sub searches, no proper grouping, so the same data has to be processed 4 times....

if the goals is to find the absence of groups, you need an static external list to compare.
PS : I did not understood the link between source and groupid, so you may have to do your own sauce.

  • add your source in all your stats.
  • replace all your join subsearches for the source by a simple static csv table or a lookup . (read the documentation for details)

with a line per groupid/source combo:

example :
source, GroupId, present
sourceA, group1,"yes"
sourceB, group2,"yes"
...

  • replace by an outer join | join mode=outer groupid source [ | inputcsv groupcsv ] | fillnull value="no" present
  • at the end the columns present should tell you if your data matched the list , and if an item of the list didn't match your data.

View solution in original post

yannK
Splunk Employee
Splunk Employee

At first sight, I would say that you have too many sub searches, no proper grouping, so the same data has to be processed 4 times....

if the goals is to find the absence of groups, you need an static external list to compare.
PS : I did not understood the link between source and groupid, so you may have to do your own sauce.

  • add your source in all your stats.
  • replace all your join subsearches for the source by a simple static csv table or a lookup . (read the documentation for details)

with a line per groupid/source combo:

example :
source, GroupId, present
sourceA, group1,"yes"
sourceB, group2,"yes"
...

  • replace by an outer join | join mode=outer groupid source [ | inputcsv groupcsv ] | fillnull value="no" present
  • at the end the columns present should tell you if your data matched the list , and if an item of the list didn't match your data.

matthewcanty
Communicator

There is some exmaple raw data here http://splunk-base.splunk.com/answers/73279/join-join-help just below MORE INFO

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

So... up until the where you're collecting events with less than three sources for the previous 24 hours, and after that you're trying to figure out which source is to blame for not being there?

Got a data example? Your approach feels quite cumbersome, maybe there's a much simpler way.

0 Karma

matthewcanty
Communicator

Updated at the bottom

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

What are you trying to achieve?

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...