topic Re: How to get common values in two different fields in two different searches in Splunk Search

How to get common values in two different fields in two different searches

xiantros — Fri, 28 Jun 2019 08:50:11 GMT

Hi all, I'd be grateful if you could help me with this. I have read other similar questions but none of them seem to solve the problem.

I have two searches:
Search 1: index=main source=os
Search 2: index=patch sourcetype=csv

In search 1, there is a field that has workstation IDs, and the field is called 'ComputerName'
In search 2, the same field exists but the name is 'extracted_Hosts'

So what I want to do is look at both searches and get workstation IDs that exist in both, and then use these events to pipe into another function.

Example:
Search 1: index=main source=os | stats values(ComputerName)
Result:
W123456
W789123
W456789
W123321
W789987

Actual number of results is 30,271

Search 2: index=patch sourcetype=csv | stats values(extracted_Host)
W123456
W154658
W789123
W546589
W789987

Actual number of results is 18,672

So the output I want is:
W123456
W789123
W789987

Your help is much appreciated!

Here is what I want to do with the data, grateful if you can help with this but I think I need to figure out the above before that.

The search 1 (source=os) has a field called 'BuildNumber' which refers to the Windows 10 build version. The search 2 (index=patch) has a field called 'patchlevel' which is the same as 'BuildNumber', but they are different numbers, so for example a version of Windows 10 could have BuildNumber = 1703 and patchlevel = 15063. I want to use the data from the previous search which gives me the workstations that are common in both sources, and then get a count of each Build Number and patch level, and see if there is any discrepancy.

Hope I've explained this well enough, let me know if I haven't! Thanks again.

Re: How to get common values in two different fields in two different searches

richgalloway — Fri, 28 Jun 2019 12:35:11 GMT

See if this gets you close to what you want.

(index=main source=os) OR (index=patch sourcetype=csv) 
| eval computer = coalesce(ComputerName, extracted_Host)
| stats values(BuildNumber) as BuildNumbers, values(patchlevel) as patchlevels by computer

Re: How to get common values in two different fields in two different searches

xiantros — Fri, 28 Jun 2019 13:27:38 GMT

Thanks for your answer. Correct me if I'm wrong but seems to put all values in 'ComputerName' and 'extracted_Host' fields into one field called 'computer'? I want to make sure that the values I get exist are in both fields, and I don't want any value that exists in only one field..

Re: How to get common values in two different fields in two different searches

richgalloway — Wed, 30 Sep 2020 01:08:03 GMT

ComputerName and extracted_Host are not present in both indexes so we can't select events that have both.
The coalesce function, however, creates a new field that contains the value of either ComputerName or extracted_Host, whichever is not null. That allows the stats function to match up the two indexes and produce the desired results.

Re: How to get common values in two different fields in two different searches

xiantros — Sat, 29 Jun 2019 09:00:42 GMT

Ok so in this case let's say index 'main' has values in ComputerName as a, b, d, e and index 'patch' has values for extracted_Host as a, b, c, f. Is the field created by coalesce function going to give me a, b, c, ,d e, f or a, b ?

I want the result to be a, b as they are the common values that exist in both.

Re: How to get common values in two different fields in two different searches

richgalloway — Tue, 02 Jul 2019 12:56:02 GMT

The coalesce function returns a single value that is the first of its arguments which is not null.
Keep in mind the function operates on one event at a time so it will only see one value for ComputerName and one value for extracted_Host.

Re: How to get common values in two different fields in two different searches

woodcock — Fri, 05 Jul 2019 23:11:54 GMT

Like this:

(index=main AND source=os) OR (index=patch AND sourcetype=csv
| eval ComputerName=coalesce(ComputerName, extracted_Host)
| stats dc(index) AS index_count dc(BuildNumber) AS BuildNumber_count dc(patchlevel) AS patchlevel_count BY ComputerName
| where index_count > 1 AND BuildNumber_count != patchlevel_count

Re: How to get common values in two different fields in two different searches

ankitkanchan — Tue, 08 Oct 2019 07:56:24 GMT

@xiantros, does it solve your problem?