create procedure proc_AuditServer

@auditserver varchar(15)

as

/* 
** "Phone Home" Auditing Script for SQL Servers.
**
**	D Maxwell, June 2007
**
** This script takes configuration and job status information
** and writes it to a designated logging server.  I'll describe
** each section in detail, below.  We write to the local box first,
** Then upload everything to the logging server.
** 
** Example: proc_AuditServer 'SQLSRV3'
** 
*/

/* 
** We want to know exactly what server this is, so
** we get the server name, instance name, as well as 
** SQL Version, Edition, and Service Pack level.
*/


truncate table admin.dbo.sql_servers

insert into admin.dbo.sql_servers
	select	convert(varchar(15), serverproperty('ServerName')),
				convert(varchar(25), serverproperty('InstanceName')),
				convert(char(9), serverproperty('ProductVersion')),
				convert(varchar(4), serverproperty('ProductLevel')),
				convert(varchar(20), serverproperty('Edition')),
				getdate()

/*
** Now, having that, we get the list of databases, 
** as well as thier creation dates and file names.
*/

truncate table admin.dbo.databases

insert into admin.dbo.databases
select 
	convert(varchar(15), serverproperty('ServerName')),
	dbid,
	name, 
	crdate,
	filename
from master..sysdatabases
where dbid > 4
order by dbid

/* 
** We need to know how the server is configured, so we
** can compare it to a list of preferred configuration 
** values, as well as the defaults.  I cut this out of
** sp_configure.
*/

truncate table admin.dbo.server_config

insert into admin.dbo.server_config
   select
	convert(varchar(15), serverproperty('ServerName')),
	name,  
     config_value = c.value,  
     run_value = master.dbo.syscurconfigs.value  
   from master.dbo.spt_values, master.dbo.sysconfigures c, master.dbo.syscurconfigs  
   where type = 'C'  
     and number = c.config  
     and number = master.dbo.syscurconfigs.config  

     and  
         ((c.status & 2 <> 0 )  
              OR  
          (c.status & 2  = 0)  
         )  
   order by lower(name)  

/*
** The next configuration item we want to get is the 
** list of jobs that run on the server.  We're looking
** specifically for backup and other maintenance jobs.
** (Which will hopefully be named appropriately...)
** We use Neil Boyle's job report script for this.
** My comments and changes prefaced by a 'DM:'
*/

truncate table admin.dbo.jobs

insert into admin.dbo.jobs
select    
	convert(varchar(15), serverproperty('ServerName')), --DM: Needed since we'll have lots of servers reporting
	j.job_id, -- DM: More unique than a name.
	convert(varchar(22), j.name) as job_name,    
	case freq_type  -- Daily, weekly, Monthly
		when 1    then 'Once'
		when 4    then 'Daily'
		when 8    then 'Wk ' -- For weekly, add in the days of the week
		+    case      freq_interval & 2 when 2 then 'M' else '' end  -- Monday
		+    case      freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
		+    case      freq_interval & 8 when 8 then 'W' else '' end  -- etc
		+    case      freq_interval & 16 when 16 then 'Th' else '' end
		+    case      freq_interval & 32 when 32 then 'F' else '' end
		+    case      freq_interval & 64 when 64 then 'Sa' else '' end
		+    case      freq_interval & 1 when 1 then 'Su' else '' end
		when 16   then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Monthly on a particular day
		when 32   then 'Mthly '  -- The most complicated one, "every third Friday of the month" for example
		+ case freq_relative_interval 
			when 1 then 'Every First '
			when 2 then 'Every Second '
			when 4 then 'Every Third '
			when 8 then 'Every Fourth '
			when 16 then 'Every Last '
		end
		+ case freq_interval	
			when 1 then 'Sunday' 
			when 2 then 'Monday'
			when 3 then 'Tuesday' 
			when 4 then 'Wednesday' 
			when 5 then 'Thursday' 
			when 6 then 'Friday' 
			when 7 then 'Saturday' 
			when 8 then 'Day' 
			when 9 then 'Week day'
			when 10 then 'Weekend day'
		end	
	when 64   then 'Startup'	-- When SQL Server starts
	when 128 then 'Idle'		-- Whenever SQL Server gets bored
	else 'Err'			-- This should never happen
	end as schedule

