Getting Data In

How do i display only specific field values in a table

ajacobi
Path Finder

Hi All,

I'm ingesting data about device health state and want to create a dashboard that only shows the device services if they are critical, warning or unknown. But i need to still include the "OK" state in the search to ensure that i get the latest result. The other factor i need to take into account is there are two different fields that may display the state. One is a daily state update (svc_state) the other is a state change alert (alert_state). This ensures that if there are any issues with data missing (which unfortunately has occurred due to issues outside of Splunk) the state should be as accurate as possible. 

So basically i only want to display the output in the table if it is the latest reported state and it is either critical, warning or unknown

This is what i have so far but obviously it isn't displaying what i want. Also if anyone can think of a way that i could create this as a post processing search as it has numerous panels but the only change to the search is the device name it would be greatly appreciated.

index=abc device=dev service=* | eval current_state = mvappend(alert_state, svc_state) | dedup service | where current_state=CRITICAL OR current_state=WARNING OR current_state=Unknown | table service current_state

Thanks

Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

If I understand correctly, you want to display the latest of either state, so you will need to get the latest of each state and the latest time of each of those and then work out which is valid

index=abc device=dev service=* 
| stats latest(alert_state) as alert_state latest_time(alert_state) as latest_alert_state latest(svc_state) as svc_state latest_time(svc_state) as latest_svc_state by service
| eval current_state=case(
  isnull(alert_state) AND isnull(svc_state), "Unknown",
  isnull(alert_state), svc_state,
  isnull(svc_state), alert_state,
  latest_alert_state>latest_svc_state, alert_state, 
  1==1, svc_state)
| where current_state="CRITICAL" OR current_state="WARNING" OR current_state="Unknown"
| table service current_state

The stats command will collect the latest data, then the eval statement looks at the possible 5 options of different possible states.

Note a couple of issues you have in your original query

a) where command cannot work with multi-value fields when using = for comparison, so you need  to use mvfind()

b) where command requires an eval statement syntax, so where current_state=CRITICAL is comparing two fields, current_state and CRITICAL, not the string "CRITICAL" which must be quoted.

Hope this helps

 

View solution in original post

0 Karma

ajacobi
Path Finder

Sorry for the delay responding. Had to take some time off.

It appears the issue i had with values not displaying only relates to one particular service (ironically the one i was using for testing). I was using that as it has some of it's services always in an unknown state at present so a known constant. The other services i have now tested on seem fine. I'll keep looking into it but am happy to say your solution worked.

Once again thanks for your help. Greatly appreciated. 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

If I understand correctly, you want to display the latest of either state, so you will need to get the latest of each state and the latest time of each of those and then work out which is valid

index=abc device=dev service=* 
| stats latest(alert_state) as alert_state latest_time(alert_state) as latest_alert_state latest(svc_state) as svc_state latest_time(svc_state) as latest_svc_state by service
| eval current_state=case(
  isnull(alert_state) AND isnull(svc_state), "Unknown",
  isnull(alert_state), svc_state,
  isnull(svc_state), alert_state,
  latest_alert_state>latest_svc_state, alert_state, 
  1==1, svc_state)
| where current_state="CRITICAL" OR current_state="WARNING" OR current_state="Unknown"
| table service current_state

The stats command will collect the latest data, then the eval statement looks at the possible 5 options of different possible states.

Note a couple of issues you have in your original query

a) where command cannot work with multi-value fields when using = for comparison, so you need  to use mvfind()

b) where command requires an eval statement syntax, so where current_state=CRITICAL is comparing two fields, current_state and CRITICAL, not the string "CRITICAL" which must be quoted.

Hope this helps

 

0 Karma

ajacobi
Path Finder

Thanks very much for your help.

That helps a lot but i have noticed that it only gives me results if I query over a short period like the last 30 minutes. If I query for a 24 hour period or for "today" then it gives me no results. I can't see any reason why the timeframe should impact the result based on using latest time. As the service state is only collected once a day this potentially means it is ignored with the shorter time queries. Am I missing something here? 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

@ajacobi 

Interesting. Can you run a search that goes back to just AFTER the service state is collected, so it's not present and see if the results are returned, then run the same search to just BEFORE it's collected, so it IS present and see the result.

It may be there's a logic problem in the SPL with checking for service state, but it should be possible to figure that out if you can prove either state with those two searches.

 

0 Karma
Get Updates on the Splunk Community!

Set Up More Secure Configurations in Splunk Enterprise With Config Assist

This blog post is part 3 of 4 of a series on Splunk Assist. Click the links below to see the other ...

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...

Enterprise Security Content Update (ESCU) v3.54.0

The Splunk Threat Research Team (STRT) recently released Enterprise Security Content Update (ESCU) v3.54.0 and ...