Auto Download of Health check report CSV mail and process by Batch

To automatically download the mail, use the macro as in my case i use imacro for Firefox, you can use the imacro for internet explorer but this version has some problem with my mail server.

http://www.iopus.com/imacros/firefox/?ref=fxhome

Here's my macro, with amendments:-

VERSION BUILD=6050612 RECORDER=FX
TAB T=1
URL GOTO=http://mailbox.id
TAG POS=1 TYPE=INPUT:SUBMIT FORM=ACTION:/?service=login&pragma=nocache ATTR=VALUE:Login
TAG POS=1 TYPE=FONT ATTR=TXT:*Report*
TAB T=2
FRAME F=2
TAG POS=1 TYPE=FONT ATTR=TXT:DailyHealthcheck.cab
'SAVEAS TYPE=CPL FOLDER=* FILE=+_{{!NOW:yyyymmdd_hhnnss}}
ONDOWNLOAD FOLDER=* FILE=+_{{!NOW:yyyymmdd_hhnnss}}
TAB T=1
FRAME F=0
BACK
WAIT SECONDS=2
TAB CLOSEALLOTHERS
TAG POS=1 TYPE=INPUT:CHECKBOX FORM=NAME:select ATTR=NAME:tid CONTENT=YES
WAIT SECONDS=2
ONDIALOG POS=1 BUTTON=CANCEL CONTENT=
TAG POS=1 TYPE=FONT ATTR=TXT:DELETEMESSAGE
TAG POS=1 TYPE=INPUT:BUTTON FORM=NAME:NoFormName ATTR=NAME:button1&&VALUE:Return
TAG POS=1 TYPE=IMG ATTR=ALT:Refresh

My batch file looks like this, it will process each file with the cab extension , archive it and expand it to report.csv, then will bcp into database and the next command will processed the data.

for %%f in (*.cab) do copy %%f archive\%%f && expand %%f Report.csv && bcp dbaRepository.dbo.tCSV_Data in Report.csv -t, -S servername-U userid -P pwd -w -C 850 && Del %%f

rem Remember to grant execute permission to the user to execute the stored procedure

osql -S servername -U userid -P password -Q "exec dbaRepository..sp_updateCSVtables"

SQL Server 2000 health check script in CVS(with compress)

create procedure sp_emailDailyHealthCheckCSV
@charfrom varchar(50),
@charsubject varchar(100),
@charemailserver varchar(15),
@intresults int OUTPUT
AS

/******************************************************************
*
* DAILY HEALTH CHECK SCRIPT - SQL Server
*
*
******************************************************************/
/*
declare @rc int
set @rc=0
exec sp_emailDailyHealthCheck
@charfrom = Ntest@test.com',
@charemailserver = N'188.188.1.13',
@charsubject = N'SQL Health Check Report: (Server: "DELL2500")',
@intresults = @rc

CVS format
COLLECTIONTYPE as varchar , e.g. SQLSVR
COLLECTIONSOURCE as varchar, e.g. DELL2500
COLLECTIONDATETIME as varchar, e.g. Mar 6 2008 8:00AM
COLLECTIONNAME as varchar e.g. DISKSPACE
COLLECTIONHEADER as varchar e.g. USEDSPACE
COLLECTIONVALUE1 as varchar e.g. C
COLLECTIONVALUE2 as varchar e.g. 100 or 200

*/
/*****************************************
* Create temp tables for storing the health check output
*****************************************/



declare @COLLECTIONTYPE varchar(20)
declare @COLLECTIONSOURCE varchar(50)
declare @COLLECTIONDATETIME varchar(20)

set @COLLECTIONTYPE = 'SQLSERVR'
set @COLLECTIONSOURCE = @@servername
set @COLLECTIONDATETIME = cast(getdate() as varchar)

