Archive
Highlighted

Match and compare fields with different names and from different sources

Explorer

Ok, so I a trying my best to evaluate the differences between two search results.

Search 1 gives me a list of "vmname"
index="1" sourcetype="1" source="1" | search state="running" | table vm
name

Search 2 gives me a list of "hostname"
index="2" source="2*" group=tcpin_connections | dedup hostname | table hostname

Each search is crafted from two different indexes and sourcetypes.

Both of these lists share common field values. For example, in search 1 vm_name can be named "MYPC" and on search 2 hostname is also "MYPC". Both are named MYPC and in reality, they are one and the same. However, I need to create a list that will essentially compare the values of both searches and if they match subtract them from one another and create the NEW list. The goal to remove MATCHED results from both searches to create a "DELTA's" list.

I have tried the "join" command but when I do the results from the second search results are completely messed up. I tried created lookups and added them to one search but I have the same problem. The only thing I can think of is maybe the issue is the search itself may yield metadata somewhere that screws up the results. For example, on search 2 I need to add "dedup hostname" to the search to retrieve an accurate list.

0 Karma
Highlighted

Re: Match and compare fields with different names and from different sources

Builder

One way to do this is to set up a multisearch:

| multisearch
[ search index="1" sourcetype="1" source="1" | search state="running" | table vm_name | rename vm_name AS hostname ]
[ search index="2" source="2*" group=tcpin_connections | dedup hostname | table hostname]
| stats count(index) AS Vol first(index) AS index by hostname
| where Vol=1
| eval message=if(index==1,"Host belongs to index 1 only","Host belongs to index 2 only")
| fields hostname message

You can test this with some "run anywhere" SPL:

| makeresults
| eval raw="index=1,sourcetype=1,source=1,hostname=1a index=1,sourcetype=1,source=1,hostname=1b index=1,sourcetype=1,source=1,hostname=1c index=1,sourcetype=1,source=1,hostname=1d index=1,sourcetype=1,source=1,hostname=1e index=2,sourcetype=,source=2,hostname=2a index=2,sourcetype=,source=2,hostname=2b index=2,sourcetype=,source=2,hostname=1a index=2,sourcetype=,source=2,hostname=2g "
| makemv raw | mvexpand raw | rename raw AS _raw | kv
| table index sourcetype,source,hostname
| stats count(index) AS Vol first(index) AS index by hostname
| where Vol=1
| eval message=if(index==1,"hostname belongs to index 1 only","hostname belongs to index 2 only")
| fields hostname message
0 Karma
Highlighted

Re: Match and compare fields with different names and from different sources

Explorer

@jpolvino I tried the multisearch command but received the following error.

"Error in 'multisearch' command: Multisearch subsearches may only contain purely streaming operations (subsearch 1 contains a non-streaming command.)"

0 Karma
Highlighted

Re: Match and compare fields with different names and from different sources

Builder

Get rid of table vm_name in the first subsearch, and table hostname in the second subsearch.

0 Karma
Highlighted

Re: Match and compare fields with different names and from different sources

Explorer

Got the following error.

"Error in 'multisearch' command: Multisearch subsearches may only contain purely streaming operations (subsearch 2 contains a non-streaming command.)"

However, the "dedup hostname" is needed in order to accurately list hosts. If I remove it from the search I get the list of all "vm_name" values in the Hostname field and "Host belongs to index 2 only" on ALL the message field values.

0 Karma
Highlighted

Re: Match and compare fields with different names and from different sources

Explorer

I also tried running it with "stats count by hostname" and get the same results

0 Karma
Highlighted

Re: Match and compare fields with different names and from different sources

Builder

So your first 3 lines should look like this:

| multisearch
[ search index="1" sourcetype="1" source="1" | search state="running" | rename vm_name AS hostname ]
[ search index="2" source="2*" group=tcpin_connections | dedup hostname ]

Is this what you have? If so, then all events will have "hostname" which is needed later on.

0 Karma
Highlighted

Re: Match and compare fields with different names and from different sources

Explorer

Yes, that is what I have. The error I get when I try to run it is "subsearch 2 contains a non-streaming command". It does not like the "dedup hostname" on search 2.

0 Karma
Highlighted

Re: Match and compare fields with different names and from different sources

Builder

Ok, leave the dedup in there, and change the top line from multisearch to union

0 Karma
Highlighted

Re: Match and compare fields with different names and from different sources

Explorer

So I can see the search results when using the first half of the search you suggested...

"| union [ search index="1" sourcetype="1" source="1" | search state="running" | rename vmname AS hostname ]
[ search index="2" source="2" group=tcpin
connections | dedup hostname ]"

Additionally, if I remove "rename vmname AS hostname" from the search, I am able to see both indexes, sourcetypes, source, vmname, and hostname.
So I then tried to table to hostname field by itself and I get a list of the hostname but also a ton of blank results. Same for viewing the vm_name field by itself.

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.