[SCCM 2012/2016]: Query SCCM to find clients a user last logged in to using PowerShell

Summary

Query SCCM to query the ConfigMgr database to find which clients a particular user had logged in to using PowerShell

Issue

In a previous article I discussed how to use PowerShell to find which clients a user had logged into using the in-built tools in SCCM.

The issue with this approach as asked in the comments of the post are that what happens if you want to query based on an external input file from another system?

One way would be to create a CSV and populate a collection, then run the same query against that collection. This seems like quite a laborious task to do on a regular basis.

Another approach however would be to use PowerShell to talk to SCCM to query the data required,

In this example
$colComputerNames
is a collection of objects.

This object can be populated and cast as required. In order to simplify the process in code this operation has been split into 3 sections

1) Get input

2) Clean-up input to only capture host names of devices

3) Format into SQL Query language

Implementation

# Script to Query SCCM for last logged on user from a list of machines
# Copyright (C) 2017  Adil Dean
# Script Name: SCCMSQLQuery.ps1
# Script Version: 1.0
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
#(at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see .
#
# ============================================================================
# Change History:
# 1.0 - 05/10/2017 - Adil Dean
#	- Script created
#
# ============================================================================
# Known Bugs/Feature requests:
# - No validation of parameters,invalid entries will cause script to fail
# ============================================================================
# Requirements:
# - Script is not signed (requires necessary execution policy)
#
# ============================================================================


# Parameters
$strSiteCode=""               # Site Code
$strSCCMServer=""  # SCCM Server querying against


# Variables
$strNameSpace="root\sms\site_$strSiteCode"    # Root for site NameSpace
$strComputerNames = $null    # initialise computer names variable

# ============================================================================
# Get input data
# ============================================================================
$colComputerNames = ("computer1","computer2","computer3")

# ============================================================================
# Cleanup input and format into SQL Query language
# ============================================================================
Foreach($strCurrentComputerName in $colComputerNames){
    # Add code to cleanup input here
    
    # Add single quotes around each entry
    $strComputerNames = $strComputerNames + " OR SMS_R_System.Name='" + $strCurrentComputerName + "'"
}
# Remove first ' OR ' from line as it is not required
$strComputerNames = $strComputerNames.TrimStart(' OR ')

# ============================================================================
# Build SQL Query
# ============================================================================
$strSQLQuery = `
    "SELECT SMS_R_System.LastLogonUserName, SMS_R_System.Name, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonTimestamp `
    FROM  SMS_R_System `
    WHERE $strComputerNames"

# ============================================================================
# Execute SQL Query
# ============================================================================
Get-WmiObject -namespace $strNameSpace -computer $strSCCMServer -query $strSQLQuery | select Name, LastLogonUserName

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s