Splunk Search

Help optimising a query

kwestlake
Engager

Hi All

I'm fairly new to Splunk, and still very much learning (its a small hobby), and I recently found Elastic Beats works great for monitoring my home servers and network, then to add a cherry, the output is easily imported into Splunk. I've got it running some ICMP PINGs against my network interfaces, and in its simplest form, generates data such as:

NAME   STATUS  PING 
site1  up      10
site2  up      10
site2  down    0
site3  up      10
site1  down    10

I'm using the Status Indicator visualisation to show a Trellis view of Total Devices, Total Up, Total Down. I've hacked together the following query, which works, but there has got to be a more optimised way of doing this:

index="beats" "monitor.type"=icmp "tags{}"=external 
| stats latest(monitor.name) as name 
        latest(monitor.status) as status 
        BY monitor.name

| stats count(name) as "   TOTAL"
        sum(eval(if(status=="up",1,0))) as "  UP"
        sum(eval(if(status=="down",1,0))) as    " DOWN"

| eval fn = "value"
| transpose column_name="category" header_field=fn

| eval color = if(category=="   TOTAL", "#006d9c", if(category=="  UP", "#00AA00", "#dc4e41"))
| eval icon = if(category=="   TOTAL", "server", if(category=="  UP", "check", "times-circle"))
| sort category
| stats last(value) as value  last(icon) as icon last(color) as color by category

Which effective does the following:

  1. Pull back the latest record for each unique "monitor.name"
  2. Then counts the Total, Total Up, Total Down (Note spaces in the as-names, a hack so I can sort them into a desired order (Total -> UP -> Down)
  3. The above totals are columns, so I transpose to a list with "category" (aka name) and "value"
  4. Use EVAL to set the colour and icons for the visualisation
  5. Sort into the order I want (Total -> UP -> Down)
  6. Then re-apply stats - I honestly don't know why this is needed, but without it, the Status Indicator visualisation doesn't work, though looking at the statistics tab, the results are exactly the same without it

I'd really appreciate any advise on how I could re-work this, improve it, also any insight into why step 6 appears to be needed.

Many Thanks
K

0 Karma
1 Solution

to4kawa
Ultra Champion
| makeresults count=2 
| streamstats count 
| eval _time=relative_time(_time,-1*count."d@d") 
| makecontinuous _time span=45min 
| eval count=random() % 6, monitor.name=mvindex(split("A,B,C,D,E,F",","),count), monitor.status=mvindex(split("up,down",","),(count % 2)) 
| rename COMMENT as "this is sample data. check this. from here, the logic"
| stats latest(monitor.status) as status BY monitor.name 
| stats count as "TOTAL" count(eval(status=="up")) as "UP" count(eval(status=="down")) as "DOWN" values(eval("value")) as fn 
| transpose header_field=fn column_name=category 
| eval color = case(category=="TOTAL", "#006d9c", category=="UP", "#00AA00",category=="DOWN", "#dc4e41") 
| eval icon = case(category=="TOTAL", "server", category=="UP", "check", category=="DOWN", "times-circle") 
| eval sorter = case(category=="TOTAL", 1, category=="UP", 2, category=="DOWN", 3) 
| stats last(value) as value last(icon) as icon last(color) as color last(sorter) as sorter by category
| sort sorter
| fields - sorter

Hi, your query is good. There are not many changes.

the Status Indicator visualisation to show a Trellis view
Trellis visualization needs aggregation.( stats , chart , timechart)

View solution in original post

to4kawa
Ultra Champion
| makeresults count=2 
| streamstats count 
| eval _time=relative_time(_time,-1*count."d@d") 
| makecontinuous _time span=45min 
| eval count=random() % 6, monitor.name=mvindex(split("A,B,C,D,E,F",","),count), monitor.status=mvindex(split("up,down",","),(count % 2)) 
| rename COMMENT as "this is sample data. check this. from here, the logic"
| stats latest(monitor.status) as status BY monitor.name 
| stats count as "TOTAL" count(eval(status=="up")) as "UP" count(eval(status=="down")) as "DOWN" values(eval("value")) as fn 
| transpose header_field=fn column_name=category 
| eval color = case(category=="TOTAL", "#006d9c", category=="UP", "#00AA00",category=="DOWN", "#dc4e41") 
| eval icon = case(category=="TOTAL", "server", category=="UP", "check", category=="DOWN", "times-circle") 
| eval sorter = case(category=="TOTAL", 1, category=="UP", 2, category=="DOWN", 3) 
| stats last(value) as value last(icon) as icon last(color) as color last(sorter) as sorter by category
| sort sorter
| fields - sorter

Hi, your query is good. There are not many changes.

the Status Indicator visualisation to show a Trellis view
Trellis visualization needs aggregation.( stats , chart , timechart)

kwestlake
Engager

Thank you - Especially for the explanation on the Trellis.

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...