As you might know, in SAM you can use SQL queries against the SQL databases to collect information. The only thing you have to consider is that the output of the script have to be on single numeric value (and an optional string). The query also have to give you a 0 if there is no result, null is not allowed.
So when creating a query, think like this for example: "how many SQL agent jobs has failed last x hours"
Here is an example of that:
-- Variable Declarations
DECLARE @Count INT
Declare @TimeCheck AS Datetime
Declare @SearchFor AS char (4)
-- Initialize Variables
SET @Count = 0
set @TimeCheck = CONVERT(datetime,DATEADD(HOUR,-20,SYSDATETIMEOFFSET())) -- How long back to search for failed jobs
Set @SearchFor='MT - ' --Job Name string to search for, job name starting with this, edit char lenght for number of characters
Begin
Select @Count = COUNT(*) from (
select run_status
from dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
where
CAST
(
CONVERT(VARCHAR, h.run_date)
+ ' ' + STUFF(STUFF(RIGHT('000000'
+ CONVERT(VARCHAR,h.run_time),6),5,0,':'),3,0,':')
AS DATETIME
)> @TimeCheck AND
h.run_status=0 AND
h.step_id<>0 AND
j.[name] LIKE @SearchFor + '%'
) as t
If (@Count =0)
select 0
else
select
count(*) as "jobs",
j.[name]
from dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
where
CAST
(
CONVERT(VARCHAR, h.run_date)
+ ' ' + STUFF(STUFF(RIGHT('000000'
+ CONVERT(VARCHAR,h.run_time),6),5,0,':'),3,0,':')
AS DATETIME
)> @TimeCheck AND
h.run_status=0 AND
h.step_id<>0 AND
j.[name] LIKE @SearchFor + '%'
group by j.[name];
End
Another check could be "how many databases has not been backed up last xx hours":
SELECT
d.database_id,
DatabaseName = d.name ,
LatetstBackUpDate = ISNULL(CONVERT(VARCHAR(30),MAX(b.backup_finish_date)),'No backup'),
'Days' = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) / 24,
'Hours' = MIN(DATEDIFF(hh,b.backup_finish_date,getdate())) % 24
INTO #BackupInfo
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name
WHERE d.name NOT IN ('tempdb','model','ReportServerTempDB','ReportServer','master','msdb') and d.state != 6
GROUP BY d.database_id, d.name
HAVING ISNULL(MIN(DATEDIFF(hh,b.backup_finish_date,getdate())),999) >= 24 * 2
IF (Select count(*) as "FirstCount" from(select b.database_id from #BackupInfo b) as FC)>0
BEGIN
Select
count(*) as "Rowcount",
t.Backupinfo
from(
Select
substring(
(
Select ', '+bkt1.DatabaseName AS [text()]
From #BackupInfo bkt1
For XML PATH ('')
)
, 2, 1000) [Backupinfo]
From #BackupInfo bkt2)
as t
group by t.backupinfo
END
ELSE
Select 0
drop table #backupinfo
As you see there is some extra code in the end, that is so that you make sure to receive a 0 when there is no failed backups AND to get the name of the databases with failing backups on one row.
So everything is possible, just ask SQL the right question 🙂
Good luck!
