Hi All,
I'm trying to create KPI widgets based upon application availability.
The idea being, we can present them in a dashboard in EOC to keep track of the stat, akin to what we can see in the main dashboard, but focused on last month
I can pull the data using the following
SELECT
ROUND(SUM(a.PercentAvailability * a.RecordCount) * 1.0 / SUM(a.RecordCount), 2) AS Availability
FROM Orion.APM.ApplicationStatus AS a
JOIN Orion.APM.Application AS app ON a.ApplicationID = app.ApplicationID AND a.InstanceSiteId = app.InstanceSiteId
JOIN Orion.Nodes AS n ON app.NodeID = n.NodeID AND app.InstanceSiteId = n.InstanceSiteId
JOIN Orion.NodesCustomProperties AS ncp ON n.NodeID = ncp.NodeID
JOIN Orion.APM.ApplicationTemplate AS t ON app.ApplicationTemplateID = t.ApplicationTemplateID
WHERE
a.TimeStamp >= DATETRUNC('month', ADDMONTH(-1, GETUTCDATE()))
AND a.TimeStamp < DATETRUNC('month', GETUTCDATE())
AND a.RecordCount > 0
AND ncp.Product = 'Product Name'
AND ncp.Environment_Type = 'Production'
AND ncp.Device_Role = 'Public URL'
AND t.Name = 'HTTPS Monitor'
However, this times out. Is there a better table to query other than Orion.APM.ApplicationStatus or a better way to identify the aggregated data of last month?
SWQL doesn't appear to support sub queries which limits me here
