December 20, 2011 at 8:43 am
jberg-604007 (12/19/2011)
line 455 is missing:$results5 |convertto-html -Title
it should read:
$results5 |convertto-html -Title "Backup Status" -Head $a -Body "<H2> ERRORSoutLIST </H2>" | out-file $workingdir\errors.html
Thanks for that jberg. Not sure how that got cut out of the script...
December 20, 2011 at 8:52 am
I did this already. It still does not work. I will put it down for a little while and come back to it later.
December 20, 2011 at 11:04 am
NJ-DBA (12/20/2011)
I seem to recall some difficulty getting dataset results formatted properly, but Im not 100pct sure.
As it is, no doubt its a great solution. However, I believe that datasets might be a better choice. You can use Chad's Invoke-SQLCMD2 found here. Use -As DataTable switch.
gallery.technet.microsoft.com/scriptcenter/7985b7ef-ed89-4dfd-b02a-433cc4e30894
December 20, 2011 at 2:45 pm
I'm getting a buttload of errors. Any help?? I'm running SQL 2008R2 on a Windows Server 2008 VM. Table is located and I'm running SP's from my dbadmin database. * I already changed the references to/from msdb.xxx. I also changed the <<$results5 |convertto-html -Title>> line.
PS C:\Users\jdjohnsoadmin> D:\PowerShell\HTMLReports\working\htmlreports.ps1
You cannot call a method on a null-valued expression.
At D:\PowerShell\HTMLReports\working\htmlreports.ps1:79 char:28
+ $line = $line.TrimEnd <<<< ();
+ CategoryInfo : InvalidOperation: (TrimEnd:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
You cannot call a method on a null-valued expression.
At D:\PowerShell\HTMLReports\working\htmlreports.ps1:79 char:28
+ $line = $line.TrimEnd <<<< ();
+ CategoryInfo : InvalidOperation: (TrimEnd:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
FAILED TO CONNECT TO INSTANCE XXXXXXSDB001PV1\I1
FAILED TO CONNECT TO INSTANCE XXXXXXSDB001PV2\I2
FAILED TO CONNECT TO INSTANCE XXXXXXWDB001PV1\I1
FAILED TO CONNECT TO INSTANCE XXXXXXWDB001PV2\I2
The term 'THIS' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelli
ng of the name, or if a path was included, verify that the path is correct and try again.
At D:\PowerShell\HTMLReports\working\htmlreports.ps1:401 char:5
+ THIS <<<< BLOCK CALLS THE BATCH FILE TO GET THE ERRORLOGS FOR EACH SERVER
+ CategoryInfo : ObjectNotFound: (THIS:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
The term './parseerrorlog.bat' is not recognized as the name of a cmdlet, function, script file, or operable program. C
heck the spelling of the name, or if a path was included, verify that the path is correct and try again.
At D:\PowerShell\HTMLReports\working\htmlreports.ps1:407 char:20
+ ./parseerrorlog.bat <<<< $instance
+ CategoryInfo : ObjectNotFound: (./parseerrorlog.bat:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
The term './parseerrorlog.bat' is not recognized as the name of a cmdlet, function, script file, or operable program. C
heck the spelling of the name, or if a path was included, verify that the path is correct and try again.
At D:\PowerShell\HTMLReports\working\htmlreports.ps1:407 char:20
+ ./parseerrorlog.bat <<<< $instance
+ CategoryInfo : ObjectNotFound: (./parseerrorlog.bat:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
The term './parseerrorlog.bat' is not recognized as the name of a cmdlet, function, script file, or operable program. C
heck the spelling of the name, or if a path was included, verify that the path is correct and try again.
At D:\PowerShell\HTMLReports\working\htmlreports.ps1:407 char:20
+ ./parseerrorlog.bat <<<< $instance
+ CategoryInfo : ObjectNotFound: (./parseerrorlog.bat:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
The term './parseerrorlog.bat' is not recognized as the name of a cmdlet, function, script file, or operable program. C
heck the spelling of the name, or if a path was included, verify that the path is correct and try again.
At D:\PowerShell\HTMLReports\working\htmlreports.ps1:407 char:20
+ ./parseerrorlog.bat <<<< $instance
+ CategoryInfo : ObjectNotFound: (./parseerrorlog.bat:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
Import-Csv : Cannot open file "D:\PowerShell\HTMLReports\errorlogcombined.txt".
At D:\PowerShell\HTMLReports\working\htmlreports.ps1:448 char:11
+ import-csv <<<< $workingdir\errorlogcombined.txt -delimiter "|" |export-csv $workingdir\Errorlogs-past72hrs.csv
+ CategoryInfo : OpenError: (:) [Import-Csv], FileNotFoundException
+ FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ImportCsvCommand
Remove-Item : Cannot find path 'D:\PowerShell\HTMLReports\errorlogcombined.txt' because it does not exist.
At D:\PowerShell\HTMLReports\working\htmlreports.ps1:454 char:12
+ remove-item <<<< $workingdir\errorlogcombined.txt
+ CategoryInfo : ObjectNotFound: (D:\PowerShell\H...logcombined.txt:String) [Remove-Item], ItemNotFoundEx
ception
+ FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.RemoveItemCommand
Get-Item : Cannot find path 'C:\Users\jdjohnsoadmin\backupreport.html' because it does not exist.
At D:\PowerShell\HTMLReports\working\htmlreports.ps1:475 char:20
+ $fileObj = get-item <<<< $fileName
+ CategoryInfo : ObjectNotFound: (C:\Users\jdjohnsoadmin\backupreport.html:String) [Get-Item], ItemNotFou
ndException
+ FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetItemCommand
You cannot call a method on a null-valued expression.
At D:\PowerShell\HTMLReports\working\htmlreports.ps1:485 char:34
+ $nameOnly = $fileObj.Name.Replace <<<< ( $fileObj.Extension,'')
+ CategoryInfo : InvalidOperation: (Replace:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
You cannot call a method on a null-valued expression.
At D:\PowerShell\HTMLReports\working\htmlreports.ps1:510 char:37
+ $nameOnly = $fileObj.Name.Replace <<<< ( $fileObj.Extension,'')
+ CategoryInfo : InvalidOperation: (Replace:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
Rename-Item : Cannot rename because item at 'backupreport.html' does not exist.
At D:\PowerShell\HTMLReports\working\htmlreports.ps1:512 char:15
+ rename-item <<<< "$fileName" "$nameOnly-$DateStamp$extOnly"
+ CategoryInfo : InvalidOperation: (:) [Rename-Item], PSInvalidOperationException
+ FullyQualifiedErrorId : InvalidOperation,Microsoft.PowerShell.Commands.RenameItemCommand
Get-Item : Cannot find path 'C:\Users\jdjohnsoadmin\Errorlogs-past72hrs.csv' because it does not exist.
At D:\PowerShell\HTMLReports\working\htmlreports.ps1:475 char:20
+ $fileObj = get-item <<<< $fileName
+ CategoryInfo : ObjectNotFound: (C:\Users\jdjohn...s-past72hrs.csv:String) [Get-Item], ItemNotFoundExcep
tion
+ FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetItemCommand
You cannot call a method on a null-valued expression.
At D:\PowerShell\HTMLReports\working\htmlreports.ps1:485 char:34
+ $nameOnly = $fileObj.Name.Replace <<<< ( $fileObj.Extension,'')
+ CategoryInfo : InvalidOperation: (Replace:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
You cannot call a method on a null-valued expression.
At D:\PowerShell\HTMLReports\working\htmlreports.ps1:510 char:37
+ $nameOnly = $fileObj.Name.Replace <<<< ( $fileObj.Extension,'')
+ CategoryInfo : InvalidOperation: (Replace:String) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
Rename-Item : Cannot rename because item at 'Errorlogs-past72hrs.csv' does not exist.
At D:\PowerShell\HTMLReports\working\htmlreports.ps1:512 char:15
+ rename-item <<<< "$fileName" "$nameOnly-$DateStamp$extOnly"
+ CategoryInfo : InvalidOperation: (:) [Rename-Item], PSInvalidOperationException
+ FullyQualifiedErrorId : InvalidOperation,Microsoft.PowerShell.Commands.RenameItemCommand
PS C:\Users\jdjohnsoadmin>
December 20, 2011 at 11:38 pm
Really good article Warren!
I, like you, have recently discovered the joys of Powershell and how it can make the life of a DBA easier by automating daily checks. I've gone a different route to you by using the SMO class library as opposed to executing queries. If you mix this up by using the WMI object class library too, you can retrieve a wealth of information about the SQL instance and the server it sits on.
And the bonus is - it is so easy to pick up! I've had some scripting experience before, but PowerShell makes it nice and easy (and doesn't fall over on the first error it comes across which is always nice!). I think you've given DBA's a fantastic head start on discovering just how much more simple you can make life, allowing you to focus your time and efforts on performance tuning and troubleshooting your SQL estate.
Good stuff!
December 21, 2011 at 5:56 am
Phil Melling (12/20/2011)
Really good article Warren!I, like you, have recently discovered the joys of Powershell and how it can make the life of a DBA easier by automating daily checks. I've gone a different route to you by using the SMO class library as opposed to executing queries. If you mix this up by using the WMI object class library too, you can retrieve a wealth of information about the SQL instance and the server it sits on.
And the bonus is - it is so easy to pick up! I've had some scripting experience before, but PowerShell makes it nice and easy (and doesn't fall over on the first error it comes across which is always nice!). I think you've given DBA's a fantastic head start on discovering just how much more simple you can make life, allowing you to focus your time and efforts on performance tuning and troubleshooting your SQL estate.
Good stuff!
Thank you for the compliment. Have played with the SMO class lib a bit and am using WMI to compile a similar report on the event logs.... Perhaps a future artile in that.
December 21, 2011 at 3:55 pm
As below 😉
December 21, 2011 at 3:58 pm
skron (12/19/2011)
Attached is a zip file with the scripts that I use.
Hey had a go with these but im getting these errors, Any ideas? Thanks for your help with this, its going to be very useful.
PhysicalServer = WN###
ServerType = P
Instance = Default
Get-WmiObject : Invalid query
At C:\PSScripts\WIP\DailyChecks\DailyChecklist_Production.ps1:63 char:28
+ $colItems = get-wmiobject <<<< -computername $PHYSICALSERVER win32_ntlogevent -filter "EventType = 1 and Log
File = 'Application' and TimeGenerated >= '$StartDate'" #| format-table TimeGenerated, EventCode, SourceName, LogFile,
Message -wrap -autosize
+ CategoryInfo : InvalidOperation: (:) [Get-WmiObject], ManagementException
+ FullyQualifiedErrorId : GetWMIManagementException,Microsoft.PowerShell.Commands.GetWmiObjectCommand
Get-WmiObject : Invalid query
At C:\PSScripts\WIP\DailyChecks\DailyChecklist_Production.ps1:79 char:28
+ $colItems = get-wmiobject <<<< -computername $PHYSICALSERVER win32_ntlogevent -filter "EventType = 1 and Log
File = 'System' and TimeGenerated >= '$StartDate'" #| format-table TimeGenerated, EventCode, SourceName, LogFile, Messa
ge -wrap -autosize
+ CategoryInfo : InvalidOperation: (:) [Get-WmiObject], ManagementException
+ FullyQualifiedErrorId : GetWMIManagementException,Microsoft.PowerShell.Commands.GetWmiObjectCommand
Exception calling "Fill" with "1" argument(s): "A network-related or instance-specific error occurred while establishin
g a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct
and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not o
pen a connection to SQL Server)"
At C:\PSScripts\WIP\DailyChecks\DailyChecklist_Production.ps1:106 char:19
+ $SqlAdapter.Fill <<<< ($DataSet)
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
Exception calling "Fill" with "1" argument(s): "A network-related or instance-specific error occurred while establishin
g a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct
and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not o
pen a connection to SQL Server)"
At C:\PSScripts\WIP\DailyChecks\DailyChecklist_Production.ps1:134 char:19
+ $SqlAdapter.Fill <<<< ($DataSet)
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException
December 22, 2011 at 1:25 pm
I did get the PowerShell Host (sqlps) working with SQL Server 2005 on a Windows 2003 Server using PowerShell 2. You have to install a number of the SQL Server 2008 Feature Pack items and register the snapins using the .Net framework InstallUtil.exe.
December 23, 2011 at 4:44 pm
OK... I was able to get "htmlreports.ps1" completely working without errors and proper output (backup and errors html report). My environment is PowerShell 2.0, SQL Server 2005, and Windows 2003 Server. It can be done, but it is not easy. I am relatively new in PS, but with some Googling, luck, and damn good troubleshooting, I got it to work. Now ask me how? Oops, I forgot to document my adventure.
Feliz Navidad!
December 27, 2011 at 8:54 am
Hi,
Thanks for the article. Myself is just a beginner with PowerShell. I got bunch of error while executing the scripts. I was able to solve the error (well it took time for me to figure out). But i am stuck with the error, mentioned below, which i am not able to figure out. Can someone help me on this? Thanks in advance..
------------------
The term './parseerrorlog.bat' is not recognized as the name of a cmdlet, function, script file, or operable program. C
heck the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:3 char:20
+ ./parseerrorlog.bat <<<< $instance
+ CategoryInfo : ObjectNotFound: (./parseerrorlog.bat:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
The term './parseerrorlog.bat' is not recognized as the name of a cmdlet, function, script file, or operable program. C
heck the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:7 char:20
+ ./parseerrorlog.bat <<<< $instance
+ CategoryInfo : ObjectNotFound: (./parseerrorlog.bat:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
----------------------------------
December 27, 2011 at 10:08 am
NY-SQL DBA (12/27/2011)
------------------The term './parseerrorlog.bat' is not recognized as the name of a cmdlet, function, script file, or operable program. C
heck the spelling of the name, or if a path was included, verify that the path is correct and try again.
At line:3 char:20
+ ./parseerrorlog.bat <<<< $instance
+ CategoryInfo : ObjectNotFound: (./parseerrorlog.bat:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException
----------------------------------
The text of the article refers to 'parserrorlog.bat', but the script refers to 'parseerrorlog.bat' -- a subtle difference.
December 27, 2011 at 10:42 am
Thanks Ron for the update.
I changed the file name to parseerrorlog.bat and made the same changes in the script accodingly. i changed back to the names as per the article, now i have a txt file named 'parseerrorlog.txt' and the batch file named 'parserrorlog.bat' on my working directory. The path in the batch file point as following "C:\Data\PowerShell\HTMLReports\parseerrorlog.txt"
Still i am getting the same error as in my earlier post.
December 27, 2011 at 11:30 am
NY-SQL DBA (12/27/2011)
Thanks Ron for the update.I changed the file name to parseerrorlog.bat and made the same changes in the script accodingly. i changed back to the names as per the article, now i have a txt file named 'parseerrorlog.txt' and the batch file named 'parserrorlog.bat' on my working directory. The path in the batch file point as following "C:\Data\PowerShell\HTMLReports\parseerrorlog.txt"
Still i am getting the same error as in my earlier post.
I'm not sure what difference it makes, but in my "htmlreports.ps1" the working directory is referenced as $workingdir="D:\Data\Powershell\HTMLReports\Working". All of the files -- ps1, txt, bat, csv, html -- reside in that directory.
When I rename (misname) the batch file to parserror.bat, I get the same error you are experiencing. The error appears to be a path/file name issue.
January 11, 2012 at 12:08 pm
how can I send this html report in the body of an email I am new to powershell
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply