topic Timeseries dt.availability.state split by hours - business non business in DQL

Timeseries dt.availability.state split by hours - business non business

AlanFoley — Wed, 17 Jul 2024 06:37:06 GMT

Hi All

I am successfully able to calculate an overall monthly Availability % which aligns to the value show in Host Classic for availability for a given host

Here is the DQL:

timeseries {AvailabilityStateUP=sum(dt.host.availability),end()}, filter:availability.state == "up"
, by: {dt.entity.host,availability.state},interval:1h
| expand `end()`
| filter dt.entity.host == "HOST-xxx"
| fieldsAdd hour = formatTimestamp(`end()`, format:"HH")
| sort `end()`

| ... rest of DQL statements for Availability Percentage Calculation and output fields

I would like to be able to report on Monthly availability percentage split by Business (06h00-18h00) and Non Business Hours (18h00-06h00) - ideally split at the "timeseries" level

In the screenshot below you can see that "AvailabilityStateUP" contains the array for the WHOLE reporting interval - in this case the last 24 hours - not split by "end()" - I can filter by "hour" after the timeries extraction but it does not help as the array contains all values for the whole reporting period

The problem is the availability figure for each hour will be the availability % calculation of the WHOLE array for the reporting period

I thought I could split by: end() but this is also not possible

I also looked at "bins:" but the documentation did not help me - is this an option? can someone assit with the syntax as per my requirement

Any other ides? Apprecaite any assistance

Thanks

Alan

Re: Timeseries dt.availability.state split by hours - business non business

krzysztof_hoja — Wed, 17 Jul 2024 10:31:53 GMT

You do not need to expand. You can use iterative expressions:

timeseries {AvailabilityStateUP=sum(dt.host.availability),e=end()}, filter:availability.state == "up" and dt.entity.host == "HOST-000CA78B479207CD" , by: {dt.entity.host,availability.state},interval:1h | fieldsAdd AvailabilityStateUP_BH = if( getHour(e[])>=8 and getHour(e[])<=17, AvailabilityStateUP[] ) | fieldsAdd AvailabilityStateUP_NBH = if( getHour(e[])<8 or getHour(e[])>17, AvailabilityStateUP[] )

this way you get 2 timeseries, each one filled only values in our outside business hours. Then you can proceed with summarization of the array using array functions.

It is possible with expand too, but you need to construct single array of records containing both: availability metric and time:

timeseries {AvailabilityStateUP=sum(dt.host.availability),e=end()}, filter:availability.state == "up" and dt.entity.host == "HOST-000CA78B479207CD" , by: {dt.entity.host,availability.state},interval:1h | fieldsAdd AvailabilityStateUPWithH = record(av=AvailabilityStateUP[], h=getHour(e[])) | expand AvailabilityStateUPWithH | fieldsFlatten AvailabilityStateUPWithH

Re: Timeseries dt.availability.state split by hours - business non business

AlanFoley — Wed, 17 Jul 2024 13:07:27 GMT

Hi krzysztof_hoja

The iterative expressions solution is perfect - works like a charm

Really appreciate you taking the time to test and provide a solution

Thanks a mil

Regards

Alan

Re: Timeseries dt.availability.state split by hours - business non business

PedroDeodato — Thu, 23 Oct 2025 16:53:43 GMT

Have been struggling with this for a while now!!
Thank you so much!