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.
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
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
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..
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.
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.
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.
@xiantros, does it solve your problem?