Help me tweak below SWQL to have the Sum of Availability of whole month

jerenelle

Hi, 

I have below SWQL that get Availability percentage of Application for some nodes.

But what it display is the per polling timestamp on each month which causes memory to exceed due to data is so big.

What I need is the sum up of all the availability of each application on each month.

The default range is last 6 months data.

SELECT
ApplicationStatus.TimeStamp AS Month,
SUBSTRING(Nodes.Caption,1,7) As 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 ApplicationStatus.TimeStamp >= ADDMINUTE(MINUTEDIFF(GETDATE(), GETUTCDATE()), ADDMONTH(MONTHDIFF(0, GETDATE()) - 6, 0)) and ApplicationName like 'ISS%'
and SUBSTRING(Caption,1,7) in ('serve1','server2','server3')
GROUP BY ApplicationStatus.TimeStamp,Application.ApplicationID, Application.Name, Nodes.Caption, Nodes.DetailsUrl, Application.DetailsUrl
ORDER BY Nodes.Caption, Application.Name

mesverrum

Looking at what you are doing there are substrings in the where condition in a way that is pretty suboptimal for db performance, https://www.sqlshack.com/sql-substring-function-and-its-performance-tips/ so you might try experimenting with other syntaxes in SSMS to see if your can get better performance.  I don't know off the top of my head by i could imagine that instead of doing the substring in the where you try 

where (caption like 'server1%' or caption like server2, etc) 

Once you get your query as optimized as it can be if you still have problems you have to look into more exotic solutions.  The default data retention for SAM is only 90 days, and if you crank it up then those tables tend to become absolutely massive which makes queries like this nearly impossible to complete inside the timeout period.  

The db structure of Orion is designed for being snappy with near real-time and short term data.  It's clearly not a data warehouse and it isn't designed to do long term retentions of super high volumes of data. To really do good reporting on anything further out than 30 days I find you really want to start looking at setting up some kind of tables outside of the core ones that Orion itself uses.  You can batch load historical data into there, index it and aggregate it in ways that would be terrible for the constant stream of inserts that we get on the real time stuff, but make reports like you are asking for really fast and easy.  This is a simple example talking about ways one might get started down that road. https://www.bobpusateri.com/archive/2011/03/a-poor-mans-data-warehouse/

It's just really hard to make a relational SQL database design that is going to hold billions of records but also let the page load in 1 second, so you split the old stuff out from the real time stuff and save yourself the pain.