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:

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
  • 3 months, 3 weeks left

CSV - FTP Management Updates, And Integration For Yahoo Store

I have a yahoo store and my supplier provides daily CSV and FTP product updates. However, they have a standard CSV file that needs to be edited for yahoo integration. I'm looking for someone who can create a program or service to convert and auto update my site daily. I need a super star! I wo

  • Fixed Price Project
  • Max $250
  • 5 months, 1 week left

Hiring Harvard Writer In 2 Hours Time

Hello writers, We are searching for a number of writers on FINANCE and MANAGEMENT to join our team of writers. Do not apply if you do not know anything to do with this style. Academic writing, proposals, Press release and research would be of added advantage...

Skills Required: research finance management
  • Fixed Price Project
  • Max $500
  • 5 months, 2 weeks left

24/7 Content Manager For Sports News Site

I have a sports website with automated content. I need someone who KNOWS SPORTS to update my site on a daily basis (tag articles by sport and publish to feed and also set featured articles for display at top of page)...

  • Fixed Price Project
  • Max $3500
  • 2 months, 1 week left

We Need You To Sell Our Amazing Products!

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

Skills Required: sales-management
  • Fixed Price Project
  • Max $200
  • 8 months 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
  • 9 months left

Operation Manager

This is a street sweeping company wit a small plant in Summerville SC, most of the work is at night, need be an honest and self motivated person that take care of 5 trucks, 4 drivers and 1 mechanic; must keep fleet running and supervise all the work. Must have a CDL Driver License, knowledge of truc

Skills Required: property-management
  • Fixed Price Project
  • Max $40000
  • 6 days, 20 hours left

SEO Content Writer

Our team is in need of a Junior level SEO Writer. The project will start as a part time offer and based upon your skills, this could lead to a full time position. Ideal candidate must have at least 1 year of SEO writing experience...

  • Fixed Price Project
  • Max $300
  • 6 days, 18 hours 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, 11 months left

Brand Partners Overseas

My company will be going international in 2014. I am looking for partners in every country we will be in...

  • Fixed Price Project
  • Max $5
  • 2 months, 1 week left

Job Details

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