Splunk Search

Match and compare fields with different names and from different sources

garciajbg
Explorer

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.

0 Karma

jpolvino
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

garciajbg
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

jpolvino
Builder

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

0 Karma

garciajbg
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

garciajbg
Explorer

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

0 Karma

jpolvino
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

garciajbg
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

jpolvino
Builder

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

0 Karma

garciajbg
Explorer

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

| union [search index=1 sourcetype=1 source=1 | search state="running" | table vm_name private_ip_address ] | appendcols [ search index=2 source="2" group=tcpin_connections | dedup hostname | table hostname sourceIp ]

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'?

| eval fan=mvappend("A","B") | mvexpand fan | eval matchIP=if(fan="A",private_ip_address,sourceIp) | stats dc(fan) as matchcount, values(*) as * by matchIP | where matchcount > 1

0 Karma

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

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

🍂 Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...