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:

Experienced Personal Assistant

Seeking an experienced (Or willing to learn fast) personal assistant with a successful proven tack record. Preferably with a degree Excellent, written, verbal,English Great communicator, Hard worker, efficient in a fast paced environment. Great analysis, organisational, management skills...

  • Fixed Price Project
  • Max $100
  • 1 year, 8 months left

Czech Republic - Do YOU Want To Manage Our Team?

We have just opened the Czech Republic and need a dedicated Sales Team Leader to ensure we build fast in this Country. Our products are selling fast around the World and are consumed 75% of the Worlds population on a daily basis...

  • Fixed Price Project
  • Max $1500
  • 1 month, 3 weeks left

Call Center Wanted

Summary = Online Education is a live transfer process matching interested customers that match the criteria with the correct educational provider. This is not sales. Short script, gain customer's permission, and transfer the call...

  • Fixed Price Project
  • Max $10000
  • 2 months, 1 week left
  • Fixed Price Project
  • Max $50
  • 1 year left

Agent For Investment Immigration To The USA By Visa EB-5

Hello, I believe you should have clients interested to immigrate to the USA. Please see below information regarding Investment Immigration. More information located on our www.AGCCRC.com & www.Business-VISA-USA.com. The easiest way to immigrate to the USA - is Investment visa EB-5...

  • Fixed Price Project
  • Max $25000
  • 3 years, 1 month 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 experience as well and explain in brief how and what you do to win these contracts over.

  • Fixed Price Project
  • Max $500
  • 3 years, 3 months left

Reputation Management Expert Wanted

We need a Reputation Management Expert to push down a negative article off of page 1 and page 2 of Google. Only experts needed who know what they are doing and can guarantee the results in a stipulated time period, and then maintain those results permanently through an on-going relationship. Tha

  • Fixed Price Project
  • Max $50
  • 1 week, 5 days left

Customer Service Rep CSR

We are looking for a self-motivated person to set appointments for free estimates. visit our website at http://jpcomputerbiz.wix.com/jpbiz We are looking for a self-motivated person to set appointments for free estimates. We have a family-like atmosphere and a friendly work environment...

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

Business Opportunity - Distributorships & Retail

http://sexytastycoffee.organogold.com/ Products Beverages Nutraceuticals Personal Care Organo Gold blends the unequalled power of Ganoderma with an unparalleled business opportunity that will allow you to capitalize in the ever expanding health and wellness industry...

  • Fixed Price Project
  • Max $5
  • 4 years, 6 months 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, 9 months left

Job Details

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