Unified reports SLO/SLA Report question

[Anon Anon]

I would like to create a report based on SLO''s per customers to run on the weekly and monthly.  I am not sure what the sql query would be to get the name of the device plus the percentage of uptime for network connectivity for that device.  If someone can help with the query I can creat the reports to run on a scheduled basis for each customer using parameters such as the account_id.

 

I just think the unified reports are much cleaner and would rather just the customer use these exclusivly.

[Anon Anon]

Here is the query I figured out to create a report in Unified reports for individual SLO metrics.  I am looking at the QOS_Constraints source field for now unless I can make it a account aware report.

 

SELECT     S_QOS_CONSTRAINTS.qos_const_id,  S_QOS_CONSTRAINTS.source, S_QOS_CONSTRAINTS.target,                       D_QOS_COMPLIANCE.percentage, D_QOS_COMPLIANCE.total, D_QOS_COMPLIANCE.ok,'100.00' as MAX
FROM         D_QOS_COMPLIANCE INNER JOIN
                      S_QOS_CONSTRAINTS ON D_QOS_COMPLIANCE.qos_const_id = S_QOS_CONSTRAINTS.qos_const_id
where S_QOS_CONSTRAINTS.source like 'msn-%'
order by percentage

[Legacy User]

This may not be the best formatted SQL statement, as I am no SQL expert, but we use variations of this SQL to do availability percentages of QOS metrics.

 

SELECT target, (CAST(ROUND((AVG((CASE WHEN RN_QOS_DATA_0094.samplevalue IS NULL THEN CAST(0 AS Decimal(15,3)) ELSE
CAST(1 AS Decimal(15,3)) END)) * 100),3,0) AS Decimal(15,3))) AS Availability
FROM S_QOS_DATA, RN_QOS_DATA_0094
WHERE qos='QOS_URL_RESPONSE'
AND RN_QOS_DATA_0094.table_id = S_QOS_DATA.table_id
AND (getdate()-RN_QOS_DATA_0094.sampletime) < 30
AND target LIKE '%Amway.%'
GROUP BY target
ORDER BY Availability DESC

 

So basically what this does is return two things, either 1 or 0.  If it didn't record a response time it sends "0" if it does it sends "1".  We then calculate avaialbility based on those returns.  Depending on the table ID's and QOS you are going after some of the details may need to change.  I've used this for Oracle Database Uptime, SQL Uptime, etc.  Seems to work rather well.

 

I'm adding what we did for Oracle and SQL as well, as the query is slightly different:

 

============= Oracle Availability

SELECT DISTINCT target, CONVERT(VARCHAR(20),CAST(ROUND((AVG(ISNULL(RN_QOS_DATA_0042.samplevalue,0)) * 100),3,0) AS Decimal(15,3))) + '%' AS AverageAvailability
FROM S_QOS_DATA, RN_QOS_DATA_0042
WHERE qos='QOS_ORACLE_CHECK_DBALIVE'
AND RN_QOS_DATA_0042.table_id = S_QOS_DATA.table_id
AND (getdate()-RN_QOS_DATA_0042.sampletime) < 30
GROUP BY target
ORDER BY AverageAvailability DESC


============= SQL Server Availability

SELECT DISTINCT target, CONVERT(VARCHAR(20),CAST(ROUND((AVG(ISNULL(RN_QOS_DATA_0068.samplevalue,0)) * 100),3,0) AS Decimal(15,3))) + '%' AS AverageAvailability
FROM S_QOS_DATA, RN_QOS_DATA_0068
WHERE qos='QOS_SQLSERVER_CHECK_DBALIVE'
AND RN_QOS_DATA_0068.table_id = S_QOS_DATA.table_id
AND (getdate()-RN_QOS_DATA_0068.sampletime) < 30
GROUP BY target
ORDER BY AverageAvailability DESC

 

Hope that this helps some.

 

Thanks,

Dave

[Anon Anon]

That is a good query as well but in our case we wanted just info that we have in a sla report and our sla also looks at the repsponse time making sure it's less than a value we specify and not just up/down. Also, some devices we may poll and collect qos data but it may not be part of a sla.  It's excellent getting all the different methods to do this though and this could be very usefull when we report all devices for a sla and are not worried about the response time then I may not even create a sla.

Or course I think I could tweek the query a little more to look for anything less than a certain value as well.

[Legacy User]

Yes, we weren't so much concerned with the response time for the reports we were generating, strictly a "are you alive" type of check.  But depending on what you need these could me moified to fit. I haven't played with the SLA tables much but I'll take a look at those when I get some time, see if anything jumps out at me.

[Anon Anon]

Yes I already have it working looking a response time.  Just added OR
                      RN_QOS_DATA_0001.samplevalue > 2000 to the query I also wanted to to it by the month so on the first of the month the customer gets last months report.

 

(MONTH(RN_QOS_DATA_0001.sampletime ) = MONTH(GETDATE() - DAY(GETDATE()))) AND (YEAR(RN_QOS_DATA_0001.sampletime ) = YEAR(GETDATE() - DAY(GETDATE())))

[Anon Anon]

Hi Neil,

 

to make the reports account aware (render data and take into account the user who is viewing them), is a bit more difficult than we had hoped. There is a guide that talks about what kind of queries you need to use to achieve that. that will enable to you make account aware reports which can be integrated with UR and display data based on whos viewing them.

 

But there is a known limitation at the moment. There is a bug in the scheduling mechanism which makes scheduling reports fail if they are run as contact users.

 

I have tried to create a report, and in theory, it will be executed in the security context of the user who requests it. But I had problems and run into some errors. It has been reported, but no ETA when it will be fixed.

 

So the only other way for know is to run them as your admin user and send in an account id or something as a parameter.

 

Just a heads up if you start trying to schedule account aware reports.

 

Geir.

 

[Anon Anon]

Thanks for the heads up, I just ended up passing some paramenters mainly the origin and scheduling that way.  Now the problem is since I output them as pdf's to a folder that each customer has there own access to they do not show in the UMP Unified reports portlet.  They do show if you just log into unified reports,  looks like to me they are only looking for actual reports inside the portlet.  A pdf file shows as a content resource.