Dashboards & Visualizations

Why am I not able to use join, subsearch, and lookup properly?

mohammadsharukh
Path Finder

Task:- Need to identify what all Mcafee A.V agents have latest updates happening

work done:-

1)Created a lookup and added all the unique source IP, total 54

2) Created a search to lookup for only the mcafee agents that have been updated and added a value 0 for tracking and then used join statement to merget it with lookup created earlier with value 1.

Problem statement:- I am looking for srcip/agents that are not update i.e not present in the logs but present in the lookup and its not showing me the result but when i want to do the otherway around i.e looking for common srcip/agent in both lookup and search logs. PFA snaps

Please help me rectify the query as per snap 2:- Non common valuescommmon entrycommmon entrynon common entrynon common entrylookup valuelookup value

Please refer the 

 

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
<your index search>
| stats count by src_ip
| eval value=1
| fields src_ip value
| append
  [| inputlookup abc.csv
   | eval value=2]
| stats sum(value) as value by src_ip
| where value=2

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust
<your index search>
| stats count by src_ip
| eval value=1
| fields src_ip value
| append
  [| inputlookup abc.csv
   | eval value=2]
| stats sum(value) as value by src_ip
| where value=2

mohammadsharukh
Path Finder

Dear ITWhisper,

Your sol. Sovled my problem but still i have 3 doubt.

1) Whats the difference between join and append commad?

2) For me, why my previous query with join command was not working? 

3) the sol. You provided is there any limitations with append command? Like limits on no. Of rows returned and all

 

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

1) join will look for a match between all left events among the right events - if you have a join type of "left", the left events will be kept even if there isn't a match from the right, otherwise, they are dropped.

append simply extends the event pipeline with more events leaving the existing events intact.

2) Your join wasn't working because only the events from the index were kept, none of the events from the csv were added if they didn't match events from the index search.

3) As with all subsearches (whether join or append), there are limits to the number of events returned (50,000). If you have more than 50,000 event in your subsearch, you will need to find a way to break up the subsearch into smaller chunks. You appear to only have 54 events in your csv so you should be OK.

mohammadsharukh
Path Finder

Appreciated. Thanks for the solution and detailed explanation.

Regards,

Sharukh

 

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...