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:

Content Writer

Experienced Content Writer; Scope 10 is seeking an experienced Web Content Writer who will be responsible for creating web-ready optimized content for multiple client websites. The content writer will also be responsible for creating the title tags and meta descriptions for content written...

  • Fixed Price Project
  • Max $10
  • 8 months left

Australia Is Now Open

We need a strong Sales Force for Australia and New Zealand. You must be a Leader and ready to build fast with a product the World uses every day, many times a day (but our product is unique and people love it)...

Skills Required: sales-management
  • Fixed Price Project
  • Max $1500
  • 10 months, 3 weeks left

LiveAuctionZone.com Sign Up Fees And Sales

Live Auction Zone is now looking for sale reps and sign up reps. All Sign up fees will be a percent of sign up sales. We Will Pay 50% For All Sign up fees and you many sign up your own sales reps and receive 25% For All Sign up fees and built a unlimited sale reps under you...

  • Fixed Price Project
  • Max $50000
  • 1 year, 4 months left

Self Employed Sales Manager - Fantastic Growth Potential (UK Citizens And Commission Only)

Please see attached job and person specification. The ideal candidate will have a graphic design background or existing contacts within hotel and leisure, bars and restaurants, interior design, corporate business, etc...

  • Fixed Price Project
  • Max $5
  • 1 year, 4 months left

Customer Service, Chat, Email, Support Expert

Need someone long term who can help manage email and my calendar. Ideal candidate will be knowledgeable with google/gmail applications. There is room for growth within our company...

  • Fixed Price Project
  • Max $5
  • 3 weeks, 6 days 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
  • 6 years, 1 month left

Need Teachers/home School Parents/educational Sales Person!

We are looking for sales person in United states who can help find target customers for our early preparation SAT quiz class on hifikids.com! You must be residence of United states and currently working or looking for part time sales job...

  • Fixed Price Project
  • Max $100
  • 4 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
  • 1 year, 8 months left

Customer Service, Chat, Email, Support Expert

Need someone long term who can help manage email and my calendar. Ideal candidate will be knowledgeable with google/gmail applications. There is room for growth within our company...

  • Fixed Price Project
  • Max $5
  • 3 weeks, 6 days left

Wanted Call & Contact Center Across Albania-Romania-Moldavia-Ukraina & North Africa

WANTED CALL CENTER AND / OR OPERATORS OF THE ITALIAN LANGUAGE FOR CONDUCT OF TELE-MARKETING CAMPAIGN. RETAIL WILL TAKE PLACE AN OUTLET FOR APPOINTMENT OF AGENTS ENEL GREEN POWER ON THE MARKET RESIDENTIAL. FOR MORE INFO PLEASE CONTACT ME TO MY DIRECT CONTACT (EMAIL OR CELL)...

  • Fixed Price Project
  • Max $15
  • 4 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