Solved

Need Help with Powershell Script

Posted on 2011-09-12
2
589 Views
Last Modified: 2012-05-12
Couple Days ago my boss gave me a task to scan number of computers to get a full Involuntary of software installed on each device.  I created a powershell script to scan list of computers in a text file (devices.txt) and then list the software installed on there own worksheet.  What my boss wants me to do now is it to scan the output file and get the full count of installed software on the network. I have a total of 2000 computers I have scanned and I need to find a way to modify my current scrip or create 2nd one to compile list of the software an a total number of each installed.

I have attached the script  and sample of the output file....  

#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$Excel.SheetsInNewWorkbook = @(get-content "C:\PS\MBSA\servers.txt").count

#Counter variable for rows
$i = 1

#Read thru the contents of the Servers.txt file
foreach ($server in get-content "C:\PS\MBSA\servers.txt")
{
    $Excel = $Excel.Workbooks.Add()
    $Sheet = $Excel.Worksheets.Item($i++)
    $Sheet.Name = $server

    $intRow = 1

    # Send a ping to verify if the Server is online or not.
    $ping = Get-WmiObject `
    -query "SELECT * FROM Win32_PingStatus WHERE Address = '$server'"
       if ($Ping.StatusCode -eq 0) {

         #Create column headers
         $Sheet.Cells.Item($intRow,1) = "NAME:"
         $Sheet.Cells.Item($intRow,2) = $server.ToUpper()
         $Sheet.Cells.Item($intRow,1).Font.Bold = $True
         $Sheet.Cells.Item($intRow,2).Font.Bold = $True

         $intRow++

         $Sheet.Cells.Item($intRow,1) = "APPLICATION"
         $Sheet.Cells.Item($intRow,2) = "VERSION"

             #Format the column headers
             for ($col = 1; $col –le 2; $col++)
             {
                  $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
                  $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
                  $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
             }

             $intRow++

             $software = Get-WmiObject `
             -ComputerName $server -Class Win32_Product | Sort-Object Name 

             #Formatting using Excel

             foreach ($objItem in $software){
                $Sheet.Cells.Item($intRow, 1) = $objItem.Name
                $Sheet.Cells.Item($intRow, 2) = $objItem.Version

                   $intRow ++
             }

        $Sheet.UsedRange.EntireColumn.AutoFit()
    }
}

Clear

Open in new window

Output-File.xls
0
Comment
Question by:amstoots
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 70

Accepted Solution

by:
Qlemo earned 500 total points
ID: 36525867
The following code shows one example how to do that. The results are stored in a hash table with a combined key of software name and version (e.g. "Microsoft Outlook 2010"), and the write-host commands are only to show how you get back the collected info. You might want to insert those in another Excel workbook instead.
#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$Excel.SheetsInNewWorkbook = @(get-content "C:\PS\MBSA\servers.txt").count

#Counter variable for rows
$i = 1

#Read thru the contents of the Servers.txt file
foreach ($server in get-content "C:\PS\MBSA\servers.txt")
{
    $Excel = $Excel.Workbooks.Add()
    $Sheet = $Excel.Worksheets.Item($i++)
    $Sheet.Name = $server

    $intRow = 1

    # Send a ping to verify if the Server is online or not.
    $ping = Get-WmiObject `
    -query "SELECT * FROM Win32_PingStatus WHERE Address = '$server'"
       if ($Ping.StatusCode -eq 0) {

         #Create column headers
         $Sheet.Cells.Item($intRow,1) = "NAME:"
         $Sheet.Cells.Item($intRow,2) = $server.ToUpper()
         $Sheet.Cells.Item($intRow,1).Font.Bold = $True
         $Sheet.Cells.Item($intRow,2).Font.Bold = $True

         $intRow++

         $Sheet.Cells.Item($intRow,1) = "APPLICATION"
         $Sheet.Cells.Item($intRow,2) = "VERSION"

             #Format the column headers
             for ($col = 1; $col –le 2; $col++)
             {
                  $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
                  $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
                  $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
             }

             $intRow++

             $software = Get-WmiObject `
             -ComputerName $server -Class Win32_Product | Sort-Object Name 

             #Formatting using Excel

             foreach ($objItem in $software){
                $Sheet.Cells.Item($intRow, 1) = $objItem.Name
                $Sheet.Cells.Item($intRow, 2) = $objItem.Version
                $key = $objItem.Name + " " + $objItem.Version
                if ($sw -eq $null -or $sw[$key] -eq $null) {$sw += @{$key = 1}} else {$sw[$key]++}
                $intRow ++
             }

        $Sheet.UsedRange.EntireColumn.AutoFit()
    }
    Write-Host $sw
    $sw.GetEnumerator() | % {Write-Host $_.Key ":  " $_.Value}
}

Clear

Open in new window

0
 

Author Closing Comment

by:amstoots
ID: 36544664
Thanks, I was able to adjust my script and it worked..
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Previously, on our Nano Server Deployment series, we've created a new nano server image and deployed it on a physical server in part 2. Now we will go through configuration.
In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
Windows 8 comes with a dramatically different user interface known as Metro. Notably missing from the new interface is a Start button and Start Menu. Many users do not like it, much preferring the interface of earlier versions — Windows 7, Windows X…
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question