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

[bsg273]

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:

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.

[ITWhisperer · Accepted solution]

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]

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

[PickleRick]

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]

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

[PickleRick]

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

[PickleRick]

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.