Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««7891011»»»

SQL Server System Report Expand / Collapse
Author
Message
Posted Friday, April 05, 2013 4:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 9:34 AM
Points: 14, Visits: 219
To Sean Smith,
Reg: usp_SQL_Server_System_Report

First off, Thank you for this script. It has made my life a lot easier.

I was woundering if you would be able to modify the script to use mounting points for the disk drive info? I am not the greatest tSql developer and I have had the hardest time trying to modify your script. I have tried to modify it using the following code as a starting point.


declare @svrName varchar(255)
declare @sql varchar(400)
--by default it will take the current server name, we can the set the server name as well
set @svrName = @@SERVERNAME
set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'
--creating a temporary table
CREATE TABLE #output
(line varchar(255))
--inserting disk name, total space and free space value in to temporary table
insert #output
EXEC xp_cmdshell @sql
--script to retrieve the values in MB from PS Script output
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)'
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)'
from #output
where line like '[A-Z][:]%'
order by drivename
--script to retrieve the values in GB from PS Script output
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)'
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'
from #output
where line like '[A-Z][:]%'
order by drivename
--script to drop the temporary table
drop table #output


Please let me if this is something you can work on.

JM
Post #1439523
Posted Friday, April 12, 2013 4:44 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 2:54 PM
Points: 314, Visits: 583
Hi there.

I am glad that the script has helped you out (I actually have plans for expanding it soon-ish).

Unfortunately, I know nothing about PowerShell, so I won't be able to help you out. Have you tried posting this question on other forums to see if an expert can assist you?
Post #1441965
Posted Friday, April 12, 2013 5:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 9:34 AM
Points: 14, Visits: 219
No, I have not asked. I am new to powershell as well. I am in no rush.
Post #1441969
Posted Friday, April 12, 2013 5:58 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 2:54 PM
Points: 314, Visits: 583
Okay. Best suggestion then is to try some PowerShell user groups / sites. If you find the answer feel free to share it. :)
Post #1441981
Posted Thursday, August 15, 2013 9:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 20, 2014 10:16 AM
Points: 20, Visits: 73
This is a great script, thanks for sharing it. I made a few little tweaks that others might find useful.

---I made @vRecipients a parameter that can be passed at the time of execution:

CREATE PROCEDURE [dbo].[usp_SQL_Server_System_Report]

@v_Output_Mode CHAR (1) = NULL
,@vUnused_Index_Days INT = 7
,@vRecipients AS NVARCHAR(128) = NULL

AS

.....

---and then commented out the declaration for it
DECLARE @vOnline_Since AS NVARCHAR (19)
-- DECLARE @vRecipients AS VARCHAR (MAX)
DECLARE @vSQL_String AS NVARCHAR (MAX)

.....

---also, I modified the part where the email is sent, so if there are multiple email profiles and/or no default mail profile, it will still work.
IF @v_Output_Mode = 'E'
BEGIN
DECLARE @profname sysname

SELECT @profname = name
FROM msdb.dbo.sysmail_profile
WHERE profile_id in (SELECT min(profile_id) FROM msdb.dbo.sysmail_profile)

EXECUTE [msdb].[dbo].[sp_send_dbmail]

@profile_name = @profname
,@recipients = @vRecipients
,@copy_recipients = @vCopy_Recipients
,@subject = @vSubject
,@body = @vBody
,@body_format = 'HTML'

END



The changes I made allow the report to be sent to anyone on the dba team.

Hope these mods are helpful to others.
Post #1484807
Posted Thursday, August 15, 2013 9:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 9:34 AM
Points: 14, Visits: 219
Can you post the entire code vs. bit and pieces?

Thank you
Post #1484833
Posted Thursday, August 15, 2013 10:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 20, 2014 10:16 AM
Points: 20, Visits: 73
Sure Juan, please see attached.

  Post Attachments 
SQL_Server_Report.txt (14 views, 36.44 KB)
Post #1484844
Posted Thursday, August 15, 2013 12:17 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 2:54 PM
Points: 314, Visits: 583
This is fantastic! Thanks for sharing. I am actually in the process of updating / adding to the code. Would you mind if I integrate your additions (officially) to it?
Post #1484878
Posted Thursday, August 15, 2013 12:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 9:34 AM
Points: 14, Visits: 219
If you could update the Drive space code with the MP code listed in an earlier post.
That would be great.

JM
Post #1484883
Posted Thursday, August 15, 2013 12:30 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:19 AM
Points: 49, Visits: 319
I would actually like to see the option to have this information saved to a AdminDB table.

SQL_Padre
aka Robert M Bishop

"Do or do not, there is no try" -- Yoda
Post #1484886
« Prev Topic | Next Topic »

Add to briefcase «««7891011»»»

Permissions Expand / Collapse