Splunk Search

How can I compare two values obtained from a search and a lookup table?

Berfomet96
Explorer

Hello,

For the past week I've been working in a way to run some queries for a report about vulnerability findings.

I have made a lookup table for the vulnerability details and I call that to the main query to do the work. However, I'm currently having a bit of trouble trying to figure out the scheduled query to run in order to update the vulnerabilities details lookup table.

Since Rapid 7 sometimes doesn't import well their vulnerability definitions to splunk (i.e: there are 270000 lines but for some reason, some day only 12000 gets imported into splunk) I wanted to make some validations before deciding to run  the outputlookup to update the table. To do this I had deviced this so far:

 

 

index=rapid7 sourcetype="rapid7:insightvm:vulnerability_definition"
| dedup id
| lookup soc_vulnerabilities.csv vulnerability_id OUTPUT vulnerability_id title description 
| stats count as today
| append 
    [| inputlookup soc_vulnerabilities.csv
    | stats count as yesterday]
| eval prov=yesterday
| eval conditional=if(today>=yesterday,1,0)
| table conditional, today, yesterday, prov

 

 

As you can see, all I'm doing is validating if the amount of lines being imported to splunk are the same or greater than the current amount of lines stored in the lookup table. Thing is, the eval with the conditional isnt working because both total values are being shown as if they were unrelated, which they kind of are. The result table is as follows:

conditionaltodayyesterdayprov
0238732  
0 238732238732

What I want is to compare both today and yesterday values in order to determine if the lookup table should or should not be updated.

I've been looking at the documentation for a way to make it work and also checked some other posts here in the forums but I haven't found a similar case. I hope it's not because it is impossible, nevertheless, I'd appreciate if you guys could help me to figure this out or should I try to solve this problem from other perspective.

Additional info: For those who have worked with this logs before, vulnerability_id field in that sourcetype doesn't exists, so we created it via CLI in the normalization options thing.

Thanks in advance.

Labels (2)
Tags (2)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

You can't compare different fields in different events

index=rapid7 sourcetype="rapid7:insightvm:vulnerability_definition"
| stats dc(id) as today
| appendcols
    [| inputlookup soc_vulnerabilities.csv
    | stats count as yesterday]
| eval prov=yesterday
| eval conditional=if(today>=yesterday,1,0)
| table conditional, today, yesterday, prov

 Rather than using append, use appendcols to get the 'yesterday' values into the same event as today.

Note - your first lookup seemed pointless as it served no purpose and I changed the stats count to stats dc.. which avoids the dedup.

 

View solution in original post

0 Karma

bowesmana
SplunkTrust
SplunkTrust

You can't compare different fields in different events

index=rapid7 sourcetype="rapid7:insightvm:vulnerability_definition"
| stats dc(id) as today
| appendcols
    [| inputlookup soc_vulnerabilities.csv
    | stats count as yesterday]
| eval prov=yesterday
| eval conditional=if(today>=yesterday,1,0)
| table conditional, today, yesterday, prov

 Rather than using append, use appendcols to get the 'yesterday' values into the same event as today.

Note - your first lookup seemed pointless as it served no purpose and I changed the stats count to stats dc.. which avoids the dedup.

 

0 Karma

Berfomet96
Explorer

Thanks a lot, just as I posted this I learned about appendcols via a post I found in stackoverflow.

Anyways, thanks a lot for the recomendations, I'll take them in consideration to make the query cleaner.

0 Karma

bowesmana
SplunkTrust
SplunkTrust

Yes, it's always the way that having taken the time to carefully construct your post and spent ages trying to figure it out - you get the solution yourself 2 milliseconds after you hit the post button 😀

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