SPL Find difference from previous day from a DB Source

[LizAndy123]

We currently have a DB Connection and a Query which brings in Project Details with the username Entitlements.

The Query runs at 10pm each night and logs into Splunk - no issue

I want to write a SPL where it will do a daily check from the previous day and just out any changes as in a entitlement was removed or added.

Sample Event is ProjectID="1234", Data_Security_Details="9:user1,user3"

Next day it coule be ProjectID="1234", Data_Security_Details="9:user1"

So I just need to know user3 was removed.

[gcusello]

Hi @LizAndy123 ,

it's really difficoult to help you without having a view on the fields that you want to compare, anyway, I'll try to give you a generic answer.

If ProjectID is the correlation field and Data_Security_Details is the field to check, you could run somering line this:

index=<your_index> earliest=-2d@d latest=@d
| eval 
     day=strftime(_time,"%d"),
     period=if(day=strftime(now()-86400,"%d"),"last_day","previous_day")
| stats 
     values(eval(period="last_day")) AS Data_Security_Details_last_day
     values(eval(period="previous_day")) AS Data_Security_Details_previous_day
     BY ProjectID

In this way, you can compare values of a field in two different periods.

Ciao.

Giuseppe

[LizAndy123]

Hey that actually helps - how can I only show a project that has changed?

[gcusello · Accepted solution]

Hi @LizAndy123 ,

you have only to add the condition check at the end of the search:

index=<your_index> earliest=-2d@d latest=@d
| eval 
     day=strftime(_time,"%d"),
     period=if(day=strftime(now()-86400,"%d"),"last_day","previous_day")
| stats 
     values(eval(period="last_day")) AS Data_Security_Details_last_day
     values(eval(period="previous_day")) AS Data_Security_Details_previous_day
     BY ProjectID
| search Data_Security_Details_last_day!=Data_Security_Details_previous_day

 Ciao.

Giuseppe

[bowesmana]

@gcusello you can't use search field!=field, has to be | where 

[gcusello]

Hi @bowesmana ,

yes correct!

I always forgot it and I have to replace the command!

Ciao.

Giuseppe

[bowesmana]

Just add a clause after the stats command, like

| where Data_Security_Details_previous_day!= Data_Security_Details_last_day

and you will only retain data where a change has occurred.

[LizAndy123]

Also want to add - the ProjectID can be any Project (this never changes) So ProjectID=1234 or ProjectID=4321