September 5, 2012 at 10:20 am
Fantastic. Hope you enjoy some of the new features of this version. 🙂
September 12, 2012 at 1:24 am
Hi Sean,
U have done a fantastic job designing a script which will give entire server report. But , is there any possibility, to save the report to local disk in the form of some html report?
September 12, 2012 at 5:29 am
There are various methods of saving output to a file. Unfortunately it is too long to go into detail here, but if you Google "SQL Server saving results to a file" you should be able to find some good articles to help you out. I've noted the idea as it is a good one and I will likely add it as an option when I do my next update of the code to allow for saving of the data to HTML, text, CSV, etc.
September 12, 2012 at 10:11 am
Not sure if this meets your requirement but if you choose the option to send the report by email the recipient can then save it manually.
September 12, 2012 at 10:56 am
Actually, that is a decent workaround for now. 🙂
April 5, 2013 at 4:18 pm
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
April 12, 2013 at 4:44 pm
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?
April 12, 2013 at 5:11 pm
No, I have not asked. I am new to powershell as well. I am in no rush.
April 12, 2013 at 5:58 pm
Okay. Best suggestion then is to try some PowerShell user groups / sites. If you find the answer feel free to share it. 🙂
August 15, 2013 at 9:11 am
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
FROMmsdb.dbo.sysmail_profile
WHEREprofile_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.
August 15, 2013 at 9:53 am
Can you post the entire code vs. bit and pieces?
Thank you
August 15, 2013 at 10:33 am
Sure Juan, please see attached.
August 15, 2013 at 12:17 pm
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?
August 15, 2013 at 12:23 pm
If you could update the Drive space code with the MP code listed in an earlier post.
That would be great.
JM
August 15, 2013 at 12:30 pm
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
Viewing 15 posts - 76 through 90 (of 189 total)
You must be logged in to reply to this topic. Login to reply