This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Availability Query is Dropping data

rmullal over 4 years ago

I have this SQL query that gives me the average availability of the previous month for the firewalls at my sites:

DECLARE @startOfCurrentMonth DATETIME
SET @startOfCurrentMonth = DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
SELECT
sub.SummaryMonth AS Month_Of,
AVG (sub.AVERAGE_of_Availability) as Total_Average
FROM
(
SELECT Nodes.VendorIcon AS Vendor_Icon,
Nodes.Caption AS NodeName,
Nodes.MachineType AS Machine_Type,
AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
CONVERT(DateTime,
LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
101) AS SummaryMonth
FROM
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)
WHERE
( datetime >= DATEADD(month, -1, @startOfCurrentMonth) AND datetime < @startOfCurrentMonth )
AND 
(
  (Nodes.Caption LIKE '%-FW')
)
GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.VendorIcon, Nodes.Caption, Nodes.MachineType
) sub
GROUP BY sub.SummaryMonth

However, the percentage that is returned keeps changing. I figured this was because there is some Availability retention setting that is less than 30 days or something. i've done some research, and it seems the Daily Statistics Retention setting is used for availability retention. I have mine set for 90 days, but the percentage was still changing.

I then played with the query a little bit and came up with this to see what data was getting pulled in:

DECLARE @startOfCurrentMonth DATETIME
SET @startOfCurrentMonth = DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
SELECT Nodes.VendorIcon AS Vendor_Icon,
Nodes.Caption AS NodeName,
Nodes.MachineType AS Machine_Type,


--find where responsetime.availability is pulling
ResponseTime.Availability AS AVERAGE_of_Availability,
CONVERT(DateTime,
LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
101) AS SummaryMonth,
DateTime, ResponseTime.NodeID
FROM
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)
WHERE
( datetime >= DATEADD(month, -1, @startOfCurrentMonth) AND datetime < @startOfCurrentMonth )
AND 
(
  (Nodes.Caption LIKE '%-FW')
)
GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.VendorIcon, Nodes.Caption, Nodes.MachineType, ResponseTime.Availability, DateTime, ResponseTime.NodeID
Order BY DateTime

It looks like it is just appending the data again? i figure this is because it is a view maybe?

So, my question is what am i doing wrong here? where is ResponseTime.Availability getting pulled from for that view?

I'm just trying to pull in the average availability for devices that end in '-FW'.

Thank you all in advance.