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

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.

  • What is your detailed data retention setting?  The first think that I would expect to skew your numbers is the weekly roll up, since it doesn't look like you are doing any kind of weighting in your query to reflect the number of polls in each hourly/daily time slice.

  • mesverrum Are you referring to "Detailed Statistics Retention"? it's currently 15 days...

    Is that the retention setting for availability? i thought the Daily Statistics Retention held node availability.

  • All NPM statistics are subject to the same rollups, other modules sometimes have their own retention settings but they basically all fall under the same model

    Node response time, loss, cpu, memory are all rolled up using the following process.

    details - raw data, weight is 1, min/max/avg will all be the same value

    hourly - take the raw values that have aged past the detailed retention period and average those raw values into hourly blocks, weighted by however many polls were taken for that object in that hour, min/max/avg will reflect the data that went into this 1 hour collection.

    daily - take the hourly averages that have aged out of the hourly retention period and average those already averaged values into 24 hour blocks, weighted by the summed weight of all the hours that formed the block, min/max is actually the min/max of the previous set of hourly averages, this smooths out actual resource load peaks and kind of makes the min/max data useless for most purposes, average is calculated properly so at least that can be relied on.

    If you are reporting on data that crosses these roll up periods I find it is extremely important to understand exactly how SW is managing the numbers when they roll ups happen.  I have long been an advocate for writing all my raw data points out to a separate data warehouse if the fine grained detail is considered really important for your org.

  • that makes sense. Where are the raw data points located in the database?

  • You would use the same responsetime view are you are looking at, IIRC there is a column called archive i believe will be 0 for raw data, then it increments up as they go through the various phases of rollup.