I had a customer ask how they could use Foglight to find out when "dbcc checkdb" last ran against their SQL Server databases.
This is a great use case for User-Defined Collections!
The difficult part is typically researching the problem, and either writing or finding something suitable to use to query the DB. Luckily there's this new thing called Google. My colleague Jason Hall sent me a script that he modified from this excellent post on the topic.
Here is the script that I used for a UDC in Foglight:
IF OBJECT_ID('tempdb.dbo.#temp', 'U') IS NOT NULL DROP TABLE #temp; CREATE TABLE #temp ( Id INT IDENTITY(1,1), ParentObject VARCHAR(255), [Object] VARCHAR(255), Field VARCHAR(255), [Value] VARCHAR(255) ) IF OBJECT_ID('tempdb.dbo.#DBCCres', 'U') IS NOT NULL DROP TABLE #DBCCres; CREATE TABLE #DBCCRes ( Id INT IDENTITY(1,1)PRIMARY KEY CLUSTERED, DBName sysname , dbccLastKnownGood DATETIME, RowNum INT ) DECLARE @DBName SYSNAME, @SQL varchar(512); DECLARE dbccpage CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR Select name from sys.databases where name not in ('tempdb') AND state_desc = 'ONLINE'; Open dbccpage; Fetch Next From dbccpage into @DBName; While @@Fetch_Status = 0 Begin Set @SQL = 'Use [' + @DBName +'];' +char(10)+char(13) Set @SQL = @SQL + 'DBCC Page ( ['+ @DBName +'],1,9,3) WITH TABLERESULTS;' +char(10)+char(13) INSERT INTO #temp Execute (@SQL); Set @SQL = '' INSERT INTO #DBCCRes ( DBName, dbccLastKnownGood,RowNum ) SELECT @DBName, VALUE , ROW_NUMBER() OVER (PARTITION BY Field ORDER BY Value) AS Rownum FROM #temp WHERE field = 'dbi_dbccLastKnownGood'; TRUNCATE TABLE #temp; Fetch Next From dbccpage into @DBName; End Close dbccpage; Deallocate dbccpage; SELECT DBName,dbccLastKnownGood FROM #DBCCRes WHERE RowNum = 1; DROP TABLE #temp DROP TABLE #DBCCRes
I was skeptical that it would accept the temp table drops and creates, but it worked.
The last part of the T-SQL returns 2 columns that Foglight can use - DBName and dbccLastKnownGood:
WHERE RowNum = 1;
The key (pun intended) is to set the "Is Key" flag on the dbname column to true.
Save the changes on the way out, and let the agent collect some data for 10 minutes or so.
On the User-Defined Collection drilldown, you should see data similar to this:
This is good so that we can review the last check date per database. If we want to write a rule to alert us that the last check was 'x' days ago, then it becomes a bit more difficult with this dataset. Writing rules on the data in a user-defined collection is "easy" when the query returns a single value. When there are multiple columns and/or rows, it's more difficult.
So.. you can modify the UDC query a little bit. I would keep everything "as-is", except replace the last SELECT with this:
select count(*) as check14
where RowNum =1
and DATEDIFF(DD, dbccLastKnownGood,GETDATE()) > 14;
You can replace 14 with whatever number of days you're comfortable with. Now a single value gets returned to the UDC with the number of databases that had their dbcc checkdb done more than 14 days ago. You can refer to my post linked at the top on how to create a rule for this if you'd like.
To learn more and give Foglight a try, just go here for a trial download.