Dashboards & Visualizations

How to create a multi-series line chart based on date, hosts, and an eval?

bsg273
Path Finder

I'm trying to create a multi-series line chart in a Splunk dashboard that shows the availability percentages of a given service across multiple individual days for a set of hosts.  In other words, date is my x-axis, availability is my y-axis, and my legend contains the various hosts.  Since a picture is worth a thousand words, here's an example of what I'm trying to create:

example_chart.jpg

 

And here is my attempt at the query that's currently not working (it's using the rhnsd service as an example and assumes ps.sh is being run every 1800 seconds so it calculates availability based off ps.sh running 86400/1800=48 times per day):

 

 

index=os host="my-db-*" sourcetype=ps rhnsd | timechart span=1d count by host | eval availability=if(count>=86400/1800,100,count/floor(86400/1800)*100) | rename _time as Date host as Host availability as Availability | fieldformat Date = strftime(Date, "%m/%d/%Y") | chart first(Availability) over Host by Date

 

 

Any assistance with getting this query working so I can visualize it in a multi-series line chart in a dashboard panel would be greatly appreciated.

Labels (3)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
index=os host="my-db-*" sourcetype=ps rhnsd 
| timechart span=1d count by host 
| untable _time host count
| eval availability=if(count>=86400/1800,100,count/floor(86400/1800)*100) 
| rename _time as Date host as Host availability as Availability 
| fieldformat Date = strftime(Date, "%m/%d/%Y") 
| xyseries Date Host Availability

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust
index=os host="my-db-*" sourcetype=ps rhnsd 
| timechart span=1d count by host 
| untable _time host count
| eval availability=if(count>=86400/1800,100,count/floor(86400/1800)*100) 
| rename _time as Date host as Host availability as Availability 
| fieldformat Date = strftime(Date, "%m/%d/%Y") 
| xyseries Date Host Availability

bsg273
Path Finder

Perfect!  Thanks!  Is the foreach approach @PickleRick mentioned any less complex?  Just curious for my own educational purposes.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

The foreach solution is a bit more straightforward in terms of understanding the idea behind it, but - in my opinion - less "splunky" 😉

index=os host="my-db-*" sourcetype=ps rhnsd 
| timechart span=1d count by host
| foreach *
   [ | eval <<FIELD>>=if(<<FIELD>> > 86400/1800,100,<<FIELD>>/floor(86400/1800)*100) ]
| rename _time as Date
| fieldformat Date = strftime(Date, "%m/%d/%Y")

bsg273
Path Finder

Ha, we wouldn't want that!  But like you said, definitely easier to understand.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Yep, that's one of the solutions I was talking about 🙂

0 Karma

PickleRick
SplunkTrust
SplunkTrust

When you do timechart, you're creating separate data series not called "count" but named after values of your "by" field. You can either do the eval calculations using foreach or you can untable, calculate your evals and xyseries the data back. Both solutions aren't very pretty, you might want to check for yourself which is better performance-wise.

And I suppose you might lose the last chart command.

In general - don't troubleshoot all pipeline at once, just add one command at a time and see where it leads you.

Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...