Help optimising a query

[kwestlake]

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

[to4kawa · Accepted solution]

| 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]

Thank you - Especially for the explanation on the Trellis.