topic Re: I am trying to check for a field value is unchanged in last two day or if possible two business days. in Splunk Search

How to check that a field value is unchanged in last two day or if possible two business days?

vgiri8 — Wed, 03 Aug 2022 15:19:50 GMT

I am checking for reboot required, if yes, since how long is the status unchanged from reboot required yes. Logic I am waiting for atleast 2 business days before I send alert to user to reboot his machine. Thank you so much for you help.

I did check an answer but did not get it. https://community.splunk.com/t5/Splunk-Search/Get-data-from-the-last-2-business-days/m-p/539517

Re: I am trying to check for a field value is unchanged in last two day or if possible two business days.

martinpu — Wed, 03 Aug 2022 10:58:09 GMT

Something like this?

| stats max(_time) as _time latest(status) as status by host 
| eval now=now()
| convert ctime(now) as day_of_week timeformat="%w"
| eval day_of_week_variable=case(day_of_week=0 OR day_of_week=1, 2,day_of_week=6 or day_of_week=2,1,1=1,0)
| eval day_age_of_last_message=(now()-_time)/60/60/24-day_of_week_variable)
| eval reboot_required=if(day_age_of_last_message>2,"reboot required", "not required")

You would need to provide more info on what your source data looks like to make something more specific.

What field are you using to determine status?

If the current weekday is monday or sunday (0 or 1) then it takes -2 days, if it is saturday or tuesday (6,2) then it takes -1 days off from the ticker.

Re: How to check that a field value is unchanged in last two day or if possible two business days?

vgiri8 — Thu, 11 Aug 2022 12:48:02 GMT

index=my_index sourcetype=my_sourcetype isDecommissioned="false" isUninstalled="false" | stats max(_time) as _time latest(threatRebootRequired) as status by host
| eval now=now()
| convert ctime(now) as day_of_week timeformat="%w"
| eval day_of_week_variable=case(day_of_week=0 OR day_of_week=1, 2,day_of_week=6 or day_of_week=2,1,1=1,0)
| eval day_age_of_last_message=((now()-_time)/60/60/24-day_of_week_variable)
| eval reboot_required=if(day_age_of_last_message>2,"threatRebootRequired=true", "threatRebootRequired=false")

| eval day_age_of_last_message=((now()-_time)/60/60/24-day_of_week_variable). //in this eval a open braces was missing, which I have added now.

| eval reboot_required=if(day_age_of_last_message>2 AND status = true,"reboot required", "not required") //What I am supposed to give here, still I do not get the desired results of only showing results >2. I have also added status= true, to remove messages which are older than 2 days but do not require reboot.

I would like to have the data compared with older logs to check, if the reboot required is still true since last two days.

Re: How to check that a field value is unchanged in last two day or if possible two business days?

yuanliu — Fri, 12 Aug 2022 06:08:25 GMT

Would this simple search do? Assuming threatRebootRequired is reported every 5 minutes,

index=my_index sourcetype=my_sourcetype isDecommissioned="false" isUninstalled="false" | bin span=5m _time | stats min(_time) as start max(_time) as end by threatRebootRequired | where threatRebootRequired == "true" and now() - end < 300 and now() - start > 2*86400

The above should give you those needing reboot for > 2 days. To get those needing reboot for > 2 business days, add a week day filter, like

Re: How to check that a field value is unchanged in last two day or if possible two business days?

vgiri8 — Fri, 12 Aug 2022 09:26:49 GMT

Hi @yuanliu ,

I worked on the query you shared, but I am getting data as below, I would like to sort by computerName and I am using span=1d here.

When I add the last part of the query (where threatRebootRequired == "true" and now() - end < 300 and now() - start > 2*86400) I do not get any results.

Please find the attached screenshot

Re: How to check that a field value is unchanged in last two day or if possible two business days?

yuanliu — Fri, 12 Aug 2022 17:05:35 GMT

Before addressing why your filter came out empty, I need to reveal a bigger problem with my previous code: stats should group by _time bucket as well as by host.

Now to the reason why the filter came empty, even though your end time is 6 days after start time (which is not what it is meant to measure because of my mistake): that "now() - end < 300" needs to be adjusted according to the collection period of threatRebootRequired as well as your time bucket. For the simplistic strategy to be correct, the collection period should be equal to or larger than the time bucket which you set to be 1d. Assuming the two are equal, that expression should be adjusted to "now() - end < 86400", i.e., that the last event at this state came in less than 1d ago. If collection period is longer, adjust 86400 to that period to match the last collected event. If collection period is shorter than time bucket, you can use latest(threatRebootRequired) as a second filter. But that kind of ruins the simplicity.

Taking these two, you can try this

index=my_index sourcetype=my_sourcetype isDecommissioned="false" isUninstalled="false" | bin span=1d _time | stats min(_time) as start max(_time) as end by host _time threatRebootRequired | where threatRebootRequired == "true" and now() - end < 86400 and now() - start > 2*86400

Re: How to check that a field value is unchanged in last two day or if possible two business days?

vgiri8 — Wed, 24 Aug 2022 13:08:00 GMT

@yuanliu

I have attached the screenshot of (a single host, as an example) for the result I am getting if I use the below query, If I had the rest of the query, I do not get any results. We are getting the data once per day.

index=my_index sourcetype=my_sourcetype isDecommissioned="false" isUninstalled="false"
| bin span=1d _time
| stats min(_time) as start max(_time) as end by host _time threatRebootRequired
| where threatRebootRequired == "true"

Re: How to check that a field value is unchanged in last two day or if possible two business days?

yuanliu — Wed, 24 Aug 2022 15:52:11 GMT

@vgiri8 The screenshot reveals a different problem by setting _time bucket to just 1 day. The bucket should greater than the amount of the test boundary. With this in mind, could you test the simpler form, without group by _time for now?

index=my_index sourcetype=my_sourcetype isDecommissioned="false" isUninstalled="false" | stats min(_time) as start max(_time) as end by threatRebootRequired | where threatRebootRequired == "true" and now() - end < 86400 and now() - start > 2*86400

Re: How to check that a field value is unchanged in last two day or if possible two business days?

vgiri8 — Thu, 25 Aug 2022 07:55:22 GMT

Hi @yuanliu,

I tried as you mentioned but the number host is now only one, compared to many host in the last query. Please find attached screenshot of current output.
Time range is took is same for both old and new query(without the bin _time)

Re: How to check that a field value is unchanged in last two day or if possible two business days?

yuanliu — Thu, 25 Aug 2022 20:23:04 GMT

Apologies again - group by host is always needed

index=my_index sourcetype=my_sourcetype isDecommissioned="false" isUninstalled="false" | stats min(_time) as start max(_time) as end by threatRebootRequired host | where threatRebootRequired == "true" and now() - end < 86400 and now() - start > 2*86400