I have a query that calculates the daily availability percentages of a given service for a set of hosts and is used to create a multi-series line chart in a Splunk dashboard. My ps.sh is running every 1800 seconds (30 minutes) on my Splunk forwarders, so I assume that it has run a total of 48 times on any given day to calculate the availability in an eval. The problem is that on the current date, the ps.sh hasn't run all 48 times yet, so I can't get a valid calculation for the current date. However, if I was able to check if the date in question was the current date, then calculate the number of seconds that have elapsed since the nearest midnight, I could divide that figure by 1800 to figure out the total number of times ps.sh would've run so far that day (hopefully I'm not overcomplicating this). To illustrate, here's my query with the pseudo-code of desired logic in it using rhnsd as an example process:
index=os host="my-db-*" sourcetype=ps rhnsd
| timechart span=1d count by host
| untable _time host count
| addinfo
| eval availability=if(<date is current date>,count/floor((info_max_time-<nearest midnight time>)/1800)*100,if(count>=48,100,count/48*100))
| rename _time as Date host as Host availability as Availability
| fieldformat Date = strftime(Date, "%m/%d/%Y")
| xyseries Date Host Availability
Any help I could get with completing the above eval would be greatly appreciated, or if I'm overcomplicating this, any alternative methodologies would be more than welcome.
This is the solution I ended up going with. It simplifies my original logic a bit and also makes the graphs a bit easier to read by displaying hostname only instead of the fqdn.
index=os host="my-db-*" sourcetype=ps rhnsd
| timechart span=1d count by host
| untable _time host count
| addinfo
| eval psRunCount=if(strftime(_time,"%m/%d/%Y")=strftime(info_max_time,"%m/%d/%Y"),floor((info_max_time-relative_time(info_max_time,"@d"))/1800),floor(86400/1800)), availability=if(count>=psRunCount,100,round(count/psRunCount*100,2)), host=mvindex(split(host,"."),0)
| rename _time as Date host as Host availability as Availability
| fieldformat Date=strftime(Date,"%m/%d")
| xyseries Date Host Availability
This is the solution I ended up going with. It simplifies my original logic a bit and also makes the graphs a bit easier to read by displaying hostname only instead of the fqdn.
index=os host="my-db-*" sourcetype=ps rhnsd
| timechart span=1d count by host
| untable _time host count
| addinfo
| eval psRunCount=if(strftime(_time,"%m/%d/%Y")=strftime(info_max_time,"%m/%d/%Y"),floor((info_max_time-relative_time(info_max_time,"@d"))/1800),floor(86400/1800)), availability=if(count>=psRunCount,100,round(count/psRunCount*100,2)), host=mvindex(split(host,"."),0)
| rename _time as Date host as Host availability as Availability
| fieldformat Date=strftime(Date,"%m/%d")
| xyseries Date Host Availability
Give this a try
index=os host="my-db-*" sourcetype=ps rhnsd
| timechart span=1d count by host
| untable _time host count
| addinfo
| eval availability=if(strftime(_time,"%F")=strftime(now(),"%F"),count/floor(now()-relative_time(now(),"@d"))/1800)*100,if(count>=48,100,count/48*100))
| rename _time as Date host as Host availability as Availability
| fieldformat Date = strftime(Date, "%m/%d/%Y")
| xyseries Date Host Availability
It's telling me there's an error in the eval command. I'll post the solution I ended up going with.
To find the lapsed time since midnight, substract midnight ("@d") from the event time (_time).
| eval secsSinceMidnight = _time - relative_time(_time, "@d")