topic Re: How to create a multi-series line chart based on date, hosts, and an eval? in Dashboards & Visualizations

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

bsg273 — Mon, 28 Mar 2022 21:09:53 GMT

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.

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

PickleRick — Mon, 28 Mar 2022 21:26:12 GMT

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.

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

ITWhisperer — Tue, 29 Mar 2022 07:20:51 GMT

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

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

PickleRick — Tue, 29 Mar 2022 07:25:27 GMT

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

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

bsg273 — Tue, 29 Mar 2022 14:57:47 GMT

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

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

PickleRick — Tue, 29 Mar 2022 15:07:47 GMT

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")

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

bsg273 — Tue, 29 Mar 2022 15:21:47 GMT

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