This report pulls statistics for the Microsoft IIS Application and only returns information for M-F 8 AM - 5 PM (Orion Server Time).
This is my first attempt for something like this, so feedback is welcome and encouraged!
Looks good! Thanks for posting. This will be very useful.
Hey ">KMSigma
I can't seem to download the above? I dont supose you could link the SQL? It might prove very useful to me.
TIA
Here is the SQL if you still need it:
SELECT MAX([dbo].[ConvertUTCToLocalTime](ast.TimeStamp)) AS Month ,
n.Caption ,
a.Name AS ApplicationName ,
SUM(ast.PercentAvailability * ast.RecordCount) / SUM(ast.RecordCount) AS PercentAvailability
FROM APM_ApplicationStatus ast WITH ( NOLOCK )
INNER JOIN APM_Application a WITH ( NOLOCK ) ON a.ID = ast.ApplicationID
INNER JOIN Nodes n WITH ( NOLOCK ) ON n.NodeID = a.NodeID
WHERE [dbo].[ConvertUTCToLocalTime](ast.TimeStamp) >= DATEADD(MINUTE,
DATEDIFF(MINUTE,
GETDATE(),
GETUTCDATE()),
DATEADD(MONTH,
DATEDIFF(MONTH,
0, GETDATE()), 0))
AND DATEPART(hh, [dbo].[ConvertUTCToLocalTime](ast.[TimeStamp])) BETWEEN 8 AND 17 -- Between 8 AM and 5 PM
AND DATEPART(weekday,[dbo].[ConvertUTCToLocalTime](ast.[TimeStamp])) BETWEEN 2 AND 6 -- Weekdays Only
AND a.Name = 'Microsoft IIS' -- Application Name
GROUP BY a.ID ,
a.Name ,
n.Caption
ORDER BY n.Caption ,
a.Name;
