topic Re: Match and compare fields with different names and from different sources in Splunk Search

Match and compare fields with different names and from different sources

garciajbg — Wed, 30 Sep 2020 04:57:10 GMT

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

Search 1 gives me a list of "vm_name"
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.

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

jpolvino — Tue, 14 Apr 2020 16:59:23 GMT

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

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

garciajbg — Tue, 14 Apr 2020 17:51:07 GMT

@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.)"

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

jpolvino — Tue, 14 Apr 2020 18:12:21 GMT

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

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

garciajbg — Tue, 14 Apr 2020 18:23:45 GMT

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.

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

garciajbg — Tue, 14 Apr 2020 18:40:45 GMT

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

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

jpolvino — Tue, 14 Apr 2020 18:43:39 GMT

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.

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

garciajbg — Tue, 14 Apr 2020 18:58:45 GMT

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.

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

jpolvino — Tue, 14 Apr 2020 20:54:44 GMT

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

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

garciajbg — Wed, 30 Sep 2020 04:57:26 GMT

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 vm_name AS hostname ]
[ search index="2" source="2" group=tcpin_connections | dedup hostname ]"

Additionally, if I remove "rename vm_name AS hostname" from the search, I am able to see both indexes, sourcetypes, source, vm_name, 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.

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

garciajbg — Wed, 30 Sep 2020 05:12:06 GMT

Ok, I was able to finally able to get everything tabled correctly by adding "appendcols" between both searches. See below.

I then tried to add the below to try a filter out matching IP's between "sourceIp and private_ip_address". This kinda works but also creates weird formatting with final results. Since all IP's listed in "sourceIp" will also be listed in "private_ip_addresses" but NOT vice versa I need to list the delta between the two. The below gives me IPs that match between both but I can not compare the difference due to some formating. Is there a better way to list the "private_ip_address" IPs that are not found in "sourceIp'?