Output to Excel

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 “LocalService*” -and $.startname -notlike “localsystem*” -and $.startname -notlike “NetworkService*”} | 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!”

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.

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

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 “LocalService*” -and $.startname -notlike “localsystem*” -and $.startname -notlike “NetworkService*”}
$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

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 “LocalService*” -and $.startname -notlike “localsystem*” -and $_.startname -notlike “NetworkService*”}
$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!”

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 
}