Seeking help with SWQL query for a report.

jdambrose

I am very new to SQL and SWQL and am hoping someone can help me with a query I'm building for a customer report.

I started by using an application availability report for the last 30 days.

I only want to report on 6 URL/HTTPS monitors I have setup for the customer and need to filter the result by using ApplicationID.

I added the text in quotes after WHERE:  "ApplicationID In (4939,4932,4933,4938,4940,4941) AND"

The query works, but finds 0 results.  This is the full query:

SELECT TOP 50
    ADDMONTH(MONTHDIFF(0, GETDATE()) - 1, 0) AS Month,
    Nodes.Caption,
        Nodes.DetailsUrl AS NodesDetailsUrl,
    Application.Name AS ApplicationName,
        Application.DetailsUrl AS ApplicationDetailsUrl,
    SUM(ApplicationStatus.PercentAvailability * ApplicationStatus.RecordCount) / SUM(ApplicationStatus.RecordCount) AS PercentAvailability
FROM Orion.APM.ApplicationStatus ApplicationStatus
INNER JOIN Orion.APM.Application Application ON Application.ApplicationID = ApplicationStatus.ApplicationID
INNER JOIN Orion.Nodes ON Nodes.NodeID = Application.NodeID
WHERE  ApplicationID In (4939,4932,4933,4938,4940,4941) AND ApplicationStatus.TimeStamp >= ADDMINUTE(MINUTEDIFF(GETDATE(), GETUTCDATE()), ADDMONTH(MONTHDIFF(0, GETDATE()) - 1, 0)) AND
    ApplicationStatus.TimeStamp < ADDMINUTE(MINUTEDIFF(GETDATE(), GETUTCDATE()), ADDMONTH(MONTHDIFF(0, GETDATE()), 0))
GROUP BY Application.ApplicationID, Application.Name, Nodes.Caption, Nodes.DetailsUrl, Application.DetailsUrl
ORDER BY Nodes.Caption, Application.Name

If I remove "AND ApplicationStatus.TimeStamp < ADDMINUTE(MINUTEDIFF(GETDATE(), GETUTCDATE()), ADDMONTH(MONTHDIFF(0, GETDATE()), 0))" it finds matches, however, the availability % results are incorrect.

Any assistance the community can provide in modifying the query to get it to yield results will be greatly appreciated.

brscott

This query seems valid in SWQL Studio. 

One note, that might or might not be related.  Remove the "Top 50" from the actual query in the report.  If you want that feature then handle it in the "Edit Table" screen as "Show Only the top 50"

Off the rails side note:  Add a custom property to Application, something like "Customer", assign those application monitors the value to that custom property like "Customer = Mystery Inc.", then in your query instead of "ApplicationID In (...) AND" change it to "Application.CustomProperties.Customer = 'Mystery Inc.' AND".  Now you never have to edit the report again. (Disclaimer that is hand typed and will have typos),

respectfully, your custom property evangelist.