CREATE TABLE #csv_output (COLLECTIONTYPE varchar(20), COLLECTIONSOURCE varchar(50),
COLLECTIONDATETIME varchar(20), COLLECTIONNAME varchar(50),
COLLECTIONHEADER varchar(50), COLLECTIONVALUE1 varchar(500),
COLLECTIONVALUE2 varchar(500) )

insert into #csv_output values('COLLECTIONTYPE', 'COLLECTIONSOURCE', 'COLLECTIONDATETIME',
'COLLECTIONNAME','COLLECTIONHEADER','COLLECTIONVALUE1','COLLECTIONVALUE2')

insert into #csv_output values(@COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SQLVERSION','SQLVERSION',replace(replace(@@version,char(9),''),char(10),''),'')



Declare @output varchar(1000)


--- Get the ip address of the server
CREATE TABLE #temp1(t varchar(3000) null)
insert into #temp1 exec xp_cmdshell 'ipconfig'
DECLARE @t1 varchar(300)-- subject to localisation

SET @t1 = (
SELECT top 1 #temp1.t from #temp1
where t like '%IP Address%'
order by t DESC
)

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'IPADRESS','IPADRESS',LTRIM(RTRIM(SUBSTRING(t,CHARINDEX(':',t)+1,len(@t1)))),''
from #temp1
where t like '%IP Address%'


drop table #temp1
-- end of getting ip address


/******************************************************************
*
* Get Total Disk Space Availaible
*
* This script displays Drive and total sql size used and free size
*
*
*****************************************************************/
Begin
Declare @a Varchar(50)
,@b Varchar(50)
,@c Varchar(50)
,@TSize1 VARCHAR(255)

Create Table #Ops_Drives
(
Drive Varchar(1),
Size int
)
Declare @a1 int
,@b1 int
,@c1 int
,@d1 int

Create Table #Ops_DriveTmp
(
DBName Varchar(25)
,Location Varchar(255)
,Size Varchar(8)
,MaxSize int
,Growth Int
,Device Varchar(30)
)

Exec SP_MSForEachDB
'Use [?] Insert into #Ops_DriveTmp
Select Upper(Convert(Varchar(25),DB_Name())) as ''Database'',
Convert(Varchar(255),FileName),Convert(Varchar(8),Size/128)''Size in MB'',
MaxSize,Growth,
Convert(Varchar(30),Name)
from SysFiles'


Select @a1=Sum(Convert(Int,Size)) from #Ops_DriveTmp

Insert Into #Ops_Drives
Exec Master..XP_Fixeddrives
Create Table #Ops_TmpDriveInfo
(
Drive Varchar(1),
SQL Varchar(8),
FreeSpace Varchar(255),
TotalSpace varchar(255),
Util varchar(4)
)

DECLARE @oFSO1 INT,@oDrive1 INT, @ret1 INT
EXEC @ret1 = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO1 OUT

Declare CheckDrives Cursor For Select Drive,size from #Ops_Drives Order by Drive
Open CheckDrives
Fetch Next from CheckDrives into @c,@c1
While (@@Fetch_Status=0)
Begin
Select @b1=sum(convert(Int,Size))
from #Ops_DriveTmp
where substring(Location,1,1)=@c

Set @a =@c + ':\'

EXEC @ret1 = master.dbo.sp_OAMethod @oFSO1, 'GetDrive', @oDrive1 OUT, @a
EXEC @ret1 = master.dbo.sp_OAMethod @oDrive1, 'TotalSize', @TSize1 OUT

set @d1 = CAST(ROUND((CAST(isnull(@TSize1,1) AS FLOAT) / 1024) /1024,0) AS INT)

Insert into #Ops_TmpDriveInfo values(@c,isnull(@b1,0),@c1,@d1 ,
(100 -(round(( @c1)/ ROUND((CAST(isnull(@TSize1,1) AS FLOAT) / 1024) /1024 ,0),3) * 100)) )

Fetch Next from CheckDrives into @c,@c1
End



insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DISKSPACE','SQLSERVERDATA',Drive,[SQL]
from #Ops_TmpDriveINFO

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DISKSPACE','FREESPACE',Drive,FreeSpace
from #Ops_TmpDriveINFO


insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DISKSPACE','TOTALSPACE',Drive,TotalSpace
from #Ops_TmpDriveINFO

drop table #Ops_TmpDriveINFO
drop table #Ops_DriveTmp
drop table #Ops_Drives

exec @ret1 = sp_OADestroy @oFSO1
exec @ret1 = sp_OADestroy @oDrive1

Close CheckDrives

Deallocate CheckDrives

End
/******************************************************************
*
* End Of Get Total Disk Space Availaible
******************************************************************/

/******************************************************************
*
* Get Summary Database Details
*
* This script displays Summary Database Details, % used
*
*
*****************************************************************/
SET nocount ON

DECLARE @dbname SYSNAME

CREATE TABLE #datafilestats (
dbname VARCHAR(200) null,
flag BIT DEFAULT 0 ,
fileid TINYINT,
[filegroup] TINYINT,
totalextents DEC(20,1),
usedextents DEC(20,1),
[name] VARCHAR(200),
[filename] VARCHAR(200))


DECLARE @string SYSNAME

SET @string = ''

SET @dbname = ''

WHILE 1 = 1
BEGIN
SELECT top 1 @dbname = name
FROM master..sysdatabases
WHERE name > @dbname
IF @@ROWCOUNT = 0
BREAK
IF NOT (SELECT Databasepropertyex(@dbname,'Status')) = 'ONLINE'
BREAK
SET @string = 'use [' + @dbname + '] DBCC SHOWFILESTATS with no_infomsgs'
INSERT INTO #datafilestats
(fileid,
[filegroup],
totalextents,
usedextents,
[name],
[filename])
EXEC( @string)
UPDATE #datafilestats
SET dbname = @dbname,
flag = 1
WHERE flag = 0
UPDATE #datafilestats
SET totalextents = (SELECT SUM(totalextents) * 8 * 8192.0 / 1048576.0
FROM #datafilestats
WHERE dbname = @dbname)
WHERE flag = 1
AND fileid = 1
AND filegroup = 1
AND dbname = @dbname
UPDATE #datafilestats
SET usedextents = (SELECT SUM(usedextents) * 8 * 8192.0 / 1048576.0
FROM #datafilestats
WHERE dbname = @dbname)
WHERE flag = 1
AND fileid = 1
AND filegroup = 1
AND dbname = @dbname
END

CREATE TABLE #sizeinfo (
db_name VARCHAR(200) NOT NULL PRIMARY KEY CLUSTERED,
total DEC(20,1) null,
data DEC(20,1) null,
data_used DEC(20,1) null,
[data (%)] DEC(20,1) null ,
data_free DEC(20,1) null,
[data_free (%)] DEC(20,1) null,
LOG DEC(20,1) null,
log_used DEC(20,1) null,
[log (%)] DEC(20,1) null,
log_free DEC(20,1) null,
[log_free (%)] DEC(20,1) null,
status DEC(20,1))

INSERT #sizeinfo
(db_name,
[log],
[log (%)],
status)
EXEC( 'dbcc sqlperf(logspace) with no_infomsgs')

UPDATE #sizeinfo
SET data = d.totalextents
FROM #datafilestats d
JOIN #sizeinfo s
ON d.dbname = s.db_name
WHERE d.flag = 1
AND d.fileid = 1
AND d.filegroup = 1

UPDATE #sizeinfo
SET data_used = d.usedextents
FROM #datafilestats d
JOIN #sizeinfo s
ON d.dbname = s.db_name
WHERE d.flag = 1
AND d.fileid = 1
AND d.filegroup = 1

UPDATE #sizeinfo
SET total = (data + LOG)

UPDATE #sizeinfo
SET [data (%)] = (data_used * 100.0 / data)

UPDATE #sizeinfo
SET data_free = (data - data_used)

UPDATE #sizeinfo
SET [data_free (%)] = (100 - [data (%)])

UPDATE #sizeinfo
SET log_used = (LOG * [log (%)] / 100.0)

UPDATE #sizeinfo
SET log_free = (LOG - log_used)

UPDATE #sizeinfo
SET [log_free (%)] = (log_free * 100.0 / LOG)

BEGIN

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DATABASESUMMARY','TOTALDATAUSED',db_name, cast(data_used as varchar)
FROM #sizeinfo WITH (nolock)

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DATABASESUMMARY','TOTALDATAFREE',db_name, cast(data_free as varchar)
FROM #sizeinfo WITH (nolock)

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DATABASESUMMARY','TOTALLOGUSED',db_name, cast(log_used as varchar)
FROM #sizeinfo WITH (nolock)


insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DATABASESUMMARY','TOTALLOGFREE',db_name,cast(log_free as varchar)
FROM #sizeinfo WITH (nolock)

END

DROP TABLE #datafilestats

DROP TABLE #sizeinfo

/******************************************************************
*
* End Of Summary Database Details
*
*****************************************************************/





/******************************************************************
*
* SQL Server Disk Space Check
*
* This script displays database and file size, compared to total
* and free disk space. It also displays the number of Kb that each
* file will grow by on the next extend.
*
******************************************************************/

BEGIN
/*****************************************
* Create temp tables for disk space info
*****************************************/
CREATE TABLE #space (dletter varchar(2) null, fspace int, tspace BIGINT null)
CREATE TABLE #dbsize (dbname varchar(50), dbsize int, remarks varchar(255) null)
CREATE TABLE #fdata ([name] VARCHAR(255), [filename] VARCHAR(255),
[filegroup] VARCHAR(10) null, [size] VARCHAR(50),
[maxsize] VARCHAR(50), growth VARCHAR(20), usage VARCHAR(20))
CREATE TABLE #growth (dbname VARCHAR(50), fname VARCHAR(255), next_exp INT, gtype VARCHAR(2))

/*****************************************
* populate temp tables
*****************************************/
INSERT INTO #space (dletter, fspace) EXEC master.dbo.xp_fixeddrives
INSERT INTO #dbsize EXEC master.dbo.sp_databases

-- Create cursor for files
DECLARE c_files CURSOR FOR
SELECT RTRIM(af.fileid), RTRIM(af.[name]), RTRIM(af.[filename]),
RTRIM(af.[size]), RTRIM(db.[name])
FROM master.dbo.sysaltfiles af, master.dbo.sysdatabases db
WHERE af.dbid = db.dbid AND db.version <> 0
DECLARE @tfileid INT, @tname VARCHAR(255), @tfilename VARCHAR(255)
DECLARE @tsize INT, @tdbname VARCHAR(50)
DECLARE @SQL NVARCHAR(255)
DECLARE @growth VARCHAR(20), @next_exp INT, @gtype VARCHAR(2)

-- Open cursor
OPEN c_files
FETCH NEXT FROM c_files
INTO @tfileid, @tname, @tfilename, @tsize, @tdbname

