Forum Discussion

m_karampasis's avatar
13 years ago
Solved

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

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.

  • 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

6 Replies

  • 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.

  • 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
  • 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?

  • 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.

  • 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.

  • 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!!!!!!!