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

[macadminrohit]

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

[elliotproebstel]

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

[elliotproebstel]

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.

[macadminrohit]

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.

[elliotproebstel]

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.*)$"

[somesoni2 · Accepted solution]

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 *

[macadminrohit]

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.

[somesoni2]

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