-- Populate #growth table with file growth details
WHILE @@fetch_status = 0
BEGIN
TRUNCATE TABLE #fdata
-- Get file data
SET @SQL = 'INSERT INTO #fdata EXEC '
SET @SQL = @SQL + @tdbname + '.dbo.sp_helpfile ''' + @tname + ''''
EXEC sp_executesql @SQL
SELECT @growth = growth FROM #fdata
-- Determine if growth is % or Mbytes
IF RIGHT(@growth,1) = '%'
BEGIN
SET @next_exp = CAST(LEFT(@growth, LEN(@growth) - 1) AS INT)
SET @next_exp = CAST(ROUND(((CAST(@tsize AS FLOAT) * 8) / 100) * @next_exp,0) AS INT)
SET @gtype = '%'
END
ELSE
BEGIN
SET @next_exp = CAST(LEFT(@growth, CHARINDEX(' ',@growth)) AS INT)
SET @gtype = 'MB'
END
-- Create record for file in #growth table
INSERT INTO #growth VALUES (@tdbname, @tname, @next_exp, @gtype)
FETCH NEXT FROM c_files
INTO @tfileid, @tname, @tfilename, @tsize, @tdbname
END

-- Close cursor
CLOSE c_files
DEALLOCATE c_files

/*****************************************
* Update temp table info with total disk sizes
*****************************************/
-- Create cursor for disk space table
DECLARE c_disks CURSOR FOR
SELECT dletter, fspace, tspace FROM #space
FOR UPDATE
DECLARE @dletter VARCHAR(2), @fspace INT, @tspace BIGINT

-- Create FileSystemObject
DECLARE @oFSO INT, @oDrive INT, @drsize VARCHAR(255), @ret INT
EXEC @ret = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO OUT



-- Open cursor and fetch first row
OPEN c_disks
FETCH NEXT FROM c_disks
INTO @dletter, @fspace, @tspace

-- Loop through all records in the cursor
WHILE @@fetch_status = 0
BEGIN
-- Get disk size
SET @dletter = @dletter + ':\'
EXEC @ret = master.dbo.sp_OAMethod @oFSO, 'GetDrive', @oDrive OUT, @dletter
EXEC @ret = master.dbo.sp_OAMethod @oDrive, 'TotalSize', @drsize OUT
-- Update table
UPDATE #space
SET tspace = CAST(@drsize AS BIGINT)
WHERE CURRENT OF c_disks
-- Destory oDrive
EXEC master.dbo.sp_OADestroy @oDrive
-- Fetch next row
FETCH NEXT FROM c_disks
INTO @dletter, @fspace, @tspace


END

-- Close cursor
CLOSE c_disks
DEALLOCATE c_disks

-- Destroy FSO
EXEC master.dbo.sp_OADestroy @oFSO


/*****************************************
* Return disk space info
*****************************************/

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DATABASEDETAIL','DATABASEFILENAME',db.[name],af.[name]
FROM master.dbo.sysaltfiles af, #space s, #dbsize ds,
master.dbo.sysdatabases db, #growth g
WHERE s.dletter = LEFT(af.[filename],1)
AND af.dbid = db.dbid
AND db.[name] = ds.dbname
AND g.dbname = db.[name]
AND g.fname = af.[name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DATABASEDETAIL','DATABASEFILEPATH',af.[name], af.[filename]
FROM master.dbo.sysaltfiles af, #space s, #dbsize ds,
master.dbo.sysdatabases db, #growth g
WHERE s.dletter = LEFT(af.[filename],1)
AND af.dbid = db.dbid
AND db.[name] = ds.dbname
AND g.dbname = db.[name]
AND g.fname = af.[name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DATABASEDETAIL','DATABASEFILESIZE',af.[name], CAST(ROUND((CAST(af.[size] AS FLOAT) * 8) / 1024,0) AS varchar)
FROM master.dbo.sysaltfiles af, #space s, #dbsize ds,
master.dbo.sysdatabases db, #growth g
WHERE s.dletter = LEFT(af.[filename],1)
AND af.dbid = db.dbid
AND db.[name] = ds.dbname
AND g.dbname = db.[name]
AND g.fname = af.[name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DATABASEDETAIL','DATABASEFILENEXTEXTENSIONSIZE',af.[name], STR(g.next_exp)
FROM master.dbo.sysaltfiles af, #space s, #dbsize ds,
master.dbo.sysdatabases db, #growth g
WHERE s.dletter = LEFT(af.[filename],1)
AND af.dbid = db.dbid
AND db.[name] = ds.dbname
AND g.dbname = db.[name]
AND g.fname = af.[name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'DATABASEDETAIL','DATABASEFILENEXTEXPANSIONSIZE',af.[name], CAST(ROUND(CAST(g.next_exp AS FLOAT) / 1024,0) AS varchar)
FROM master.dbo.sysaltfiles af, #space s, #dbsize ds,
master.dbo.sysdatabases db, #growth g
WHERE s.dletter = LEFT(af.[filename],1)
AND af.dbid = db.dbid
AND db.[name] = ds.dbname
AND g.dbname = db.[name]
AND g.fname = af.[name]



/*****************************************
* Drop temporary tables
*****************************************/
DROP TABLE #space
DROP TABLE #dbsize
DROP TABLE #fdata
DROP TABLE #growth

END

/******************************************************************
*
* End Of SQL Server Disk Space Check
******************************************************************/



/******************************************************************
*
*Check for Last Backup Done.
******************************************************************/


insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'BACKUPDETAIL','DATABASELASTBACKUP', B.name ,ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER')
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

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'BACKUPDETAIL','DAYSSINCELASTBACKUP', B.name ,ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER')
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

/******************************************************************
*
* End Of SQL Server Disk Space Check
******************************************************************/


/******************************************************************
*
* Lists user and permission
******************************************************************/


-- ***************************************************************************
-- Declare local variables
DECLARE @DBName1 VARCHAR(32);
DECLARE @SQLCmd VARCHAR(1024);
-- ***************************************************************************

-- ***************************************************************************
-- Get the SQL Server logins
-- Create Temp User table
CREATE TABLE #Users1 (
[sid] varbinary(85) NULL,
[Login Name] varchar(24) NULL,
[Default Database] varchar(18) NULL,
[Login Type] varchar(9),
[AD Login Type] varchar(8),
[sysadmin] varchar(3),
[securityadmin] varchar(3),
[serveradmin] varchar(3),
[setupadmin] varchar(3),
[processadmin] varchar(3),
[diskadmin] varchar(3),
[dbcreator] varchar(3),
[bulkadmin] varchar(3));
---------------------------------------------------------
INSERT INTO #Users1 SELECT sid,
loginname AS [Login Name],
dbname AS [Default Database],
CASE isntname
WHEN 1 THEN 'AD Login'
ELSE 'SQL Login'
END AS [Login Type],
CASE
WHEN isntgroup = 1 THEN 'AD Group'
WHEN isntuser = 1 THEN 'AD User'
ELSE ''
END AS [AD Login Type],
CASE sysadmin
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [sysadmin],
CASE [securityadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [securityadmin],
CASE [serveradmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [serveradmin],
CASE [setupadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [setupadmin],
CASE [processadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [processadmin],
CASE [diskadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [diskadmin],
CASE [dbcreator]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [dbcreator],
CASE [bulkadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [bulkadmin]
FROM master.dbo.syslogins
Where sysadmin =1 or securityadmin =1 or serveradmin =1 or setupadmin=1 or processadmin = 1
or diskadmin=1 or dbcreator =1 or bulkadmin=1;

---------------------------------------------------------

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SERVERROLES','SYSADMIN', [Login Name],[sysadmin]
FROM #Users1
ORDER BY [Login Type], [AD Login Type], [Login Name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SERVERROLES','SECURITYADMIN', [Login Name], [securityadmin]
FROM #Users1
ORDER BY [Login Type], [AD Login Type], [Login Name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SERVERROLES','SERVERADMIN', [Login Name], [serveradmin]
FROM #Users1
ORDER BY [Login Type], [AD Login Type], [Login Name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SERVERROLES','SETUPADMIN', [Login Name], [setupadmin]
FROM #Users1
ORDER BY [Login Type], [AD Login Type], [Login Name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SERVERROLES','PROCESSADMIN', [Login Name], [processadmin]
FROM #Users1
ORDER BY [Login Type], [AD Login Type], [Login Name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SERVERROLES','DISKADMIN', [Login Name], [diskadmin]
FROM #Users1
ORDER BY [Login Type], [AD Login Type], [Login Name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SERVERROLES','DBCREATOR', [Login Name], [dbcreator]
FROM #Users1
ORDER BY [Login Type], [AD Login Type], [Login Name]

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SERVERROLES','BULKADMIN', [Login Name], [bulkadmin]
FROM #Users1
ORDER BY [Login Type], [AD Login Type], [Login Name]


-- ***************************************************************************
-- ***************************************************************************

-- ***************************************************************************
-- Create the output table for the Database User ID's
CREATE TABLE ##DBUsers1 (
[Database] VARCHAR(64),
[Database User ID] VARCHAR(64),
[Server Login] VARCHAR(64),
[Database Role] VARCHAR(64))
-- ***************************************************************************


-- Declare a cursor to loop through all the databases on the server
DECLARE csrDB CURSOR FOR
SELECT name
FROM master..sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb');
-- ***************************************************************************
-- ***************************************************************************
-- Open the cursor and get the first database name
OPEN csrDB
FETCH NEXT
FROM csrDB
INTO @DBName1
-- ***************************************************************************
-- ***************************************************************************
-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
-- ***************************************************************************
-- ***************************************************************************
--
SELECT @SQLCmd = 'INSERT ##DBUsers1 ' +
' SELECT ''' + @DBName1 + ''' AS [Database],' +
' su.[name] AS [Database User ID], ' +
' COALESCE (u.LoginName, ''** Orphaned **'') AS [Server Login], ' +
' COALESCE (sug.name, ''Public'') AS [Database Role] ' +
' FROM [' + @DBName1 + '].[dbo].[sysusers] su' +
' LEFT OUTER JOIN master.dbo.syslogins u' +
' ON su.sid = u.sid' +
' LEFT OUTER JOIN ([' + @DBName1 + '].[dbo].[sysmembers] sm ' +
' INNER JOIN [' + @DBName1 + '].[dbo].[sysusers] sug ' +
' ON sm.groupuid = sug.uid)' +
' ON su.uid = sm.memberuid ' +
' WHERE su.hasdbaccess = 1' +
' AND su.[name] != ''dbo'' AND u.LoginName is null '
EXEC (@SQLCmd)
-- ***************************************************************************
-- ***************************************************************************
-- Get the next database name
FETCH NEXT
FROM csrDB
INTO @DBName1
-- ***************************************************************************
-- ***************************************************************************
-- End of the cursor loop
END
-- ***************************************************************************
-- ***************************************************************************
-- Close and deallocate the CURSOR
CLOSE csrDB
DEALLOCATE csrDB
-- ***************************************************************************
-- ***************************************************************************

insert into #csv_output
select distinct @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'ORPHANUSER','ORPHANUSER', [Database User ID], [Database]
FROM ##DBUsers1
ORDER BY [Database User ID],[Database];

-- ***************************************************************************
-- ***************************************************************************
DROP TABLE #Users1;

DROP TABLE ##DBUsers1;
-- ***************************************************************************



/******************************************************************
*
* End of Lists user and permission
*
******************************************************************/

/******************************************************************
*
* Failed Scheduled alert jobs logs
*
******************************************************************/




insert into #csv_output
select distinct @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'FAILEDSCHEDULEDJOBS', 'FAILEDSCHEDULEDJOBS',j.name ,cast(DATEADD
( SECOND, jh.run_duration, CAST
(CONVERT(VARCHAR, jh.run_date)
+ ' ' + STUFF(STUFF(RIGHT('000000'
+ CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':')
AS DATETIME
) ) as varchar)
FROM
msdb..sysjobhistory jh
INNER JOIN
msdb..sysjobs j
ON
j.job_id = jh.job_id
WHERE
jh.step_name = '(Job outcome)'
and jh.run_date >= cast(year(getdate() - 1)as varchar) + right('0'+ cast(month(getdate() -1 )as varchar),2) + right('0'+ cast(day(getdate() -1) as varchar),2)
and jh.run_status =0 -- failure

/******************************************************************
*
* End Of SQL Server Disk Space Check
******************************************************************/



/******************************************************************
*
* Lists out the Last 20 Sql error logs with filtering on unimportant mesg
*
******************************************************************/


CREATE TABLE #Errors (vchMessage varchar(500), ID int)
CREATE INDEX idx_msg ON #Errors(ID, vchMessage)

INSERT #Errors EXEC sp_readerrorlog

--This will remove the header from the errolog
SET ROWCOUNT 4
DELETE #Errors
SET ROWCOUNT 20

insert into #csv_output
select @COLLECTIONTYPE, @COLLECTIONSOURCE, @COLLECTIONDATETIME,
'SERVERLOGS','LAST20SERVERLOGS', vchMessage, ''
FROM #Errors
WHERE vchMessage NOT LIKE '%Copyright (c)%'
AND (isdate(substring(vchMessage,1,10)) = 1)
AND substring(vchMessage,1,10) > convert(varchar(10),(dateadd(dd,-1,getdate()-1)),120)
ORDER BY ID DESC


DROP TABLE #Errors
SET ROWCOUNT 0

/******************************************************************
*
*End of Lists out the Last 20 Sql error logs with filtering on unimportant mesg
*
******************************************************************/



/******************************************************************
*
* Writing the script output to csv file
*
******************************************************************/


DECLARE @objFileSystem int
,@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
@Command varchar(1000),
@hr int,
@fileAndPath varchar(80),
@objFile int


set nocount on

select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT



Declare @Filename varchar(60)
--set @Filename = 'DailyHealthcheck_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') + '.html'
set @Filename = 'c:\temp\DailyHealthcheck'

Select @FileAndPath=@filename + '.csv'


if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'

if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @FileAndPath,1,True


if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='writing to the file "'+@FileAndPath+'"'

Declare msgcursor CURSOR FOR
select Replace(COLLECTIONTYPE + ',' + COLLECTIONSOURCE + ',' +
COLLECTIONDATETIME + ',' + COLLECTIONNAME + ',' +
COLLECTIONHEADER + ',' + COLLECTIONVALUE1 + ',' +
COLLECTIONVALUE2,char(13),'') as msg from #csv_output

Declare @String1 varchar(4000)

Open msgcursor
fetch next from msgcursor
into @String1

-- Loop through all records in the cursor
WHILE @@fetch_status = 0
BEGIN
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'WriteLine', Null, @String1

fetch next from msgcursor
into @String1

END

Close msgcursor
Deallocate msgcursor

if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'

if @hr<>0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int

EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
raiserror (@strErrorMessage,16,1)
end
EXECUTE sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream


drop table #csv_output



/******************************************************************
*
* End Of Writing the script output to csv file
******************************************************************/


/******************************************************************
*
* Compressing the report
******************************************************************/

declare @SQLCommand varchar(200)

SET @SQLCommand = 'exec master..xp_cmdshell ' + '''' + 'makecab ' + @Filename + '.csv ' + @Filename + '.cab'''

EXEC (@SQLCommand)

/******************************************************************
*
* Compressing the report
******************************************************************/


/******************************************************************
*
* Sending the health check report to email
******************************************************************/
set @Filename = @Filename + '.cab'

declare @rc int
exec @rc = master.dbo.xp_smtp_sendmail
@from = @charfrom,
@to = N'xxx@mail.com',
@server = @charemailserver,
@subject = @charsubject ,
@attachment = @Filename ,
@message = N'See Attached report '


/******************************************************************
*
* End Of Sending the health check report to email
******************************************************************/

GO