Wednesday, May 26, 2004

Morning DBA Report

Well here it is, my latest little gem.

First off, you have to understand my needs.

I am a web developer, for an ecommerce site, we do not have a dba available to be able to monitor the performance of our site/databases.

So therefore, I have tried to develop ways to ease my monitoring the growth/problems/security/issues with both our Development and Production Databases.

I have done a lot of Q&A on Experts Exchange http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20996844.html

So from there and other resources, I have found the data I want to make available to me every morning.

The goal is that every morning, I want to make sure everything went alright from last night, and make sure our growth isn't out of control.

Above is a link to a list of Seven Monitoring Scripts. They were used preeminently, in my coding of my Morning DBA Report.

This is mainly for SQL Server, although you can probably find similar scripts for other databases.

Also with SQL Server is a SQLMail option, that can be used to send you alerts/reports and so forth. But it can also be a security vulnerability. So I always have that configured as off.

So that means I have to use ColdFusion to gather the data from the scripts, and then put it into a readable format.

Here is the code:

<!--- step by step create an printable report of morning/last nights' activities --->

<!--- step one Scheduled Tasks Last Night --->

<!--- Failed Jobs Report --->
<cfquery name="development_failed_reports" datasource="development_database">
SELECT a.name
FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B
WHERE A.job_id = B.job_id
AND B.last_run_outcome = 0
</cfquery>

<cfquery name="production_failed_reports" datasource="production_database">
SELECT a.name
FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B
WHERE A.job_id = B.job_id
AND B.last_run_outcome = 0
</cfquery>

<!--- Disabled Jobs Report --->
<cfquery name="development_disabled_reports" datasource="development_database">
SELECT name
FROM msdb.dbo.sysjobs
WHERE enabled = 0
ORDER BY name
</cfquery>

<cfquery name="production_disabled_reports" datasource="production_database">
SELECT name
FROM msdb.dbo.sysjobs
WHERE enabled = 0
ORDER BY name
</cfquery>

<!--- step two Backup Status Report --->
<cfquery name="development_backup_reports" datasource="development_database">
select b.name as database_name, isnull(str(abs(datediff(day, getdate(), max(backup_finish_date)))), 'never') as dayssincelastbackup, isnull(convert(char(10), max(backup_finish_date), 101), 'never') as lastbackupdate
from master.dbo.sysdatabases b left outer join msdb.dbo.backupset a on a.database_name = b.name and a.type = 'd'
group by b.name
order by b.name
</cfquery>

<cfquery name="production_backup_reports" datasource="production_database">
select b.name as database_name, isnull(str(abs(datediff(day, getdate(), max(backup_finish_date)))), 'never') as dayssincelastbackup, isnull(convert(char(10), max(backup_finish_date), 101), 'never') as lastbackupdate
from master.dbo.sysdatabases b left outer join msdb.dbo.backupset a on a.database_name = b.name and a.type = 'd'
group by b.name
order by b.name
</cfquery>

<!--- step three Hard Drive Status Report --->
<cfquery name="development_harddrive_reports" datasource="development_database">
EXEC master..xp_fixeddrives
</cfquery>

<cfquery name="production_harddrive_reports" datasource="production_database">
EXEC master..xp_fixeddrives
</cfquery>

<cfquery name="development_status_reports" datasource="development_database">
EXEC master..sp_helpdb
</cfquery>

<cfquery name="production_status_reports" datasource="production_database">
EXEC master..sp_helpdb
</cfquery>

