Output to Excel

This topic contains 5 replies, has 3 voices, and was last updated by Profile photo of Martin Nielsen Martin Nielsen 1 year, 9 months ago.

  • Author
    Posts
  • #22311
    Profile photo of Jeffrey Ritch
    Jeffrey Ritch
    Participant

    I'm trying to create a PS script that takes input from a text file, gets the services running from our servers and exports that to one excel file.
    I have everything working except that as the script creates the excel file, it over rights each of the first line over and over for each server.
    Q: How do I get my script to drop down to the next line and record the data and then drop down again for more data.

    Thank you for any help!
    ========================================
    # Get Server Information
    param (
    [string[]]$ServerArray = (Get-Content -Path c:\temp\serverlist.txt)
    )
    # Create new Excel workbook
    Write-Verbose "$(Get-Date): Script begins!"
    Write-Verbose "$(Get-Date): Opening Excel..."
    $Excel = New-Object -ComObject Excel.Application
    $Excel.Visible = $True
    $Excel = $Excel.Workbooks.Add()
    $Sheet = $Excel.Worksheets.Item(1)
    $Sheet.Name = "Server Services"
    #======================================================
    $Sheet.Cells.Item(1,1) = "Server"
    $Sheet.Cells.Item(1,2) = "Service Name"
    $Sheet.Cells.Item(1,3) = "Status"
    $Sheet.Cells.Item(1,4) = "Startup Type"
    $Sheet.Cells.Item(1,5) = "Service Account"
    #=======================================================
    $intRow = 2
    $WorkBook = $Sheet.UsedRange
    $WorkBook.Interior.ColorIndex = 40
    $WorkBook.Font.ColorIndex = 11
    $WorkBook.Font.Bold = $True
    #=======================================================
    # Main loop of script
    ForEach ($Server in $ServerArray)
    {
    Get-WmiObject win32_service -ComputerName $Server | where {$_.startname -notlike "*Local*Service*" -and $_.startname -notlike "*local*system*" -and $_.startname -notlike "*Network*Service*"} | select Name,
    { If ($Server -notlike "SERVERTEST")
    { $Sheet.Cells.Item($intRow, 1) = $_.systemname
    $Sheet.Cells.Item($intRow, 2) = $_.Name
    $Sheet.Cells.Item($intRow, 3) = $_.State
    $Sheet.Cells.Item($intRow, 4) = $_.StartMode
    $Sheet.Cells.Item($intRow, 5) = $_.StartName
    }
    }
    $WorkBook.EntireColumn.AutoFit() | Out-Null
    }
    $intRow ++
    $Sheet.Cells.Item($intRow,1) = "Server Inventory Completed"
    $Sheet.Cells.Item($intRow,1).Font.Bold = $True
    $Sheet.Cells.Item($intRow,1).Interior.ColorIndex = 40
    $Sheet.Cells.Item($intRow,2).Interior.ColorIndex = 40
    $Sheet.Cells.Item($intRow,3).Interior.ColorIndex = 40
    Write-Verbose "$(Get-Date): Completed!"
    ========================================

  • #22318
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    Hey Jeffrey,

    Your command for incrementing the row is outside of the For..Each loop, which is causing this.

    If you place the

     $intRow++

    immediately after the line with

    $WorkBook.EntireColumn.AutoFit() | Out-Null

    , you should be okay.

  • #22320
    Profile photo of Jeffrey Ritch
    Jeffrey Ritch
    Participant

    Tim,
    Thank you for your reply.
    That places the output for each server on a new line (correct) but doesn't lable it on as a server name. Then the same responce as before for each sever that has an output, it over rights it on that server line in excell. Not dropping down to the next.
    Jeffrey

  • #22321
    Profile photo of Jeffrey Ritch
    Jeffrey Ritch
    Participant

    With a little luck, I got this working. I just need one more item. On the output, if a server doesn't have any output, it just drops a line and continues to the next server. Which is fine, except I would like a list of all servers, even if there isn't any services that need reported.

    If anyone has any ideas?

    ======================================================
    Start of the script
    ======================================================

    #======================================================
    # Get Server Information
    # Modify the path to get list of servers
    #======================================================
    [CmdletBinding()]
    param (
    [string[]]$ServerArray = (Get-Content -Path c:\temp\listservers.txt)
    )
    #======================================================
    # Create new Excel workbook
    #======================================================
    Write-Verbose "$(Get-Date): Script begins!"
    Write-Verbose "$(Get-Date): Opening Excel..."
    $Excel = New-Object -ComObject Excel.Application
    $Excel.Visible = $True
    $Excel = $Excel.Workbooks.Add()
    $Sheet = $Excel.Worksheets.Item(1)
    $Sheet.Name = "Server Services"
    #======================================================
    # Place Column Header Names in Excel file
    #======================================================
    $Sheet.Cells.Item(1,1) = "Server"
    $Sheet.Cells.Item(1,2) = "Service Name"
    $Sheet.Cells.Item(1,3) = "Status"
    $Sheet.Cells.Item(1,4) = "Startup Type"
    $Sheet.Cells.Item(1,5) = "Service Account"
    #=======================================================
    # Format the Top Header Rows
    #=======================================================
    $intRow = 2
    $WorkBook = $Sheet.UsedRange
    $WorkBook.Interior.ColorIndex = 40
    $WorkBook.Font.ColorIndex = 11
    $WorkBook.Font.Bold = $True
    #=======================================================
    # Main Loop
    # Gets the computer name from the txt file, searches and removes any notlike
    # Places results in the Excel file
    #=======================================================
    ForEach ($Server in $ServerArray)
    {
    $datainfo = Get-WmiObject win32_service -ComputerName $Server | where {$_.startname -notlike "*Local*Service*" -and $_.startname -notlike "*local*system*" -and $_.startname -notlike "*Network*Service*"}
    $datainfo | % {
    If ($Server -notlike "SERVERTEST")
    { $Sheet.Cells.Item($intRow, 1) = $_.systemname
    $Sheet.Cells.Item($intRow, 2) = $_.Name
    $Sheet.Cells.Item($intRow, 3) = $_.State
    $Sheet.Cells.Item($intRow, 4) = $_.StartMode
    $Sheet.Cells.Item($intRow, 5) = $_.StartName
    $intRow ++
    }
    $WorkBook.EntireColumn.AutoFit() | Out-Null
    }
    }
    #=======================================================
    # Format the Ending Row
    #=======================================================
    $intRow ++
    $Sheet.Cells.Item($intRow,1) = "Server Inventory Completed"
    $Sheet.Cells.Item($intRow,1).Font.Bold = $True
    $Sheet.Cells.Item($intRow,1).Interior.ColorIndex = 40
    $Sheet.Cells.Item($intRow,2).Interior.ColorIndex = 40
    $Sheet.Cells.Item($intRow,3).Interior.ColorIndex = 40
    Write-Verbose "$(Get-Date): Completed!"

    ======================================================
    End of the script
    ======================================================

  • #22659
    Profile photo of Jeffrey Ritch
    Jeffrey Ritch
    Participant

    This should be the finished product. Copy and make changes as needed.

    #======================================================
    # Get Server Information
    # Modify the path to get list of servers
    #======================================================
    param (
    [string[]]$ServerArray = (Get-Content -Path ListServers.txt)
    )
    #======================================================
    # Create new Excel workbook
    #======================================================
    Write-Verbose "$(Get-Date): Script begins!"
    Write-Verbose "$(Get-Date): Opening Excel..."
    $Excel = New-Object -ComObject Excel.Application
    $Excel.Visible = $True
    $Excel = $Excel.Workbooks.Add()
    $Sheet = $Excel.Worksheets.Item(1)
    $Sheet.Name = "Server Services"
    #=======================================================
    # Place Column Header Names in Excel file
    #=======================================================
    $Sheet.Cells.Item(1,1) = "Server"
    $Sheet.Cells.Item(1,2) = "Service Name"
    $Sheet.Cells.Item(1,3) = "Status"
    $Sheet.Cells.Item(1,4) = "Startup Type"
    $Sheet.Cells.Item(1,5) = "Service Account"
    #=======================================================
    # Format the Top Header Rows
    #=======================================================
    $intRow = 2
    $WorkBook = $Sheet.UsedRange
    $WorkBook.Interior.ColorIndex = 40
    $WorkBook.Font.ColorIndex = 11
    $WorkBook.Font.Bold = $True
    #=======================================================
    # Main Loop
    # Gets the computer name from the txt file, searches and removes any notlike
    # Places results in the Excel file
    #=======================================================
    ForEach ($Server in $ServerArray)
    {
    $Sheet.Cells.Item($intRow, 1) = $Server
    $Sheet.Cells.Item($intRow, 2) = ""
    $Sheet.Cells.Item($intRow, 3) = ""
    $Sheet.Cells.Item($intRow, 4) = ""
    $Sheet.Cells.Item($intRow, 5) = ""
    $a=Get-WmiObject win32_service -ComputerName $Server | where {$_.startname -notlike "*Local*Service*" -and $_.startname -notlike "*local*system*" -and $_.startname -notlike "*Network*Service*"}
    $a | % {
    If ($Server -notlike "SERVERTEST")
    {

    If ($Server -ne $_.systemname)
    {
    $intRow ++
    }
    else
    {
    $Sheet.Cells.Item($intRow, 1) = $_.systemname
    $Sheet.Cells.Item($intRow, 2) = $_.Name
    $Sheet.Cells.Item($intRow, 3) = $_.State
    $Sheet.Cells.Item($intRow, 4) = $_.StartMode
    $Sheet.Cells.Item($intRow, 5) = $_.StartName
    $intRow ++
    }
    }
    $WorkBook.EntireColumn.AutoFit() | Out-Null
    }
    }
    #=======================================================
    # Format the Ending Row
    #=======================================================
    $intRow ++
    $Sheet.Cells.Item($intRow,1) = "Server Inventory Completed"
    $Sheet.Cells.Item($intRow,1).Font.Bold = $True
    $Sheet.Cells.Item($intRow,1).Interior.ColorIndex = 40
    $Sheet.Cells.Item($intRow,2).Interior.ColorIndex = 40
    $Sheet.Cells.Item($intRow,3).Interior.ColorIndex = 40
    Write-Verbose "$(Get-Date): Completed!"

  • #22744
    Profile photo of Martin Nielsen
    Martin Nielsen
    Participant
    Get-Service | Export-Csv -UseCulture -NoTypeInformation services.csv

    This'll open up just fine in Excel. Granted it doesn't have colors, but it can run on any machine, even without Excel installed.

    To get a list of services from different machines, you could do

    foreach($computer in $computers) { 
        Get-Service -ComputerName $computer | Add-Member -NotePropertyName Computer -NotePropertyValue $computer -PassThru | Export-Csv -Append -UseCulture -NoTypeInformation services.csv 
    }
    

You must be logged in to reply to this topic.