topic Re: compare results of two searches in Splunk Search

compare results of two searches

aliroumani — Tue, 29 Sep 2020 10:45:07 GMT

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

Re: compare results of two searches

sundareshr — Thu, 25 Aug 2016 13:00:37 GMT

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]

Re: compare results of two searches

mhpark — Thu, 25 Aug 2016 13:07:22 GMT

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.

Re: compare results of two searches

gfuente — Thu, 25 Aug 2016 13:08:26 GMT

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.

Re: compare results of two searches

aliroumani — Tue, 29 Sep 2020 10:46:24 GMT

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

Re: compare results of two searches

justinatpnnl — Sun, 28 Aug 2016 06:47:39 GMT

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.

Re: compare results of two searches

mhpark — Mon, 29 Aug 2016 14:15:18 GMT

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

index=os_windows

search/where condition after the stats.

Re: compare results of two searches

aliroumani — Sun, 04 Sep 2016 05:07:49 GMT

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