How to retrieve user logged on by reading computer name from database?

Question

I am very new to powershell and have this script that will ping every computer from a database server. I am using MySQL (yes I know most people use MSSQL with powershell but in my case I have to use MySQL).

The ping works perfectly fine, how can I retrieve USERNAME for every computer that pings succesfully?

Thanks in advance!

Below is my code:

#ping the computers from the database to verify if it is online/offline 
 #Create a command object
$command = New-Object MySql.Data.MySqlClient.MySqlCommand;
$command.Connection = $connection

# $command = $connection.CreateCommand() #ERROR

#call procedure to execute the command
$command.CommandText = "SELECT w.ws FROM
                        sandbox_maesc4.coordinates c
                        INNER JOIN
                        softphone_materials_updater.workstations w
                        ON c.station_number = w.pod";

#Execute the Command by reading each row
$reader = $command.ExecuteReader();
#Read values from database
$workstation_list = $( while ($reader.Read() ){
                    $reader.GetValue(0) 
                })
   #close reader                     
$reader.Close()


#ping each computer if online, obtain username logged in only
foreach($pc_db in $workstation_list){
    if(Test-Connection -ComputerName $pc_db -Count 1 -ErrorAction SilentlyContinue){
     #HERE IS THE CODE, I JUST WANT TO RETRIEVE USERNAME THAT'S LOGGED ON!    
     Get-WMIObject -ComputerName $pc_db -Class Win32_ComputerSystem | Select-Object -ExpandProperty Username
    }
    else{
        Write-Host "$pc_db is Offline" -ForegroundColor Red
    }


}#end for each loop  
$connection.Close()
}#end ping_test function

db_conn #execute function

you mean get the name of whoever's logged in on the machine you're pinging? — Marc B, Oct 21 '14 at 21:23

score 1 · Accepted Answer · answered Oct 21 '14 at 21:25

1

If you're looking for the username of the current logged on user, you can reference the Win32_ComputerSystem WMI object which has a username parameter containing the current logged-in user:

Get-WMIObject -ComputerName $pc_db -Class Win32_ComputerSystem | Select-Object -ExpandProperty Username

answered Oct 21 '14 at 21:25

Kohlbrr

3,861
1
21
24

Man you are fast :) in PowerShell v3 `Get-WMIObject` is superseded and Get-CimInstance should be used – Paul Oct 21 '14 at 21:29
True, however I believe WMI has the advantage of being fully compatible with legacy systems (XP, 2003, etc.)...not that those systems should ideally be in a prod environment by now. – Kohlbrr Oct 21 '14 at 21:31
i agree for old systems wmi is the way to go, also agree with the second part – Paul Oct 21 '14 at 21:34
As for speed - combine caffeine with a PowerShell-centric day and there's your answer :) – Kohlbrr Oct 21 '14 at 21:36
@Kohlbrr hey I tested it and gives me this error ( I put it inside my IF statement where I ping each computer): **Select-Object : Cannot process argument because the value of argument "obj" is null. Change the value of argument "obj" to a non-null va lue.** whats going on? – mario Oct 22 '14 at 13:05
Change the command to `Get-WMIObject -ComputerName $pc_db -Class Win32_ComputerSystem -Property Username` and let me know if the "Username" property is populated / the command executes successfully. – Kohlbrr Oct 22 '14 at 13:13
Run `Get-WMIObject -ComputerName $pc_db -Class Win32_ComputerSystem` and see if it returns something (disregard me, i´ll leave this to you @Kohlbrr :)) – Paul Oct 22 '14 at 13:14
@Kohlbrr it gives me this long output, some of them have usernames and others dont: **__GENUS : 2 __CLASS : Win32_ComputerSystem __SUPERCLASS : __DYNASTY : __RELPATH : __PROPERTY_COUNT : 1 __DERIVATION : {} __SERVER : __NAMESPACE : __PATH : UserName :** – mario Oct 22 '14 at 13:21
@Kohlbrr If I removed **-ExpandProperty** from the answer that you gave me it stops showing **"obj" is null"**. However, it skips lines in blank spaces and gives me the usernames. I'm confused – mario Oct 22 '14 at 13:49
Can you give an example of the output? Does the command successfully return any usernames? – Kohlbrr Oct 22 '14 at 13:53
@Kohlbrr If I use the following command **Get-WMIObject -ComputerName $pc_db -Class Win32_ComputerSystem | Select-Object Username** It gives me this output: **Username -------- callcentre\oxgor** which oxgor is the username but at moments it will give me this error : **Get-WmiObject : The RPC server is unavailable** how can I avoid it? – mario Oct 22 '14 at 14:02
@mario "RPC server is unavailable" is usually a firewall problem on the remote machine. are you requesting the object from the same computer that returned output before? – Paul Oct 22 '14 at 14:10
@Paul I'm sorry im not understanding your question? I tried doing TRY-CATCH but still shows up – mario Oct 22 '14 at 14:42
He's asking if there are some computers that consistently give the username, and others that consistently throw an RPC error - specifically that a computer doesn't return a username sometimes and an RPC error at other times. – Kohlbrr Oct 22 '14 at 14:45
@Paul It only throws an RPC error once. The other times it will either show me the usernames that are logged on on those computers and the others will just return me an empty username with the computer name. – mario Oct 22 '14 at 16:23

score 1 · Answer 2 · answered Oct 21 '14 at 21:26

1

You can use

Get-CimInstance -ComputerName $pc_db -class Win32_ComputerSystem | select username

This should return the currently active user

answered Oct 21 '14 at 21:26

Paul

5,524
1
22
39

Hey I tested it and gives me this error : **The term 'Get-CimInstance' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again** I put it inside my IF statement where I ping each computer.. whats going on? – mario Oct 22 '14 at 13:03
please post the output of $psversiontable, i guess you are running powershell v2. if this is the case use Get-WmiObject like Kohlbrr suggests – Paul Oct 22 '14 at 13:06
I updated my code so you can see where exactly I put it, I commented it out because Im comparing both answers – mario Oct 22 '14 at 13:06
$psversiontable: **CLRVersion 2.0.50727.5485 BuildVersion 6.1.7601.17514 PSVersion 2.0 WSManStackVersion 2.0 PSCompatibleVersions {1.0, 2.0} SerializationVersion 1.1.0.1 PSRemotingProtocolVersion 2.1** – mario Oct 22 '14 at 13:11
I was right, you are using V2. Get-CimInstance is a V3 cmdlet. Please remove the powershell v3 tag from your question – Paul Oct 22 '14 at 13:12

How to retrieve user logged on by reading computer name from database?

2 Answers2