We are using the SLA business hour report shown online and below but need a way to only have it show the previous day and for the life of me I cannot figure out how to make it work successfully. Below is the swql code that I'm working with which obviously works great, but we don't want to see any day except the previous day. This is a report that we run every morning at 1am to report on the previous day system availability and I'm stuck on how to use the code below so that the report only contains the previous day and not the entire month.
SELECT n.Caption, sub_query.sla_day, sub_query.sla_availability, n.CustomProperties.time_from, n.CustomProperties.time_to FROM
(
SELECT avg(Availability) AS sla_availability, datetrunc('day',datetime) AS sla_day, r.NodeID
FROM Orion.ResponseTime r
WHERE hour(datetime) >= r.Node.customproperties.time_from
AND hour(datetime)<= r.Node.customproperties.time_to
GROUP BY datetrunc('day',datetime), r.NodeID
) AS sub_query
INNER JOIN Orion.Nodes n
ON n.NodeID=sub_query.NodeID
I am new to swql, love it and use it often but my knowledge of proper syntax and usage needs more experience. Any help would be greatly appreciate it.
Robert
I found a solution that works.
AND DayDiff(datetime, GetUtcDate()) < 1
This document was generated from the following discussion: Help with business hours sla report to only show previous day.