,	case freq_subday_type		-- FOr when a job funs every few seconds, minutes or hours
		when 1    then 'Runs once at:'
		when 2    then 'every ' + convert(varchar(3), freq_subday_interval) + ' seconds'
		when 4    then 'every ' + convert(varchar(3), freq_subday_interval) + ' minutes'
		when 8    then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours'
	end as frequency

	-- All the subsrings are because the times are stored as an integer with no leading zeroes
	-- i.e. 0 means midnight, 13000 means half past one in the morning (01:30:00)

,    	substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6), 1, 2)
	+    ':'
	+ substring (
	right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_start_time), 6) ,3 ,2)
	+    ':'
	+ substring (
	right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6) ,5 ,2) as start_at

,case	freq_subday_type
	when 1  then NULL  -- Ignore the end time if not a recurring job
	else	substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6), 1, 2)
	+    ':'
	+ substring (
	right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,3 ,2)
	+    ':'
	+ substring (
	right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,5 ,2) end as end_at
from	msdb.dbo.sysjobs j
join 	msdb.dbo.sysJobSchedules s
on   	j.job_id = s.job_id
order	by j.name, start_at

/*
** Now that we know what jobs we have, let's find out 
** how they did recently. 
*/

truncate table job_status

insert into job_status
select convert(varchar(15), serverproperty('ServerName')), 
		job_id, run_status, run_date, 
		run_time, run_duration
	from msdb..sysjobhistory
	where step_name = '(job outcome)'               --  The last 90 days' worth.  
	and run_date > (select replace(convert(varchar(10), (getdate() - 90), 120), '-', ''))
	order by run_date desc

/* 
** Now for the data upload.  First, we determine if this 
** server is already connected to the auditing server.
*/

if (select count(*) from master.dbo.sysservers where srvname = 'AUDITCONN') < 1

	declare @SQL nvarchar(1000)
	set @SQL = 'master..sp_addlinkedserver ''AUDITCONN'', '' '', ''MSDASQL'', NULL, NULL,
	''Driver={SQL Server};Database=AUDITDB;Server=' + @auditserver + ';UID=auditor;PWD=*****;'''
	exec @SQL

/*
** If this server is already known to the audit server, 
** we need to remove the existing data from the audit
** tables.
*/


declare @known bit
set @known = 
	(select count(*) 
	from [AUDITCONN].AUDITDB.dbo.sql_servers 
	where server_name = 
		(select convert(varchar(15), serverproperty('servername'))))

/*
** Now we remove the existing information from the audit tables,
** if need be. 
*/

if @known = 1
	begin 

	delete from [AUDITCONN].AUDITDB.dbo.sql_servers
	where server_name = (select convert(varchar(15), serverproperty('ServerName')))

	delete from [AUDITCONN].AUDITDB.dbo.databases
	where server_name = (select convert(varchar(15), serverproperty('ServerName')))

	delete from [AUDITCONN].AUDITDB.dbo.server_config
	where server_name = (select convert(varchar(15), serverproperty('ServerName')))

	delete from [AUDITCONN].AUDITDB.dbo.jobs
	where server_name = (select convert(varchar(15), serverproperty('ServerName')))

	delete from [AUDITCONN].AUDITDB.dbo.job_status
	where server_name = (select convert(varchar(15), serverproperty('ServerName')))

	end



/* 
** Finally, we upload the new info from here to the audit server.
*/

insert into [AUDITCONN].AUDITDB.dbo.sql_servers
select * from admin.dbo.sql_servers

insert into [AUDITCONN].AUDITDB.dbo.server_config
select * from admin.dbo.server_config

insert into [AUDITCONN].AUDITDB.dbo.databases
select * from admin.dbo.databases

insert into [AUDITCONN].AUDITDB.dbo.jobs
select * from admin.dbo.jobs

insert into [AUDITCONN].AUDITDB.dbo.job_status
select * from admin.dbo.job_status

go