My Work Blog - Oracle and SQL server

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

My Work Blog - Oracle and SQL server

Tuesday, August 19, 2008

Changing SQL Server Network Connetivity Protocol priority using registry

Tuesday, July 29, 2008

Favicon's in a SharePoint 2007

Sunday, July 20, 2008

Problem :Communication from the Oracle Management Service host to the Agent host failed.

Thursday, July 10, 2008

Discovering IAS failed if the agent is install under another OS user than ias

Wednesday, July 02, 2008

Caught by SQL injection

Thursday, June 26, 2008

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

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

My Blog List

Blog Archive

About Me

Labels