Splunk Search

compare record from yesterday to today if field equals

zachsisinst
Explorer

Say I have a batch job that pushes JSON records that look like this on Monday: 

{
   DepartmentEngineering
   Employee_Number4642
   Employment_Status: Active
   Termination_Date
   Full_Name: Jane Doe

}

But on Tuesday A new record gets pushed like this: 

{
   Department: Engineering
   Employee_Number: 4642
   Employment_Status: Terminated
   Termination_Date01/31/2022
   Full_Name: Jane Doe

}

How would I create a search that would compare the "Employment StatusFor each record, and only return the records that transitioned to "Terminated"  within the last 2 days? 

I tried the following, but it's not working. 


index=myinventory sourcetype=HR earliest=-2d@d
| eventstats earliest(_time) as earliestEventTime by Employment_Status
| dedup FullName, Employment_Status
| where Employment_Status!="Active"
| table _time, earliestEventTime, FullName, Employment_Status

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

ITWhisperer
SplunkTrust
SplunkTrust

You could do something like this

index=myinventory sourcetype=HR earliest=-2d@d
| stats values(Employment_Status) as Employment_Status latest(Employment_Status) as Last_Status values(FullName) as FullName by Employee_Number
| where mvcount(Employment_Status)>1 and Last_Status="Terminated"

View solution in original post

johnhua
Builder

Here's a different approach. Since you have the termination date in the daily update, you can simply key off of that and calculate where term_age_days=1.

index=myinventory sourcetype=HR earliest=-1d@d
| search Employment_Status="Terminated"
| eval term_age_days=ROUND((RELATIVE_TIME(NOW(),"-0d@d")-STRPTIME(Termination_Date, "%m/%d/%Y"))/86400)
| where term_age_days=1
| table _time, FullName, Employment_Status, Termination_Date, term_age_days

 

 

0 Karma

PickleRick
Ultra Champion

And simple search for Employment_Status="Terminated" is not enough because?

Btw, -2d@d is not "last 24h"

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Because the previous day might also be terminated not active?

0 Karma

PickleRick
Ultra Champion

Ahh, OK. That was not obvious from the initial specifications. We only want the accounts that returned some other status as well as "Terminated"? But that's not very well specified since "Transitioned to Terminated within last 2 days" might as well mean that the account was Active just before our search range and transitioned to Terminated right at the beginning of it. If there is a guarantee that we have at least one event per day per single account, everything should be ok as long as we extend a bit the search range and check the last Terminated timestamp. But if not - that's different use case.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

You could do something like this

index=myinventory sourcetype=HR earliest=-2d@d
| stats values(Employment_Status) as Employment_Status latest(Employment_Status) as Last_Status values(FullName) as FullName by Employee_Number
| where mvcount(Employment_Status)>1 and Last_Status="Terminated"

zachsisinst
Explorer

This works great! Thank you!! 

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...