topic compare record from yesterday to today if field equals in Splunk Search

compare record from yesterday to today if field equals

zachsisinst — Wed, 02 Feb 2022 03:27:52 GMT

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

Re: compare record from yesterday to today if field equals

ITWhisperer — Tue, 01 Feb 2022 22:41:21 GMT

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"

Re: compare record from yesterday to today if field equals

PickleRick — Tue, 01 Feb 2022 23:20:38 GMT

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

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

Re: compare record from yesterday to today if field equals

ITWhisperer — Tue, 01 Feb 2022 23:41:16 GMT

Because the previous day might also be terminated not active?

Re: compare record from yesterday to today if field equals

zachsisinst — Wed, 02 Feb 2022 03:30:31 GMT

This works great! Thank you!!

Re: compare record from yesterday to today if field equals

johnhuang — Wed, 02 Feb 2022 07:11:13 GMT

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

Re: compare record from yesterday to today if field equals

PickleRick — Wed, 02 Feb 2022 09:22:52 GMT

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.