compare record from yesterday to today if field equals

[zachsisinst]

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

{
   Department: Engineering
   Employee_Number: 4642
   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_Date: 01/31/2022
   Full_Name: Jane Doe
}

How would I create a search that would compare the "Employment Status" For 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

[johnhuang]

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

[PickleRick]

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

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

[ITWhisperer]

Because the previous day might also be terminated not active?

[PickleRick]

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.

[ITWhisperer · Accepted solution]

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]

This works great! Thank you!!