Splunk Search

How to join two queries where one query is always static?

macadminrohit
Contributor

Hi,

I have to create a table in splunk which is basically with two queries out of which one is always static i.e the field value filters will be static. I already have one part of the query which gives a stats table, I want to join the static query to this. Basically, I am trying to compare the Applications installed on two some servers with one static server(baseline server)

index=main sourcetype="Script:InstalledApps" host=server1 DisplayName="*"  DisplayVersion="*"
|rex field=_raw "DisplayName=(?\w.*)$" | search Name="Microsoft*"
|stats count by host Name DisplayVersion | fields - count 

The above query will give stats table for Appname, the version installed on Server1. I want to add another column to the table which gives the stats table for baseline server. The fields DisplayName will be same, the only field which would differ is DisplayVersion.

Final table :
host         Name            DisplayVersion            baselineServer
server1    service1        18.2.3                                 18.2.5
server1    service2         5.2.3                               5.5.0
server1    service3         4.5.9                               4.6.8
server1    service4         2.0.1                               2.3.4

index=main sourcetype="Script:InstalledApps" host=server1 DisplayName="*"  DisplayVersion="*"
|rex field=_raw "DisplayName=(?\w.*)$" | search Name="Microsoft*"
|stats count by host Name DisplayVersion | fields - count

index=main sourcetype="Script:InstalledApps" host=baselineServer DisplayName="*"  DisplayVersion="*"
|rex field=_raw "DisplayName=(?\w.*)$" | search Name="Microsoft*"
|stats count by host Name DisplayVersion | fields - count

I want to join these two queries and get the above layout

0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

index=main sourcetype="Script:InstalledApps" host=server1 DisplayName="*"  DisplayVersion="*"
 |rex field=_raw "DisplayName=(?<Name>\w.*)$" | search Name="Microsoft*"
 |stats count by host Name DisplayVersion | fields - count
| append [search index=main sourcetype="Script:InstalledApps" host=baselineServer DisplayName="*"  DisplayVersion="*"
 |rex field=_raw "DisplayName=(?<Name>\w.*)$" | search Name="Microsoft*"
 |stats count by host Name DisplayVersion | rename DisplayVersion as BaselineServerVersion | table Name BaselineServerVersion ]
| stats values(*) as * by Name | table host Name *

View solution in original post

0 Karma

elliotproebstel
Champion

Can you clarify what this part is supposed to do: |rex field=_raw "DisplayName=(?\w.*)$"Are you trying to extract a new field called Name out of the _raw text? The way it's structured now, I don't think it runs.

But assuming that part is working fine for you, I think something like this might work:

index=main sourcetype="Script:InstalledApps" (host=server1 OR host=baselineServer) DisplayName="*" DisplayVersion="*" 
| rex field=_raw "DisplayName=(?\w.*)$" 
| search Name="Microsoft*" 
| eval baselineServer=if(host="baselineServer", DisplayVersion, NULL), DisplayVersion=if(host="server1", DisplayVersion, NULL)
| stats list(DisplayVersion) AS DisplayVersion, list(baselineServer) AS baselineServer BY host Name
| fields host Name DisplayVersion baselineServer
0 Karma

elliotproebstel
Champion

By the way - when you say that the baselineServer part is always static, do you mean that the DisplayVersion field that you're returning here will always be the same? If so, it would be a good idea to convert the data to a lookup table. Then you can run your search over various hosts and use the related fields to consult the lookup table and return the baselineServer version number. It will be more efficient and clearer.

0 Karma

macadminrohit
Contributor

No baselinServer, is the fieldvalue pair of one of the query which will always remain same. in my case host=server1 will always be same. I need to compare the values of that with all other servers in my lookup file.

0 Karma

elliotproebstel
Champion

Gotcha. In that case, what I've proposed should work for you - with the caveat that the rex you posted is invalid. I'm guessing it might need to be this:

| rex field=_raw "DisplayName=(?<Name>\w.*)$"
0 Karma

somesoni2
Revered Legend

Give this a try

index=main sourcetype="Script:InstalledApps" host=server1 DisplayName="*"  DisplayVersion="*"
 |rex field=_raw "DisplayName=(?<Name>\w.*)$" | search Name="Microsoft*"
 |stats count by host Name DisplayVersion | fields - count
| append [search index=main sourcetype="Script:InstalledApps" host=baselineServer DisplayName="*"  DisplayVersion="*"
 |rex field=_raw "DisplayName=(?<Name>\w.*)$" | search Name="Microsoft*"
 |stats count by host Name DisplayVersion | rename DisplayVersion as BaselineServerVersion | table Name BaselineServerVersion ]
| stats values(*) as * by Name | table host Name *
0 Karma

macadminrohit
Contributor

Thanks Somesh, What is the logic behind this query?

Also i want to add another column in this which compares the version, if it is less than Baseline server, it should say less, more if More and if that component doesnt exist in either of that should state that.

0 Karma

somesoni2
Revered Legend

Firstly, just want to point out that this query works only for single server (means you're providing a single host in the base search). If you're planning to use multiple servers, you'd need to change it to use a rather expensive version which uses join command.

The logic is that first you build host-Name-hostVersion table, then append Name-baselineVersion table using subsearch, then the last stats command run over Name, which generates a table with host (will return server1), hostVersion from first search result and baselineVersion from appended subsearch result.

Now to add a new field to compare it, you'd have split out the major.minor.build from both version and compare. like this

Above search which gives field host Name DisplayVersion BaselineServerVersion 
| rex field=DisplayVersion "(?<Dmajor>\d+)\.(?<Dminor>\d+)\.(?<Dbuild>\d+)"
| rex field=BaselineVersion "(?<Bmajor>\d+)\.(?<Bminor>\d+)\.(?<Bbuild>\d+)"
| eval DVNumber=Dmajor*10000+Dminor*100+Dbuild
| eval BVNumber=Bmajor*10000+Bminor*100+Bbuild
| eval result=case(DVNumber>BVNumber,"more",DVNumber<BVNumber,"less",true(),"same") | fields - *major *minor *build *VNumber
0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...