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:

Event Coordinators

We are looking for event coordinators around the world. You shud have the following skills 1. Ability to bring atleast 100 viewers 2. Ability to bring atleast 10 demonstrators with talents 3. Ability to organise events live in the web and also in the public place 4. Excellent communication skill 5.

  • Fixed Price Project
  • Max $50
  • 2 years, 4 months left

Agent For Investment Immigration To The USA By Visa EB-5

Hello, I believe you should have clients interested to immigrate to the USA. Please see below information regarding Investment Immigration. More information located on our www.AGCCRC.com & www.Business-VISA-USA.com. The easiest way to immigrate to the USA - is Investment visa EB-5...

  • Fixed Price Project
  • Max $25000
  • 3 years, 5 months left

Experienced "

* * * * * * * Multi-skilled English speaking, Web savvy, Virtual Assistant WANTED to join our unique company (managerial and key position)* * * * * * * Please read this description carefully before applying to ensure you meet all the requirements...

  • Fixed Price Project
  • Max $500
  • 19 hours, 9 minutes left

Creative People Needed With Good Knowledge Of Image Search & Creative Writing

I need people to make lists of '10 best things' and include pictures with related text. Job is fairly simple & the topics will be varied from Entertainment, Hollywood, Humor, Relationships, Facts and more. One 'File' will include 10 images & 10 write-ups (one for each image

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

Lotto POS Terminal Mobile Apps

Hello, We are independent IT contractors. We have a client who runs a lotto company and need the lotto POS terminal application to be designed and developed to desktop and mobile apps that must be compatible with the latest androids and smart phones iOS...

  • Fixed Price Project
  • Max $5000
  • 3 weeks, 5 days 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
  • 2 months left

Writers Needed 6pm To 12 Midnight (Mountain Time Zone, U.S.A.)

I represent a website, blog, and article content company. We need more English writers fast! We have a very efficient content development service -- so please read all of this job description carefully...

  • Fixed Price Project
  • Max $500
  • 3 weeks, 4 days left

Chief Of Processing & Compliance

SALARY $350 per Month, 8 Hours Per Day Manage Compliance Staff Communicate and disseminate essential policies or regulatory changes to senior management and the relevant business units and provide guidance on how the requirements could be met...

  • Fixed Price Project
  • Max $350
  • 2 months, 3 weeks 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

Job Details

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