{"name":"DatabaseEvents","description":"An anxiety level metric based on detection of databases being stopped, started, attached, detached, created or dropped. These actions should be unusual on a production server.","tsql":"DECLARE @Target_Data XML =\r\n            (\r\n            SELECT TOP 1 Cast(xet.target_data AS XML) AS targetdata\r\n              FROM sys.dm_xe_session_targets AS xet\r\n                INNER JOIN sys.dm_xe_sessions AS xes\r\n                  ON xes.address = xet.event_session_address\r\n              WHERE xes.name = 'DatabaseEvents'\r\n                AND xet.target_name = 'ring_buffer'\r\n            );\r\nSELECT COALESCE(Sum( CASE the.event_data.value('(@name)[1]', 'sysname') \r\n              WHEN 'database_started' THEN  1 --on a rating 1-10\r\n              WHEN 'database_stopped' THEN 8  --on a rating 1-10\r\n              WHEN 'database_attached' THEN 3 --on a rating 1-10\r\n              WHEN 'database_detached' THEN 7 --on a rating 1-10\r\n              WHEN 'database_created' THEN 4  --on a rating 1-10\r\n              WHEN 'database_dropped' THEN 10 --on a rating 1-10\r\n            END\r\n          ),0)\r\n    FROM @Target_Data.nodes('\/\/RingBufferTarget\/event') AS the(event_data)\r\n  WHERE DateDiff (\r\n          MINUTE, Convert(DATETIME2,\r\n           SwitchOffset(\r\n             Convert(DATETIMEOFFSET,\r\n                     the.event_data.value('(@timestamp)[1]', 'datetime2')),\r\n                     DateName(TzOffset,SysDateTimeOffset()))\r\n          ), GetDate()) < 60 --AnxietyLevel is measured over a 60 minute span","instances":false,"databases":{"mode":1},"frequency":60,"metricenabled":true,"noalertremark":"The appropriate alert threshold depends on how often these events are likely to occur on your systems. You should set the alert threshold after establishing a baseline for your working instance.","frequency_comment":"seconds (collected every minute)","rateofchange":false,"aboveorbelow":"above","_thresholds_high":{"selected":false},"_thresholds_medium":{"selected":false},"_thresholds_low":{"selected":false},"collections":1,"alertenabled":true,"targetentitytype":0}