Powershell software query

Tim_Jr
Tim_Jr used Ask the Experts™
on
Hi everyone. I'm quite new to powershell and I really enjoy using it. I have a script that queries remote computers from a text file and searchs for the instance of a software package (Snare for Windows) and then queries the registry for some values corresponding to the software (Destination and Destination Port). The req query was tough to find and I'm not sure if it is perfect.
Anyway, the script works ok and writes to an excel sheet, except the fact that it only writes output of the very last IP address that I put in the text file. If I have 5 IP addresses in the text file, it only exports the information correspinding to the 5th entry in the file. Weird. Here is my script:

# Queries a list of computers for a specific software instance, this example looks for Snare
$a = New-Object -comobject Excel.Application
$a.visible = $True

$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)

$c.Cells.Item(1,1) = "Machine Name"
$c.Cells.Item(1,2) = "Snare Version"
$c.Cells.Item(1,3) = "Snare Destination"
$c.Cells.Item(1,4) = "Snare Port"

$d = $c.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True

$intRow = 2

$colComputers = get-content c:\powershell\snarecomps.txt
foreach ($strComputer in $colComputers)
{
$Computer = get-wmiobject Win32_computerSystem -computername $strComputer
$Snare = gwmi win32_product -computername $strComputer | Where-Object { $_.vendor -like "*Inter*" }  
$key= "Software\InterSect Alliance\AuditService\Network"
$keytype=[Microsoft.Win32.RegistryHive]::LocalMachine
$remotebase=[Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($keytype,$strComputer)
$regkey=$remotebase.OpenSubKey($key)
$regkeygetport = $regkey.Getvalue("DestPort")
$regkeygetip = $regkey.Getvalue("Destination")
}

$c.Cells.Item($intRow,1) = $strComputer.Toupper()
$c.Cells.Item($intRow,2) = $Snare.Name
$c.Cells.Item($intRow,3) = $regkeygetip
$c.Cells.Item($intRow,4) = $regkeygetport

$c.Cells.Item($intRow,5) = Get-date

$intRow = $intRow + 1

# $d.EntireColumn.AutoFit()


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chris DentPowerShell Developer
Top Expert 2010

Commented:

Your Row increment needs to be within a loop unless there's more to your code. At the moment it's just continually overwriting the second row.

Does that fit in with what you're seeing?

Chris
Chris DentPowerShell Developer
Top Expert 2010

Commented:

Potentially like this...

Chris
# Queries a list of computers for a specific software instance, this example looks for Snare
$a = New-Object -comobject Excel.Application
$a.visible = $True

$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)

$c.Cells.Item(1,1) = "Machine Name"
$c.Cells.Item(1,2) = "Snare Version"
$c.Cells.Item(1,3) = "Snare Destination"
$c.Cells.Item(1,4) = "Snare Port"

$d = $c.UsedRange
$d.Interior.ColorIndex = 19
$d.Font.ColorIndex = 11
$d.Font.Bold = $True

$intRow = 2

Get-Content c:\powershell\snarecomps.txt | %{

  $Computer = Get-WMIObject Win32_computerSystem -ComputerName $_
  $Snare = Get-WMIObject Win32_Product -Computername $_ | `
    Where-Object { $_.vendor -like "*Inter*" }  

  $Key = "Software\InterSect Alliance\AuditService\Network"
  $KeyType = [Microsoft.Win32.RegistryHive]::LocalMachine
  $Remotebase = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($KeyType, $_)
  $RegKey = $RemoteBase.OpenSubKey($Key)

  $RegKeyGetPort = $RegKey.GetValue("DestPort")
  $RegKeyGetIP = $RegKey.GetValue("Destination")

  $c.Cells.Item($intRow,1) = $_.ToUpper()
  $c.Cells.Item($intRow,2) = $Snare.Name
  $c.Cells.Item($intRow,3) = $RegKeyGetIP 
  $c.Cells.Item($intRow,4) = $RegKeyGetPort 

  $c.Cells.Item($intRow,5) = Get-date

  $intRow++
}

# $d.EntireColumn.AutoFit()

Open in new window

Author

Commented:
Holy crow that was fast Chris. Thanks, your version worked perfect. If you don't mind me asking, what were the major changes that you did to fix the issue? Was it the $intRow++ command ... ?
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

PowerShell Developer
Top Expert 2010
Commented:
Hey,

Not too many changes, going through them...

> Get-Content c:\powershell\snarecomps.txt | %{

This just swapped out the ForEach loop for ForEach-Object (via it's alias, %). It works in almost the same way, it just saves a tiny bit of space. One major difference between ForEach and ForEach-Object is that the latter can be pipelined. e.g.

Get-Content c:\powershell\snarecomps.txt | %{ $_ } | Set-Content "SomeOtherFile.txt"

You can't do that with a ForEach loop unless you encapsulate it in a Sub-Expression (without extra work).

That's not particularly meaningful in this script, but it's nice to know about the tools.

>   $Remotebase = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($KeyType, $_)

You'll see $_ a fair bit above. That's the pipeline object, the current entry in the file in this case. It's the equivalent of $strComputer from your own.

>  $intRow++

PowerShell has Increment and Decrement operators (as well as Pre-Increment and Pre-Decrement operators).

In the context of this it simply replaces $intRow = $intRow + 1.

If you're interested in how those work it's better to see these in action, and the easiest way to see is when you use an array index counter.

Hopefully that covers them all :)

Chris
# Increment, Decrement, Pre-Increment and Pre-Decrement Operators

$Arr = "one", "two",

# Increment Operator:
$i = 0
# Will show "one"
Write-Host $($Arr[$i])
# Will show "one" again, $i is incremented afterwards
Write-Host $($Arr[$i++])
# Will show "two", the counter was incremented last time
Write-Host $($Arr[$i])

# Decrement Operator:
# Will show "two"
Write-Host $($Arr[$i])
# Will show "two" again, $i is decremented afterwards
Write-Host $($Arr[$i--])
# Will show "one", the counter was incremented last time
Write-Host $($Arr[$i])

# Pre-Increment Operator:
# Will show "one"
Write-Host $($Arr[$i])
# Will show "two", $i is incremented beforehand
Write-Host $($Arr[++$i])
# Will show "two", the counter was incremented last time
Write-Host $($Arr[$i])

# Pre-Decrement Operator:
# Will show "two"
Write-Host $($Arr[$i])
# Will show "one", $i is decremented beforehand
Write-Host $($Arr[--$i])
# Will show "one", the counter was incremented last time
Write-Host $($Arr[$i])

Open in new window

Chris DentPowerShell Developer
Top Expert 2010

Commented:

Oh and I moved the block that writes to the cells inside the loop as well. Kind of important :)

Chris

Author

Commented:
Incredible, greatly appeciated Chris!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial