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:

Urgent - We Need A Team Leader In Thailand

We have just opened Thailand 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...

Skills Required: sales-management
  • Fixed Price Project
  • Max $1500
  • 1 month, 4 weeks left

Urgent - We Need A Team Leader For The Philippines

We are open in The Philippines 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 by 75% of the Worlds population on a daily basis...

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

Looking For Sales/Lead Generator For Florida Marketing Company

I Run a Local Consulting Agency In Florida. I'm Looking For Experienced and WELL SPOKEN B2B Salesperson to do the following : 1. Record The Company Voice Mail 2. Cold Call Local Businesses Offering OUR Services 3...

  • Fixed Price Project
  • Max $500
  • 1 month, 2 weeks left

Website Publisher For UK Hindi/Punjabi Media Website

My client is looking for someone to handle day to day running of their website. This involves:- Publishing press releases with appropriate images Publishing content to facebook, twitter and google+ Publishing news from 3rd party sources to website Publishing news from website writers...

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

Wordpress Expert!

The company is in need of someone who has commitment in making big projects together with the other people on the team. He/she will be responsible in all aspects of the website's back-end...

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

Recruiting IT Guy With Business Background (FULL TIME)

this is a long shot but ideally, we would be looking for someone with following background (for full time) 1. business / accounting 2. IT (system support, basic software management, etc) 3. good command of communication in English and Thai we could consider Thai or foreigners and could provi

  • Fixed Price Project
  • Max $1000
  • 2 months left

Website Designer/Developer

*** This contract is fix price per, Various projects available*** I need a Website Designer/Developer to task some tasks. Right now I have 2 sites to complete. One for services and another for product selling. Requirements: * Knowledge with Photoshop & Dreamweaver...

  • Fixed Price Project
  • Max $20
  • 1 year, 2 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, 11 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