Automating Daily Checks with Powershell

  • 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...

  • I did this already. It still does not work. I will put it down for a little while and come back to it later.

  • 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

  • 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>

  • 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!

  • 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.

  • As below 😉

  • 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

  • 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.

  • 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!

  • 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

    ----------------------------------

  • 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.

  • 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.

  • 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.

  • 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