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:

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

Are YOU Ready Australia / New Zealand?

After months of preparation and setting up we are soft Launched in Australia and New Zealand and we need dedicated Sales people to take us to the top in this area. We have pre launch Events in Melbourne / Hobart / Adelaide / Perth / Brisbane / Auckland / Wellington and Sydney over the next 2 weeks..

Skills Required: sales-management
  • Fixed Price Project
  • Max $1500
  • 9 months, 3 weeks 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, 1 month left

Full Time Philippines Based Joomla, Php, HTML, CSS Website And , Apple/Android Apps Project Coordinator

Looking for a full time website / app project coordinator with the following experience from Philippines - Understanding of Joomla, Php, CSS and HTML website development - Understanding of Iphone/Andriod app development - Good communication skills in English - Ability to coordinate with offshore...

  • Fixed Price Project
  • Max $400
  • 3 months left

**Technical** Project Manager - Senior Technical Level Person

Company: Small Software company uses freelance developers - SharePoint App development. Your job - THIS IS A RECURRING JOB - it does not have a finite end date. Ongoing business. The budget below is not indicative of compensation. This is to be negotiated...

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

Commission Based Sales People Needed For Established Web Design And Internet Services Company

Ice Cube Web Design is an established and highly regarded web design and development company. We specialize in super high quality responsive web design, search engine optimization and search engine marketing. We are looking for international sales people to join our team...

  • Fixed Price Project
  • Max $20000
  • 1 month left

Forex Introducing Broker/Affiliate

We have set up a website www.stocksoptionsforex.com to promote some of our best algorithmic trading solutions. Please see the ones available for forex here http://www.stocksoptionsforex.com/managed-forex-account. We also offer a partnership program with various brokers including hotforex...

  • Fixed Price Project
  • Max $5
  • 3 days, 12 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, 10 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