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 ..................
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.
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.
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.
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
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.
Yep, this would do the trick.
and you could add the
index=os_windows
search/where condition after the stats.
perfect answer 🙂
thanks a million guys .. i really appreciate it.
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]