Dear expert,
I am new to solarwinds and will like to monitor database job status, alive and size of directory.
Anyone know is there any custom queries to the above.
Or any solution..
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!
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.