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

This topic contains 3 replies, has 3 voices, and was last updated by  James Crompton 1 week, 1 day ago.

  • Author
    Posts
  • #102449

    SK
    Participant

    $Header = @"

    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;}

    "@
    Get-WmiObject win32_Service | 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:\Services_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).'html'

    The above code works on a single server and it generates a HTML table successfully.I need to run the code on multiple sql servers and create the output in a single html file as above.I need to past the server list in a text file(C:\abcd\servers.txt)
    How can I do this?
    Thanks

  • #102487

    postanote
    Participant

    Try, just using a loop using your serverlist.
    Something like..

    # First create your blank HTML file
    # Then run your code

    (Get-Content -Path 'C:\abcd\servers.txt') | %{
    # Your code here 
    # appending results to the created file.
    }
    
  • #102541

    SK
    Participant

    Does not loop through the servers in the text file.

  • #102554

    James Crompton
    Participant

    Get-WMIObject can be run against many computers at once. One way is to do this

    $serverList = Get-Content -Path 'C:\abcd\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:\Services_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).'html'
    

    For error handling you can add the -ErrorVariable parameter to the Get-WMIObject so you can determine if it fails against one or more servers.

You must be logged in to reply to this topic.