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:

Graphic Design Expert

We are looking for a graphic designer who can be proficient in Microsoft Word and Publisher . The Designer will have demonstrated strengths in graphics design, project management, and communication...

  • Fixed Price Project
  • Max $5
  • 4 years, 6 months left
  • Fixed Price Project
  • Max $50
  • 5 months, 3 weeks left

Help Us Win More Contracts

We are in search of someone with skills to win over contracts. We need to increase our contracts. If you have the skills, please submit your cover letter, state you experiance as well and explain in brief how and what you do to win these contracts over.

  • Fixed Price Project
  • Max $500
  • 2 years, 9 months left

German Speaking Project Manager In Vietnam

Hi, we are looking for a german speaking project manager in vietnam to work at the location of our software development team in vietnam. Your job will be to clarify tasks with customers and discuss directly with the team to get the software development jobs done. Please send your CV to us. Thanks T

Skills Required: german project-management
  • Fixed Price Project
  • Max $800
  • 1 year, 5 months left

Versatile Web Developer - Management Candidat

You are a truly versatile, motivated INDIAN Web Developer with excellent skills in Joomla, JomSocial, VirtueMart and similar Joomla extentions, preferably residing in CHENNAI or willing to relocate in the future there? Then we ascend not only an interesting, but long-term task with the ability to..

  • Fixed Price Project
  • Max $5
  • 5 years, 5 months left

Senior ColdFusion Developer

AREAS OF EXPOSURE RDBMS : SQL2000, ORACLE 10,11g DBMS : Microsoft Access, Mysql, and FoxPro GUI : VB...

Skills Required: coldfusion oracle-database
  • Fixed Price Project
  • Max $24000
  • 3 weeks, 4 days left

Are You A Sales Professional, Then We Must Talk?

Our products are now being recognised as a replacement and we want Top Sales Professionals to make your Country more aware of the Science behind our Product Range...

  • Fixed Price Project
  • Max $200
  • 2 weeks, 3 days left

Credit Card Payment Processor Needed

We are a company outsourced by international Client that needs payment processing for her online shop. Volume is high with a minimum of $50,000 and maximum of $400,000 monthly...

  • Fixed Price Project
  • Max $1500
  • 1 year left

SQL/Dextrity Programmer

Microsoft Dynamics Great Plains installation and support knowledge. Troubleshooting knowledge in Dextrity programming SQL Programing is a must SSRS (SQL server Reporting service) Implementation knowledge Crystal report knowledge Great Plains 2013 Web Client implementation...

  • Fixed Price Project
  • Max $4000
  • 5 years, 3 months left

Long-term Reliable Academic Writers Needed

PLEASE ONLY APPLY IF YOU ARE SERIOUS AND RESPONSIBLE ABOUT WORKING ONLINE AS AN ACADEMIC WRITER! THANKS!! We are looking for a lot of good writers to work with us in the long term...

  • Fixed Price Project
  • Max $60
  • 9 months, 4 weeks left

Job Details

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