none
Need help with PS script to read CSV and interegate WMI for logged on user RRS feed

  • Question

  • Hello,
    I am trying to write a PS script to query the WMI of remote computers and return the currently logged on user. The script I have so far is working, and I can feed it into an HTML document. What I am trying to do, and struggling with, is import a list of computers in a single column CSV file. The idea is that the program will work its way down the list of computers returning a the username for each logged on user. This is the code I have so far,

    Please excuse the untidyness of it all, it's a work inprogress.

    [console]::ForegroundColor = "cyan"
    
    $a = "<style>"
    $a = $a + "BODY{background-color:white;}"
    $a = $a + "TABLE{border-width: 2px;border-style: solid;border-color: black;border-collapse: collapse;}"
    $a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}"
    $a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:palegoldenrod}"
    
    $a = $a + "</style>"
    
    
    $password = get-content C:\Scripts\cred.txt | convertto-securestring
    $credentials = new-object -typename System.Management.Automation.PSCredential -argumentlist "administrator",$password
    
    #do
    #{
    $computer= import-csv c:\scripts\rooms.csv | select-object rooms
    #read-host "Enter a computer name"
    clear
    foreach ($line in $computer)
    {
    get-wmiobject win32_computersystem -computername $computer -credential $credentials | select Name, Username `
    | convertTo-html -head $a | out-file c:\Scripts\whosonline.htm | invoke-expression c:\Scripts\whosonline.htm
    }
    
    #$convert = read-host "Would you like to see this list in a browser?(y/n)"
    #if ($convert -eq"y") 
    #{
    #invoke-expression c:\Scripts\whosonline.htm
    #}
    #clear
    #else {$collitems | format-table Name, Username -autosize}
    
    #clear
    
    $repeat= read-host "Would you like to see another computer?(y/n)"
    #}
    #until ($repeat -eq "n")
    #exit
    

    As you can see, there is a lot of comment lines - this is because I'll try something new but not want to lose what I had before, so comment it out instead.

     

    As it stands, the code works but I need it to go through the csv and for each computer, query the WMI to see if there is a logged on user.  Can someone point out the error in my ways?!  I get an error message for each computer it attempts to connect to, saying that the RPC Server is unavailable.  Any ideas?

     

    TIA

    Doug

    Tuesday, October 26, 2010 11:51 AM

Answers

  • Yes, if you want everything in a single HTML file you'll want to do something like this:

     

    $MyOutput = @()
    
    foreach ($line in $computer)
    {
    $MyOutput += get-wmiobject win32_computersystem -computername $line.'Computer Number' | select Name, Username
    }
    
    $MyOutput | ConvertTo-Html -Head $a | Out-File whosonline.htm
    

     

    $MyOutput is an array that will hold Name and Username values.

    $MyOutput += adds an object to the array each time through the loop.

    $MyOutput | ConvertTo-Html -Head $a | Out-File whosonline.htm creates the HTML file using all the objects in the $MyOutput array.

    • Marked as answer by dmdougie Wednesday, October 27, 2010 8:27 AM
    Tuesday, October 26, 2010 4:41 PM

All replies

  • In your CSV, does the column "rooms" indicate the computer name?

    If a CSV looks like this:

    Rooms
    computername1
    computername2
    computername3

    The computer names could be accessed like this:

    $computer= import-csv c:\scripts\rooms.csv
    
    foreach ($line in $computer)
    {
    get-wmiobject win32_computersystem -computername $line.rooms -credential $credentials | select Name, Username `
    | convertTo-html -head $a | out-file c:\Scripts\whosonline.htm | invoke-expression c:\Scripts\whosonline.htm
    }
    
    

    When a CSV is imported with Import-CSV, each row in the CSV becomes an object and each column becomes a property of that object.

    A sample of your CSV file would help if you still have problems.

     

    Tuesday, October 26, 2010 12:51 PM
  • Hi, thankyou for your reply.

     

    I tried what you suggested, this time it returns a different error.  It says that

    get-wmiobject:  cannot validate arguement on parameter "computername". The arguement is null or empty.

     

    The CSV looks like this:

    Computer Number

    c17-01,

    c17-02,

    c17-03,

    etc to 24

     

    I appreciate your help on this :)

     

    Tuesday, October 26, 2010 1:12 PM
  • Make sure there isnt a blank line at the end of a file. I've seen this cause a problem before.

    Tuesday, October 26, 2010 1:39 PM
  • If the name of the header in your CSV is "Computer Number", to access the name of the computer in your foreach loop, use $line.'Computer Number'

    foreach ($line in $computer)
    {
    get-wmiobject win32_computersystem -computername $line.'Computer Number' -credential $credentials | select Name, Username `
    | convertTo-html -head $a | out-file c:\Scripts\whosonline.htm | invoke-expression c:\Scripts\whosonline.htm
    }
    

    Tuesday, October 26, 2010 1:51 PM
  • Thank you both for your replys.

     

    There's definately no blank line at the end of the csv.  If by name of the header you mean the first line of the .csv file, then yes, it's Computer Number.

     

    I tried the ammendment you suggested but to no avail.  It takes a little longer to return the same error message.  Though, weirdly, I checked my web browser and it had returned 1 entry that was correct.  But I know that there at least 10 other users currently logged into that room.

     

    If it helps, the error message in full reads:

    Get-WminObject : The RPC server is unavilable.  (Exception from HRESULT: 0x800706BA) at C:\Scripts\WhosOnlinetake2.ps1:22 char:14

    + get-wmiobject <<<<  win32_computersystem -computername $line.'Computer Number' -credential $credentials | select name, Username `

     + categoryInfo   :  invalidOperation (:) [get-wmiobject], COMExpection

    +fullyQualifiedErrorID  :  getwmicomexception,Microsoft.powershell.commands.getwmiobjectcommand

     

    Not sure if that means anything to anyone?!

     

    Thanks for your help so far.

    Tuesday, October 26, 2010 2:16 PM
  • That error indicates Get-WmiObject was unable to contact the computer specified by the -ComputerName parameter. This error will be generated for any of the computers in the CSV file that are offline.

     

    Tuesday, October 26, 2010 2:29 PM
  • But there all online.  I have another admin tool that allows me to see if anyone is logged into any computer.  i can see right now that 8 people are logged in in that room.  Weird thing is, that if I write the script to prompt the user to enter a computer name, it works fine. 
    Tuesday, October 26, 2010 2:31 PM
  • Try adding the line Write-Host $line.'Computer Number' to the foreach loop so the variable being passed to the -computername parameter is displayed.

     

    Tuesday, October 26, 2010 2:43 PM
  • Hi, thanks for the reply.

     

    I've tidied up the script a bit so it's easier on the eyes.  Note that I have added the line as per previous suggestion:

    [console]::ForegroundColor = "cyan"
    
    $a = "<style>"
    $a = $a + "BODY{background-color:white;}"
    $a = $a + "TABLE{border-width: 2px;border-style: solid;border-color: black;border-collapse: collapse;}"
    $a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}"
    $a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:palegoldenrod}"
    
    $a = $a + "</style>"
    
    
    $password = get-content C:\Scripts\cred.txt | convertto-securestring
    $credentials = new-object -typename System.Management.Automation.PSCredential -argumentlist "administrator",$password
    
    
    $computer= import-csv c:\scripts\rooms.csv
    
    clear
    foreach ($line in $computer)
    {
    get-wmiobject win32_computersystem -computername $line.'Computer Number' -credential $credentials | select Name, Username `
    | convertTo-html -head $a | out-file c:\Scripts\whosonline.htm | invoke-expression c:\Scripts\whosonline.htm
    Write-Host $line.'Computer Number'
    }

    Whats happening now is that the first computer is listed

    c17-01

    It then ticks over for about 15 seconds, and then outputs the same error message and moves on to the next one in the list.  I guess this signifies that theres nothing wrong with the import as it seems to be working it's way down the list.  As you said before, it's almost as if they are turned off.  Yet I can ping each of them and I can run another version of this script that queries them individually as per user entry.

     

    What do you think could be the cause of that?!  Thanks, once again for your help with this.  I appreciate you time and expertise :)

    Tuesday, October 26, 2010 2:56 PM
  • OK I've made some progress.

     

    I took out the out-file converTo-HTML part and pumped it into a regular table within the PS Console.  It worked for the first one straight away, then failed for the next four, then returned the values of the next 13 or so computers then failed again then returned the rest of the entries.  Wasn't ideal as every time it returned a value it was in a new table so I had to scroll up to see the others.

    Do you think maybe I need to take the out-file convertto html bit out of the foreach loop, and add it outside of the loop?  My thoguhts are that maybe it's overwriting each entry everytime it reads a new line?

     

    Thanks for your help so far, almost there maybe? ;)

    Tuesday, October 26, 2010 3:13 PM
  • Yes, if you want everything in a single HTML file you'll want to do something like this:

     

    $MyOutput = @()
    
    foreach ($line in $computer)
    {
    $MyOutput += get-wmiobject win32_computersystem -computername $line.'Computer Number' | select Name, Username
    }
    
    $MyOutput | ConvertTo-Html -Head $a | Out-File whosonline.htm
    

     

    $MyOutput is an array that will hold Name and Username values.

    $MyOutput += adds an object to the array each time through the loop.

    $MyOutput | ConvertTo-Html -Head $a | Out-File whosonline.htm creates the HTML file using all the objects in the $MyOutput array.

    • Marked as answer by dmdougie Wednesday, October 27, 2010 8:27 AM
    Tuesday, October 26, 2010 4:41 PM
  • Excellent.  Thank you so much for your persistent help with this JHofferlem worked a charm :)

    Wednesday, October 27, 2010 8:26 AM