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

johnhuang
Motivator

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

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

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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...