SQL Query DWH - Show resolution state as name

Dashboard Server
, , , ,
1

Hi!

 

I created a query where I can see all alarms with the error message “Failed to connect to computer”:

SELECT a.AlertName, a.AlertDescription, a.RaisedDateTime, ap.ParameterValue, ar.ResolutionState, ar.StateSetDateTime, ar.StateSetByUserId
FROM Alert.vAlert AS a
INNER JOIN Alert.vAlertParameter AS ap ON a.AlertGuid = ap.AlertGuid
INNER JOIN Alert.vAlertResolutionState AS ar ON ar.AlertGuid = ap.AlertGuid
Where a.AlertName = 'Failed to connect to Computer' AND ar.ResolutionState = 255 AND ar.ResolutionState != 0
Order BY ar.StateSetDateTime DESC

Now I want to show it in SquaredUp.

The only Problem I have is that the ResolutionState is shown as “255”. Is there a way to show it as “closed” (text)?

 

Thanks!

2 
SELECT TOP 300 a.AlertName, a.AlertDescription, a.RaisedDateTime, ap.ParameterValue, res.ResolutionStateName, ar.StateSetDateTime, ar.StateSetByUserId
FROM Alert.vAlert AS a
INNER JOIN Alert.vAlertParameter AS ap ON a.AlertGuid = ap.AlertGuid
INNER JOIN Alert.vAlertResolutionState AS ar ON ar.AlertGuid = ap.AlertGuid
INNER JOIN dbo.vResolutionState AS res ON res.ResolutionStateId = ar.ResolutionState
Where a.AlertName = 'Failed to connect to Computer' AND ar.ResolutionState = 255 AND ar.ResolutionState != 0
Order BY ar.StateSetDateTime DESC

Perfect! Thanks!! :slight_smile:

1 Like
3

Resolution state names are stored in ‘dbo.ResolutionState’ - a join to this should allow you to do what you need

4

Awesome! Thanks for posting the full query too!