Learn how your company can work directly with me or one of my peers.
The benefit is not just having an experienced engineer helping you manage the health of your environment. Through Premier Field Engineering, your company will have access to a wealth of knowledge from all of PFE and a channel into the product group to answer the most complex questions.
/*
RMS Unavailable History with Reason
http://blogs.technet.com/b/jonathanalmquist/
04-19-2011
Updated 04-20-2011 with outage durations dd:hh:mm
*/
SELECT
ME.Path,
HSO.StartDateTime AS OutageStartDateTime,
CAST (DATEDIFF(SECOND, HSO.StartDateTime, HSO.EndDateTime)/86400 AS VARCHAR(5)) + ':' +
CONVERT(CHAR(5), DATEADD(SECOND, DATEDIFF(SECOND, HSO.StartDateTime, HSO.EndDateTime), ''), 114) AS 'Duration (dd:hh:mm)',
HSO.ReasonCode,
DS.Name AS ReasonString
FROM vManagedEntity AS ME INNER JOIN
vHealthServiceOutage AS HSO ON HSO.ManagedEntityRowId = ME.ManagedEntityRowId INNER JOIN
vStringResource AS SR ON HSO.ReasonCode =
REPLACE(LEFT(SR.StringResourceSystemName, LEN(SR.StringResourceSystemName)
- CHARINDEX('.', REVERSE(SR.StringResourceSystemName))), 'System.Availability.StateData.Reasons.', '') INNER JOIN
vDisplayString AS DS ON DS.ElementGuid = SR.StringResourceGuid
WHERE (SR.StringResourceSystemName LIKE 'System.Availability.StateData.Reasons.[0-9]%') AND
(ME.Path in (
SELECT DISTINCT Path
FROM vManagedEntity
WHERE FullName LIKE 'Microsoft.SystemCenter.NotificationServer:%')
)
ORDER BY OutageStartDateTime DESC
Go to main page to download formatted TSQL scripts for all report dataset samples on my blog.
Getting the following syntax error while executing this query against DW DB, am missing anything?
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'SR'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ')'.
@Ramesh - Please use the link at end of post to download the formatted TSQL. There are problems with copy/paste from the code snippet window.