SSRS Database Management Reports

I need the following scripts listed below modified. There are four reports. The first report is the inventory report. The additional information I need to see in the inventoyr report is the application name which is captured in the Database Growth report listed below. The second report is the backup history report, which needs to show all jobs that executed with success and executed and failed and the report should be driven by date range so I should be able to query between any dates for this report( example: March 1 2012 to March 20 2012). The third report is the SQL Server Security Report which needs to include the servername and SQL Instance name. Currently it does not show the server name and SQL Instance name.

------1-----SQL Server 2005/2008 Database inventory report - will display server name, sql server version, user database name, date of creation------------

SELECT @@SERVERNAME AS ServerName, @@VERSION AS SQL_Server_Version, name AS DataBase_Name, crdate AS Created_Date
FROM sys.sysdatabases

------2--------SQL Server 2005/2008 Backup History report - will be parameter driven by date range and parameter driven job status such as failed or success. -----------------

SELECT
A.[Server],
A.last_db_backup_date,
B.backup_start_date,
B.expiration_date,
B.backup_size,
B.logical_device_name,
B.physical_device_name,
B.backupset_name,
B.description
FROM
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
) AS A

LEFT JOIN

(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
) AS B
ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY
A.database_name



-------3--------------SQL Server 2005/2008 Security Report (Report showing all users across all database servers that have sysadmin role)-------------

SELECT s.name,IS_SRVROLEMEMBER('sysadmin', p.name) AS IsSysAdmin FROM sys.server_principals p
right join sys.sysdatabases s on s.sid=p.sid where IS_SRVROLEMEMBER('sysadmin', p.name)=1

----4----SQL Server 2005/2008 Database Growth.-----------------------------
DECLARE @path NVARCHAR(1000)
SELECT @path = Substring(PATH, 1, Len(PATH) - Charindex('', Reverse(PATH))) +
'log.trc'
FROM sys.traces
WHERE id = 1
SELECT DatabaseName,
e.name AS eventname,
cat.name AS [CategoryName],
starttime,
e.category_id,
loginname,
loginsid,
spid,
hostname,
applicationname,
servername,
textdata,
objectname,
eventclass,
eventsubclass
FROM ::fn_trace_gettable(@path, 0)
INNER JOIN sys.trace_events e
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS cat
ON e.category_id = cat.category_id
WHERE e.name IN( 'Data File Auto Grow', 'Log File Auto Grow' )
ORDER BY starttime DESC

Search All Jobs:

Job Details

  • Job Type
  • Fixed Price Job
  • Budget
  • Max $20
  • Start Date
  • 04/17/2012 02:43:07 PM
  • Job Status
  • Job Expired