compare results of two searches

[aliroumani]

i have to searches :
1) index=symantec_sep sourcetype="symantec:ep:scan:file" | dedup dest |table dest | sort dest
2) index=os_windows Workstation_Name="*"| dedup Workstation_Name | table Workstation_Name | sort Workstation_Name

both shows the workstations in environment (1st named as dest from symantec sep) & (2nd is named Workstation_Name).
i need to run as earch to compare the results of both searches, remove duplicates and show me only missing machines:
ex:
1st search result is:
dest
abcd1020
fgh123
bnm1n1

2nd search result is:
Workstation_Name
kil123
abcd1020
fgh123

result should show two columns named (dest) and (Workstation_Name) and showing only missing machines in both, like:
dest
bnm1n1

Workstation_Name
kil123

thanks for your help in advance guys ..................

[gfuente]

Hello

You could use the set command

| set diff [search index=symantec_sep sourcetype="symantec:ep:scan:file" | dedup dest |table dest  ] [search index=os_windows Workstation_Name="*"| dedup Workstation_Name | table Workstation_Name | rename Workstation_Name as dest ]

But this is not very good at performance. It should be better doing something like:

(index=symantec_sep sourcetype="symantec:ep:scan:file") OR (index=os_windows Workstation_Name="*") | rename Workstation_Name as dest | dedup dest, sourcetype | stats dc(sourcetype) as sourcetypes by dest | where sourcetypes=1

The idea is to normalize the computer name in the dest field, then dedup by sourcetype and dest fields. And count the number of disctict sourctypes per dest. If you only have one then that computer appears only in one sourcetype.

[mhpark · Accepted solution]

Since your indices are different,

(index=symantec_sep sourcetype="symantec:ep:scan:file") OR (index=os_windows Workstation_Name="*")
| eval host_name = coalesce(dest, Workstation_Name)
| stats dc(index) as occurrence by host_name
| where occurrence < 2

coalesce merges two field values into one, so it's the key.

[aliroumani]

Thank you mhpark,
this is working and i can see the missing results.
but my questions now is how can i know in which index this result appears!?

for ex:
1 host_name occurrence is 1, which means its in one index search result not in the other one, but how can i tell in which one!?
because actually, I'm interested to know the host names that are in index=os_windows and not in index=symantec_sep

thanks in advance

[justinatpnnl]

You could try making a small addition to the stats command:

(index=symantec_sep sourcetype="symantec:ep:scan:file") OR (index=os_windows Workstation_Name="*")
 | eval host_name = coalesce(dest, Workstation_Name)
 | stats dc(index) as occurrence, values(index) as indexes by host_name
 | where occurrence < 2

This should give you the name of the index it was found in.

[mhpark]

Yep, this would do the trick.
and you could add the

index=os_windows

search/where condition after the stats.

[aliroumani]

perfect answer 🙂
thanks a million guys .. i really appreciate it.

[sundareshr]

Try this

index=symantec_sep sourcetype="symantec:ep:scan:file" | dedup dest | table dest | sort dest | appendcols [search index=os_windows Workstation_Name="*"| dedup Workstation_Name | table Workstation_Name | sort Workstation_Name]