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:

Ab Initio Consultant

Job Title: Ab Initio Consultant (Multiple Positions) Location: NJ/FL/TX Duration: Full-time Job Requirements: "

  • Fixed Price Project
  • Max $100
  • 4 years, 4 months left

RE:make My Hosing Account Successful

I have a hosting account that sales hosting and I need it to be successful I need someone who knows how to do web editing drive traffic and tah ke care of mailing list if you are ok with these thing Dont apply it will be a waste of time for us both I need someone who knows what there doing and can.

  • Fixed Price Project
  • Max $75
  • 1 year, 3 months left

Lead Generation And Outbound Telemarketing Service

Hello. I m CEO in eBizzers International Ltd, Uk based company works in Cloud softwares. We produce web based services based on Hosted and ready made softwares in Ecommerce, Booking Engine, Help Desk solutions...

  • Fixed Price Project
  • Max $25
  • 4 years, 5 months left
  • Fixed Price Project
  • Max $80
  • 2 years, 6 months left

APA, Chicago, Harvard, MLA Styles

Hello writers, READ THIS CAREFULLY AS WE WANT A NUMBER OF WRITERS AND THOSE WHO FOLLOW INSTRUCTIONS. We are in search of a number of ACADEMIC writers on: 1. MEDICAL AND HEALTH 2. Technology 3. Project Management 4. English 5. Engineering 6. Finance 7. Thesis/Dissertation Proposal 8. Essay 9...

  • Fixed Price Project
  • Max $1000
  • 1 year, 5 months 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, 7 months left

Reliable Academic Writers Needed

We are looking for a lot of good writers to work with us in the long term...

  • Fixed Price Project
  • Max $60
  • 7 months, 3 weeks 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
  • 7 months, 4 weeks left

Looking For Freelance Sales Professionals

YourSales is looking for freelance sales professionals worldwide for a range of missions we carry out for clients...

  • Fixed Price Project
  • Max $50000
  • 8 years, 3 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, 1 month left

Job Details

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