How to find field value changes for two environments?

[Fe-atSplunk]

There are two environments, INT and PROD. The value of IREFFECTIVEDATE in INT is always the same, as is PROD, however they have different values. I want to know when the value of IREFFECTIVEDATE in its environment changes. Here is a log sample:

2022-03-04 14:13:00.006, IREFFECTIVEDATE="2016-07-01 00:00:00.0", IRLOANRATE="5"

So far my search is this:

index= xy sourcetype=xy
| eval env = if(host=="prod1", "PROD", "INT")
| table IREFFECTIVEDATE IRLOANRATE env
| head 1
| eval single_value="IREFFECTIVEDATE : ".IREFFECTIVEDATE." | IRLOANRATE : ".IRLOANRATE." |  Environment : ".env"
| fields single_value
| sort 0 _time
| streamstats current=f last(IREFFECTIVEDATE) as priorDate last(_time) as priorTime by env
| where NOT (IREFFECTIVEDATE=priorDate)
| mvcombine single_value delim="
"
| nomv single_value

Streamstats recognizes the changing value but it needs to be split by env.

Any ideas please?

[somesoni2]

Your search is selecting just one event (as it uses "| head 1").  How are you comparing two IREFFECTIVEDATE values?

[Fe-atSplunk]

For better visibility I'm showing in Statistics what was sent last (as we get those alerts once an hour). Looking back further I get lost of Events. 

As seen here there are two events per hour, one PROD, one INT but nothing changes from the last entry

[somesoni2]

What's the time range you use in your alert? Last two hours?

[Fe-atSplunk]

That's a good point, the alerts is looking back at the last one hour so it doesn't have anything to compare to. I'll try it with 2 hours. The problem is it takes 2 hours to wait for results

[somesoni2]

Based on samples, you're receiving events around 13th minute of every hour so you can setup your alert to run shortly after that (e.g. run on 15th minute) with time-range of last 120 minute so that you'll cover data for this hour and last hour and get your alert sooner.

[Fe-atSplunk]

Sorry that still doesn't work. At the moment the last two logs are both Prod and Stats show one event so it would still trigger an alert even though the value hasn't changed. Any ideas?

[ITWhisperer]

Try this

| streamstats current=f global=f last(IREFFECTIVEDATE) as priorDate last(_time) as priorTime by env

[Fe-atSplunk]

Hi, 

Thanks for your response but it doesn't do anything. The previous event is PROD and the next one is INT, it still creates an incident event though the value of INT is what it always is. 

[ITWhisperer]

Here is a runanywhere example showing it working

| gentimes start=-1 increment=1h
| rename starttime as _time
| rename endhuman as IREFFECTIVEDATE
| eval env=mvindex(split("PROD|INT","|"),random()%2)
| streamstats current=f global=f last(IREFFECTIVEDATE) as priorDate last(_time) as priorTime by env
| fieldformat priorTime=strftime(priorTime,"%F %T")

Obviously, if it isn't working for you, something else is amiss!