Forum Discussion

m_karampasis's avatar
m_karampasis
Level 4
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.

Backing Up
Backup and Recovery
OpsCenter
Performance
Reporting
Tip-How to
Windows Server (2003-2008)
  • MilesVScott's avatar
    MilesVScott
    13 years ago

    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

  • MilesVScott's avatar
    MilesVScott
    Level 6
    13 years ago

    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's avatar
    MilesVScott
    Level 6
    13 years ago

    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's avatar
    m_karampasis
    Level 4
    13 years ago

    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's avatar
    m_karampasis
    Level 4
    13 years ago

    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's avatar
    MilesVScott
    Level 6
    13 years ago

    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's avatar
    m_karampasis
    Level 4
    13 years ago

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