last status changed time

[roopeshetty]

Hi Guys,

I have this query , which will provide me the list of “Name” on which ProtectionStatus is OFF.

index=altiris sourcetype=altiris source=altiris_BGP_Excluded_WithREGION OR source=mi_input://altiris_BGP_Excluded_WithREGION | eval ProtectionStatus = if(Protectionstatus == 0, "OFF", "ON") | dedup Name | where ProtectionStatus="OFF"

ProtectionStatus goes OFF and ON frequently ( we run the query in every 6 hours). Here we need to add one more field or column which should show us the time (Date and time) when the last ProtectionStatus change happened (it can be either OFF to ON or ON to OFF). Can some one please help us on this.

[rnowitzki]

Hi @roopeshetty ,

This should give you a start:

| streamstats current=f window=1 last(ProtectionStatus) as prev_status
| eval statuschange=if(ProtectionStatus!=prev_status,"true", "false")

There was a similiar question recently. There is also another option using command "Delta" suggested by @manjunathmeti 

Cheers
Ralph

[roopeshetty]

Hi Ralph,

I added the your query at the end of my query (as pasted below) and ran it. It gave me a new field "statuschange" which has value as "false". But actually I want to see the date and time when the last ProtectionStatus change happened (it can be either OFF to ON or ON to OFF).

index=altiris sourcetype=altiris source=altiris_BGP_Excluded_WithREGION OR source=mi_input://altiris_BGP_Excluded_WithREGION | eval ProtectionStatus = if(Protectionstatus == 0, "OFF", "ON") | dedup Name | where ProtectionStatus="OFF" | streamstats current=f window=1 last(ProtectionStatus) as prev_status
| eval statuschange=if(ProtectionStatus!=prev_status,"true", "false")

[rnowitzki · Accepted solution]

You filtered on "OFF"  events before the streamstats, so you don't have any status changes any more after your query...

I removed your where command:

index=altiris sourcetype=altiris source=altiris_BGP_Excluded_WithREGION OR source=mi_input://altiris_BGP_Excluded_WithREGION 
| eval ProtectionStatus = if(Protectionstatus == 0, "OFF", "ON") 
| dedup Name 

| streamstats current=f window=1 last(ProtectionStatus) as prev_status
| eval statuschange=if(ProtectionStatus!=prev_status,"true", "false")

You could now add a filter for statuschange=true, so you only see the events where a change of status happened. 

| where statuschange="true"

[roopeshetty]

thanks Ralph. I got the output now. I really appreciate your help on this.