SQL Query on behalf of SCOM 2007

 Hi There,



I would like to have a SQL query in SCOM 2007.

Which shows:

Managed computer, Alert description and Alert severity.



Any idea how to make one anyone?



regards,

 I think the same SQL query could be done in a PowerShell one liner I'll post that later on my blog. But here is the SQL Script that you requested. I used MonitoredObjectDisplayname instead of Managed computer.

-- Scott Moss
-- 10/27/2008
-- SCOM 2007 SP1 OPerationsManager DB Query
-- Get listing of MonitoringObject, Alert descriptiong and Severity
-- http://myitforum.com/cs2/blogs/smoss

select
MonitoringObjectDisplayName,
AlertStringName,
AlertStringDescription,
'Severity' = Case
When AlertView.Severity = 0 Then 'INFORMATIONAL'
When AlertView.Severity = 1 Then 'WARNING'
When AlertView.Severity = 2 Then 'CRITICAL'
Else 'Undetermined'
End
From AlertView

 powershell one liner to get the same information:

get-alert  | select-object NetbiosComputerName, Description, Severity | Export-Csv -path “c:\alerts.csv“

 Thanks! That looks sweet!

However, I discovered that the query I requested doesn't provide me the correct result. What I'm really after is a query that gives me an overview off all alerts with status new, created today with corresponding managed devices. From another forum I got another query which I 've rebuilt a little and I'd like to extend with today's date.

SELECT     TOP (100) PERCENT vAlert.DBCreatedDateTime, vAlert.RaisedDateTime, vEntity.DisplayName, vAlert.AlertName, vAlert.AlertDescription,
Alert.vAlertResolutionState.AlertGuid, Alert.vAlertResolutionState.ResolutionState
FROM         Alert.vAlert AS vAlert INNER JOIN
dbo.vManagedEntity AS vEntity ON vAlert.ManagedEntityRowId = vEntity.ManagedEntityRowId INNER JOIN
Alert.vAlertResolutionState ON vAlert.AlertGuid = Alert.vAlertResolutionState.AlertGuid
WHERE     (Alert.vAlertResolutionState.ResolutionState = 0)
ORDER BY vAlert.RaisedDateTime DESC

I would like to extend this one with today's all new alerts.

So I add something like

AND RaisedDateTime = GetDate()

But this produces a blank result.

Is there, probably very simple, a way to achieve this?