Post reply

Extract data into a HTML table from multiple sql servers using powershell

  • June 14, 2018 at 3:01 am

    #355320

    $Header = @"
    <style>
    TABLE {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;}
    TH {border-width: 1px; padding: 3px; border-style: solid; border-color: black; background-color: #6495ED;}
    TD {border-width: 1px; padding: 3px; border-style: solid; border-color: black;}
    </style>
    "@
    $serverList = Get-Content -Path "C:\Servers.txt"
    Get-WmiObject win32_Service -ComputerName $serverList | where {$_.DisplayName -match "SQL Server"} | select SystemName, DisplayName, Name, State, Status, StartMode, StartName |
    ConvertTo-Html -Property SystemName,Name,State,Status,StartMode,StartName -Head $Header |Out-File C:\EFGH_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).'html' | Sort-Object Name

    I was able to loop through servers and capture data into a single html file as above, but some servers are throwing out an error and the code still runs through the rest of the servers.txt file.How do I capture those servers in a separate text file or in the same html output file under a different table with a header:Servers unable to connect.
    I plan to run this under a task scheduled job.
    Thanks

  • June 19, 2018 at 11:33 am

    #1994497

    Would a try-catch construct help?

  • June 20, 2018 at 11:05 pm

    #1994760

    how do you do that?

  • June 21, 2018 at 4:56 am

    #1994785

    Something like

    try
    {
    Get-WmiObject win32_Service -ComputerName $serverList | where {$_.DisplayName -match "SQL Server"} | select SystemName, DisplayName, Name, State, Status, StartMode, StartName |
    ConvertTo-Html -Property SystemName,Name,State,Status,StartMode,StartName -Head $Header |Out-File C:\EFGH_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).'html' | Sort-Object Name
    }
    catch
    {
    write-host $_.Exception.Message
    #do something with the server(s) that are causing the error.
    }

    You may need to break up the command

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply