Splunk Search

last status changed time

roopeshetty
Path Finder

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.

Labels (2)
0 Karma
1 Solution

rnowitzki
Builder

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"

 

--
Karma and/or Solution tagging appreciated.

View solution in original post

0 Karma

rnowitzki
Builder

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

--
Karma and/or Solution tagging appreciated.
0 Karma

roopeshetty
Path Finder

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")

 

0 Karma

rnowitzki
Builder

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"

 

--
Karma and/or Solution tagging appreciated.
0 Karma

roopeshetty
Path Finder

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

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...