Splunk Search

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

xiantros
Engager

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.

0 Karma

woodcock
Esteemed Legend

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
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.
0 Karma

xiantros
Engager

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

xiantros
Engager

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

ankitkanchan
New Member

@xiantros, does it solve your problem?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...