Navigation

PowerShell Query to find a users computer name in SCCM

By on January 17, 2012 in PowerShell, SCCM

We rely on Remote Assistance. Usually, I just type “msra /offerra” in to my PowerShell session and lookup a the user’s computer name in the SCCM report named “Computers for a specific user name”. I wanted to make that process quicker. I wrote the following script to query SCCM for the “list of computer’s who’s last logged on user” is the person I am looking for.

FUNCTION JBMURPHY-SCCM-GetComputerByLastLoggedOnUser {
Param([parameter(Mandatory = $true)]$SamAccountName,
	$SiteName="JBM",
	$SCCMServer="SCCMServer.domain.local")
	$SCCMNameSpace="root\sms\site_$SiteName"
	Get-WmiObject -namespace $SCCMNameSpace -computer $SCCMServer -query "select Name from sms_r_system where LastLogonUserName='$SamAccountName'" | select Name
}

Let me know if this is useful to you!

,

9 Responses to PowerShell Query to find a users computer name in SCCM

  1. VertigoRay January 23, 2012 at 2:31 pm #

    Well done, after modifying the two obvious variables that needed to be change ($SiteName & $SCCMServer) to match my SCCM info, I only had to tweak your SQL query to remove that rogue comma…

    “select Name from sms_r_system where LastLogonUserName=’$SamAccountName'”

    Thanks for saving me the time of hashing this out myself. 😉

  2. jbmurphy January 23, 2012 at 2:39 pm #

    Thanks for taking the time to reply. I have remove the “rouge comma” ! Glad this was helpful to you.

  3. VertigoRay January 30, 2012 at 1:17 pm #

    Hey Jeffrey,
    I took your function a little farther and back-referenced this post since it was an expansion of your code. Hope you enjoy:
    http://go.vertigion.com/PowerShell-Get-CompByUser

  4. jbmurphy January 30, 2012 at 1:27 pm #

    Sweet! Thanks for taking your time to let me know! I like it when the code I write is helpful.

  5. Ben September 16, 2013 at 2:40 pm #

    You saved my life. Thank you!

  6. Shane July 9, 2014 at 10:24 pm #

    I had to do something similar but ended up writing a custom SCCM Report that took a plain text variable which I then queried from powershell to create an object that was returned for a simple menu to select which would then launch the remote control tool for that computer ID. Why go to such great complication. Answer = Firewall. Powershell goodness….

  7. Armand April 3, 2015 at 6:55 am #

    A bit late to the party, but very useful (saved me some time!)

    I re-used your function for our purposes and edited it a bit (to accept a inputlist with samaccountnames and export the output to a CSV file direclty)

    Thanks for sharing!

  8. thk4this May 28, 2015 at 5:51 pm #

    Thanks, this was quit helpful.
    To make it more useful for me, I changed the Select call to:

    Select-Object Name, @{Name=”Timestamp”;Expression={$_.ConvertToDateTime($_.LastLogonTimestamp)}}

  9. thk4this May 28, 2015 at 5:54 pm #

    oh yeah, and the WMI query to:

    “select Name,LastLogonTimestamp from …