Splunk Search

How to compare multiple values of a field with the corresponding values of another field and add a new value based on the comparison result?

sureshmurgan
Path Finder

I want to check for list of applications installed and its versions from all the PCs in my environment. If all the list of applications and the expected versions are present in the PC, I want to tag it as compliant.

Sample Data: Consider the following data added to Splunk, PC, Name, Version are the field names and I have added some values to it.
PC : x,y
Name : Adobe, Chrome, vlc
Version : 10(Adobe), 11(Chrome), 12(vlc) (--> I added App Name within brackets for our understanding, Version field only has the numeric values 10,11 and 12 given here)

My Requirement : I want to check for each PC x and y, If all three applications Adobe,Chrome and vlc are installed with the version given above or newer version.

0 Karma
1 Solution

ololdach
Builder

Hi,
maybe the input data could look like this:

 _time PC InstalledAppName InstalledAppVersion
<sometime> X Adobe 10.2
<sometime> X Chrome 11.x
<sometime> X vlc 12
<someothertime> Y Adobe 9.x
<someothertime> Y vlc 14.6

Ideally a compliance.csv file would hold the versions required for compliance:

RequiredAppName RequiredAppVersion
Adobe 10
Chrome 11
vlc 12

and the query could possibly work like this:

...|lookup compliance.csv RequiredAppName as InstalledAppName output RequiredAppVersion | eval status=if(InstalledAppVersion<RequiredAppVersion,1,0) | stats sum(status) as status by PC | eval compliance=if(status>0,"Non-Compliant","Compliant")|...

For every application installed, the required version is being looked up and assigned a status of 0 (ok) or 1 (not ok). The reason is that if you sum the status and any one or more apps are non-compliant, the result will be !=0. Only if all apps are compliant, the sum is 0.

Hope it helps.
Oliver

View solution in original post

0 Karma

ololdach
Builder

Hi,
maybe the input data could look like this:

 _time PC InstalledAppName InstalledAppVersion
<sometime> X Adobe 10.2
<sometime> X Chrome 11.x
<sometime> X vlc 12
<someothertime> Y Adobe 9.x
<someothertime> Y vlc 14.6

Ideally a compliance.csv file would hold the versions required for compliance:

RequiredAppName RequiredAppVersion
Adobe 10
Chrome 11
vlc 12

and the query could possibly work like this:

...|lookup compliance.csv RequiredAppName as InstalledAppName output RequiredAppVersion | eval status=if(InstalledAppVersion<RequiredAppVersion,1,0) | stats sum(status) as status by PC | eval compliance=if(status>0,"Non-Compliant","Compliant")|...

For every application installed, the required version is being looked up and assigned a status of 0 (ok) or 1 (not ok). The reason is that if you sum the status and any one or more apps are non-compliant, the result will be !=0. Only if all apps are compliant, the sum is 0.

Hope it helps.
Oliver

0 Karma

sureshmurgan
Path Finder

This looks like a nice logic. Will attempt this and get back. Thank you 🙂

0 Karma

sureshmurgan
Path Finder

This worked well, thanks for that Oliver. Actually my requirement is a lot more complex. I will try to explain it here,
This is the query am using now,
| pivot Application_ApplicationInventory
latest(InstallDate) as InstallDate
latest(DisplayVersion) as DisplayVersion
splitrow
host
splitrow
DisplayName
filter host in (101,102,103,104)
filter DisplayName in ("Adobe","Chrome","vlc")
| where isnotnull(DisplayName)
| eval PreReq=case((DisplayName="Adobe" AND DisplayVersion < "7.5") OR (DisplayName="Chrome" AND DisplayVersion < "2.0") OR (DisplayName="vlc" AND DisplayVersion < "6.0"),"1",true(),"0")

This is giving me the result as intended for the PCs where its installed. I have some more requirements and need to include those conditions as well,
1. Need to mark the PCs as non compliant where any of these applications is not installed at all. It would be nice if we know what application is not installed but not mandatory at this point.(This data is not available directly. I have an option to append the PCs separately but search goes longer.)
2. Compliance for another app AdobeAcrobat (this will not be present in all PCs but we need to consider only the PCs where its installed and check for the version to mark it as compliant or noncompliant)
3. Compliance for two apps IE and Mozilla (either IE or Mozilla will be present in the PCs, so if any of them is present - check for the respective version and mark as compliant or noncompliant. Also, here when both these apps are not installed we have to mark it as noncompliant)

Like I have said before we have to check for all these and mark a PC as compliant or noncompliant.
I have tried to explain as clearly as I could. Any suggestion is greatly appreciated.

0 Karma

sureshmurgan
Path Finder

Your suggestion have worked and I have marked it as the answer. I will post the addon queries in a separate thread. Thanks again Oliver!

0 Karma

ololdach
Builder

Hi sureshmurgan,
thank you, I'm glad I could help. Your added complexity can easily be solved with the lookup based logic that I suggested:

For the different requirements per PC, define a lookup that defines different classes of PCs: appmap.csv

ClassName,AppName,AppStatus
OfficeStd,Word,Required
OfficeStd,Excel,Optional
OfficeFin,Word,Required
OfficeFin,Excel,Required

Create yet another lookup for the mapping between PC classes and PCs: classmap.csv

PC,ClassName
X, OfficeStd
Y,OfficeFin

A search to check if a PC has all required applications installed would be someting like this:
... search to get an event for every InstalledAppName installed on PC ... | transaction PC | lookup classmap.csv PC output ClassName | eval AppStatus="Required" | lookup appmap.csv ClassName,AppStatus output AppName | eval AppsMissing=if(InstalledAppName!=AppName,"Apps Missing","Apps Compliant") ...

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