Video Screencast Help

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

Created: 08 Jul 2013 • Updated: 11 Jul 2013 | 6 comments
m.karampasis's picture
This issue has been solved. See solution.

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.

Operating Systems:

Comments 6 CommentsJump to latest comment

MilesVScott's picture

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.

If my answer was helpful, please mark as a solution so others can benefit as well.

NBU 7.1.0.3,Win 2k3 Enterprise x64 SP2

Miles Scott | Holland & Knight
IT Infrastructure Specialist

MilesVScott's picture

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

If my answer was helpful, please mark as a solution so others can benefit as well.

NBU 7.1.0.3,Win 2k3 Enterprise x64 SP2

Miles Scott | Holland & Knight
IT Infrastructure Specialist

SOLUTION
m.karampasis's picture

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 picture

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 picture

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.

If my answer was helpful, please mark as a solution so others can benefit as well.

NBU 7.1.0.3,Win 2k3 Enterprise x64 SP2

Miles Scott | Holland & Knight
IT Infrastructure Specialist

m.karampasis's picture

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