cancel
Showing results for 
Search instead for 
Did you mean: 

OPS Analytics Custom Report for Drives at Down State (SQL QUERY)

m_karampasis
Level 4
Partner Accredited

Hello all,

We would like to create a custom OPS report in a weekly basis with all the drives that were in a down state during that week. 

Thank you in regards.

1 ACCEPTED SOLUTION

Accepted Solutions

MilesVScott
Level 6
Certified

Try this:

 

SELECT dda.DriveName, utcBigIntToNomTime(a.creationTime) AS 'Time', las.name AS 'Assigned Status', a.assignedTo AS 'Assigned To'
FROM am_Alert a
INNER JOIN am_AlertPolicy ap
ON a.alertPolicyId = ap.id
INNER JOIN am_DriveDownAlert dda
ON a.id = dda.alertId
INNER JOIN lookup_AlertAssignmentState las
ON a.assignmentState = las.id
WHERE ap.name = 'Drive Down' AND utcBigIntToNomTime(a.creationTime) > GETDATE() - 7

View solution in original post

6 REPLIES 6

MilesVScott
Level 6
Certified

Do you have alerts set up for the condition you want to report on? I don't think OpsCenter keeps track of drive status history like that.

MilesVScott
Level 6
Certified

Try this:

 

SELECT dda.DriveName, utcBigIntToNomTime(a.creationTime) AS 'Time', las.name AS 'Assigned Status', a.assignedTo AS 'Assigned To'
FROM am_Alert a
INNER JOIN am_AlertPolicy ap
ON a.alertPolicyId = ap.id
INNER JOIN am_DriveDownAlert dda
ON a.id = dda.alertId
INNER JOIN lookup_AlertAssignmentState las
ON a.assignmentState = las.id
WHERE ap.name = 'Drive Down' AND utcBigIntToNomTime(a.creationTime) > GETDATE() - 7

m_karampasis
Level 4
Partner Accredited

Dear MIles,

Thank you very much for your response, when we try to run the your sql query we get the following error:

"The report did not return any data"

Any ideas?

m_karampasis
Level 4
Partner Accredited

Hello Miles,

 

I have tried your query to another environment and also we get the "The report did not return any data".

Although we have drives in down state.

I think somethink is wrong with the WHERE ap.name = 'Drive Down' .

 

Thank in regards.

MilesVScott
Level 6
Certified

Sorry about the delay in my responce. You will need to have the alerts enabled for this query to work. It pulls the history of any drive down alerts. As far as the 'Drive Down' You will need to change that text to whatever the name of your Downed Drive alert is. Sorry for the lack of clarification.

m_karampasis
Level 4
Partner Accredited

For one more time Miles is right!!!!!We had to change the 'Drive Down' to 'Drive is Down'  Thank you very much for your script and your help!!!!!!!