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

[mohammadsharukh]

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 entrynon common entrylookup value

Please refer the 

[ITWhisperer · Accepted solution]

<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]

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

[ITWhisperer]

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]

Appreciated. Thanks for the solution and detailed explanation.

Regards,

Sharukh