<!--- now put into cfmail tag --->
<cfmail to="your@email_address.com" subject="Morning DBA Report" from="your@email_address.com" type=html>
<font face="verdana, arial, helvetica, sans-serif" size=+1>Morning DBA Report for #dateformat(now(),'dddd mmm, d, yyyy')# #timeformat(now(),'hh:mm:ss tt')#</font>
<br><Br>
<font face="verdana, arial, helvetica, sans-serif" size=-1><b>Failed Jobs Report:</b></font>#chr(10)#<br><br>
<Cfif development_failed_reports.recordcount gt 0>
On Dev:#chr(10)#<br>
<cfloop query="development_failed_reports">
#development_failed_reports.name##chr(10)#<br>
</cfloop>
</cfif>
<Cfif production_failed_reports.recordcount gt 0>
On Production:#chr(10)#<br>
<cfloop query="production_failed_reports">
#production_failed_reports.name##chr(10)#<br>
</cfloop>
</cfif>
<br>
<font face="verdana, arial, helvetica, sans-serif" size=-1><b>Disabled Jobs Report:</b></font>#chr(10)#<br><br>
<Cfif development_disabled_reports.recordcount gt 0>
On Dev:#chr(10)#<br>
<cfloop query="development_disabled_reports">
#development_disabled_reports.name##chr(10)#<br>
</cfloop>
</cfif>
<Cfif production_disabled_reports.recordcount gt 0>
On Production:#chr(10)#<br>
<cfloop query="production_disabled_reports">
#production_disabled_reports.name##chr(10)#<br>
</cfloop>
</cfif>
<font face="verdana, arial, helvetica, sans-serif" size=-1><b>Backup Reports:</b></font>#chr(10)#<br><br>
<Cfif (development_backup_reports.recordcount gt 0) or (production_backup_reports.recordcount gt 0)>
<table width=100% cellspacing=0 cellpadding=2 border=1 bordercolor=0D4D77>
<tr bgcolor=BEC8D8>
<td align=center><font face="verdana, arial, helvetica, sans-serif" size=-1>Server</font></td>
<td align=center><font face="verdana, arial, helvetica, sans-serif" size=-1>Database Name</font></td>
<td align=center><font face="verdana, arial, helvetica, sans-serif" size=-1>Days Since Last Backed Up</font></td>
<td align=center><font face="verdana, arial, helvetica, sans-serif" size=-1>Last BackUp Date</font></td>
</tr>
</cfif>
<Cfif development_backup_reports.recordcount gt 0>
<cfloop query="development_backup_reports">
<tr>
<td align=center><font face="verdana, arial, helvetica, sans-serif" size=-1>Dev</font></td>
<td align=left><font face="verdana, arial, helvetica, sans-serif" size=-1> #development_backup_reports.database_name#</font></td>
<td align=center><font face="verdana, arial, helvetica, sans-serif" size=-1>#development_backup_reports.dayssincelastbackup#</font></td>
<td align=center><font face="verdana, arial, helvetica, sans-serif" size=-1>#development_backup_reports.lastbackupdate#</font></td>
</tr>
</cfloop>
</cfif>
<Cfif production_backup_reports.recordcount gt 0>
<cfloop query="production_backup_reports">
<tr>
<td align=center><font face="verdana, arial, helvetica, sans-serif" size=-1>Production</font></td>
<td align=left><font face="verdana, arial, helvetica, sans-serif" size=-1> #production_backup_reports.database_name#</font></td>
<td align=center><font face="verdana, arial, helvetica, sans-serif" size=-1>#production_backup_reports.dayssincelastbackup#</font></td>
<td align=center><font face="verdana, arial, helvetica, sans-serif" size=-1>#production_backup_reports.lastbackupdate#</font></td>
</tr>
</cfloop>
</cfif>
<Cfif (development_backup_reports.recordcount gt 0) or (production_backup_reports.recordcount gt 0)>
</table>
</cfif>
<br>
<font face="verdana, arial, helvetica, sans-serif" size=-1><b>Database Size Report:</b></font>#chr(10)#<br><br>
<Cfif (development_status_reports.recordcount gt 0) or (production_status_reports.recordcount gt 0)>
<table width=100% cellspacing=0 cellpadding=2 border=1 bordercolor=0D4D77>
<tr bgcolor=BEC8D8>
<td align=center><font face="verdana, arial, helvetica, sans-serif" size=-1>Server</font></td>
<td align=center><font face="verdana, arial, helvetica, sans-serif" size=-1>Database Name</font></td>
<td align=center><font face="verdana, arial, helvetica, sans-serif" size=-1>Hard Drive Space Usage</font></td>
</tr>
</cfif>
<Cfif development_status_reports.recordcount gt 0>
<cfloop query="development_status_reports">
<tr>
<td align=center><font face="verdana, arial, helvetica, sans-serif" size=-1>Dev</font></td>
<td align=left><font face="verdana, arial, helvetica, sans-serif" size=-1> #development_status_reports.name#</font></td>
<td align=left><font face="verdana, arial, helvetica, sans-serif" size=-1> #development_status_reports.db_size#</font></td>
</tr>
</cfloop>
</cfif>
<cfif production_status_reports.recordcount gt 0>
<cfloop query="production_status_reports">
<tr>
<td align=center><font face="verdana, arial, helvetica, sans-serif" size=-1>Production</font></td>
<td align=left><font face="verdana, arial, helvetica, sans-serif" size=-1> #production_status_reports.name#</font></td>
<td align=left><font face="verdana, arial, helvetica, sans-serif" size=-1> #production_status_reports.db_size#</font></td>
</tr>
</cfloop>
</cfif>
<Cfif (development_status_reports.recordcount gt 0) or (production_status_reports.recordcount gt 0)>
</table>
</cfif>
<br>
</cfmail>

That's my gem this week.

I am planning to add things like hard drive space and so forth.

What kinds of things would you like to see as a morning report?

No comments:

Post a Comment