Trevor Sullivan's Tech Room

Minding the gap between administration and development

Posts Tagged ‘configuration manager’

PowerShell: Get a List of Installed Software from ConfigMgr

Posted by Trevor Sullivan on 2011/12/07


Let’s say you’ve got Microsoft’s System Center Configuration Manager (SCCM / ConfigMgr) in your IT environment (and if you don’t, why on earth not!). If you’re on the desktop management team, you might occasionally get requests from someone on a network or security team, inquiring as to the installed software on a particular client, or group of clients.

Rather than diving straight into the ConfigMgr reports, as most people do, sometimes it’s just faster to load a data set into PowerShell and massage the data from there. Why PowerShell? Well, it provides very easy, real-time filtering and sorting capabilities, and if you need to make a modification to a temporary “report,” you don’t have to worry about modifying the Report object in the ConfigMgr provider, which is typically done through the ConfigMgr console.

Read the rest of this entry »

Advertisements

Posted in configmgr, powershell, scripting | Tagged: , , , , , , , , , , , , , , | 1 Comment »

ConfigMgr 2012 RC Issues with CAS + Primary Hierarchy

Posted by Trevor Sullivan on 2011/11/18


I just got done setting up a ConfigMgr 2012 Release Candidate primary site beneath a Central Administration Site (CAS), and when firing up the console on the primary site, I’m getting the following message: “Your Configuration Manager console is in read-only mode while this site completes tasks related to maintenance mode. After these tasks are complete you must reconnect you Configuration Manager console before you can edit or create new objects.”

Here are a few facts about the hierarchy:

  • The CAS runs on Windows Server 2008 R2 SP1
  • The CAS points to a remote SQL 2008 SP1 Server on Windows Server 2008 R2 SP1
  • The Primary Site runs on Windows Server 2008 R2 SP1
  • The Primary Site points to a local SQL 2008 R2 SP1 instance (with KB2603910 installed)

image

After passing this message box, the following error would appear in the SmsAdminUI.log file:

[5, PID:3140][11/16/2011 20:28:22] :System.Management.ManagementException\r\nGeneric failure \r\n   at System.Management.ManagementException.ThrowWithExtendedInfo(ManagementStatus errorCode)
   at System.Management.ManagementObjectCollection.ManagementObjectEnumerator.MoveNext()
   at Microsoft.ConfigurationManagement.ManagementProvider.WqlQueryEngine.WqlQueryResultsObject.<GetEnumerator>d__0.MoveNext()\r\nManagementException details:
instance of SMS_ExtendedStatus
{
    Description = " Could not find property systemIsolationState";
    ErrorCode = 1078464256;
    File = "e:\\nts_sccm_release\\sms\\siteserver\\sdk_provider\\smsprov\\sspobjectquery.cpp";
    Line = 3900;
    Operation = "ExecQuery";
    ParameterInfo = "Select COUNT(*) FROM SMS_G_System_NAPCLIENT where systemIsolationState=0";
    ProviderName = "WinMgmt";
    StatusCode = 2147749889;
};

 

Obviously something was going on, because this same error was not happening on the CAS when I’d launch the console. We can see in this message that the property named systemIsolationState apparently does not exist in the SMS_G_System_NAPCLIENT WMI class in the root\sms\site_### namespace. Now that we know exactly what’s missing, it’s time to fire up the WMI Explorer and see if that property does or doesn’t exist.

image

As you can see, the property surely does not exist on the “001” site, which is the primary site beneath the CAS. Next, let’s check the CAS for this property.

Note: I’m not sure that this property is necessarily supposed to exist on the CAS, but it’s worth checking out anyway. It’s highly probable that it should exist on the CAS, because it’s a common inventory class for system resources.

Here’s a similar screenshot from the CAS.

image

Aha! It looks like the property does exist on the CAS. At this point, I’m suspecting that something failed during the installation of the primary site, so let’s head back over to the primary site and check out its ConfigMgr setup log (c:\ConfigMgrSetup.log). I saw this message repeating towards the end of the setup:

INFO: Still monitoring Replication initialization.    Configuration Manager Setup    11/16/2011 3:35:38 PM    1168 (0x0490)
INFO: Still monitoring Replication initialization.    Configuration Manager Setup    11/16/2011 3:44:38 PM    1168 (0x0490)
INFO: Still monitoring Replication initialization.    Configuration Manager Setup    11/16/2011 3:53:38 PM    1168 (0x0490)
INFO: Still monitoring Replication initialization.    Configuration Manager Setup    11/16/2011 4:02:38 PM    1168 (0x0490)

And finally, after a while, it finished:

INFO: Stopping component monitoring as stop signal received.    Configuration Manager Setup    11/16/2011 6:52:25 PM    3752 (0x0EA8)
INFO: Stopping server role monitoring as stop signal received.    Configuration Manager Setup    11/16/2011 6:52:26 PM    3836 (0x0EFC)
INFO: Stopping replication monitoring as stop signal received.    Configuration Manager Setup    11/16/2011 6:52:26 PM    1168 (0x0490)
<11-16-2011 18:52:27> *****************************************************         1/1/1601 12:00:00 AM    1992907627 (0x76C95B6B)
<11-16-2011 18:52:27> ***** Exiting ConfigMgr 2012 Setup Bootstrapper *****         1/1/1601 12:00:00 AM    1992907627 (0x76C95B6B)
<11-16-2011 18:52:27> *****************************************************         1/1/1601 12:00:00 AM    1992907627 (0x76C95B6B)

I’m a little confused at how it finished successfully, because there were some other errors in the log as well:

omGetServerRoleAvailabilityState could not read from the registry on sccm03.mybiz.loc; error = 5:
omGetServerRoleAvailabilityState could not read from the registry on sccm03.mybiz.loc; error = 5:

Also this showed up:

INFO: SDK Provider is on sccm03.mybiz.loc.    Configuration Manager Setup    11/16/2011 8:56:25 PM    1748 (0x06D4)
INFO: Retrieving current site control image…    Configuration Manager Setup    11/16/2011 8:56:25 PM    1748 (0x06D4)
INFO:  SQL Connection succeeded. Connection: SMS ACCESS, Type: Secure    Configuration Manager Setup    11/16/2011 8:56:25 PM    1748 (0x06D4)
INFO: Stored SQL Server computer certificate for Server [sccm01.mybiz.loc] successfully on [sccm03.mybiz.loc].    Configuration Manager Setup    11/16/2011 8:56:25 PM    1748 (0x06D4)
CSql Error: Cannot find type data, cannot get a connection.    Configuration Manager Setup    11/16/2011 8:56:25 PM    1748 (0x06D4)
ERROR:  SQL Connection failed. Connection: CCAR_DB_ACCESS, Type: Unsecure    Configuration Manager Setup    11/16/2011 8:56:25 PM    1748 (0x06D4)
CSql Error: Cannot find type data, cannot get a connection.    Configuration Manager Setup    11/16/2011 8:56:28 PM    1748 (0x06D4)
ERROR:  SQL Connection failed. Connection: CCAR_DB_ACCESS, Type: Unsecure    Configuration Manager Setup    11/16/2011 8:56:28 PM    1748 (0x06D4)
CSql Error: Cannot find type data, cannot get a connection.    Configuration Manager Setup    11/16/2011 8:56:31 PM    1748 (0x06D4)
ERROR:  SQL Connection failed. Connection: CCAR_DB_ACCESS, Type: Unsecure    Configuration Manager Setup    11/16/2011 8:56:31 PM    1748 (0x06D4)
CSql Error: Cannot find type data, cannot get a connection.    Configuration Manager Setup    11/16/2011 8:56:34 PM    1748 (0x06D4)
ERROR:  SQL Connection failed. Connection: CCAR_DB_ACCESS, Type: Unsecure    Configuration Manager Setup    11/16/2011 8:56:34 PM    1748 (0x06D4)
CSql Error: Cannot find type data, cannot get a connection.    Configuration Manager Setup    11/16/2011 8:56:37 PM    1748 (0x06D4)
ERROR:  SQL Connection failed. Connection: CCAR_DB_ACCESS, Type: Unsecure    Configuration Manager Setup    11/16/2011 8:56:37 PM    1748 (0x06D4)
CSql Error: Cannot find type data, cannot get a connection.    Configuration Manager Setup    11/16/2011 8:56:40 PM    1748 (0x06D4)
ERROR:  SQL Connection failed. Connection: CCAR_DB_ACCESS, Type: Unsecure    Configuration Manager Setup    11/16/2011 8:56:40 PM    1748 (0x06D4)
CSql Error: Cannot find type data, cannot get a connection.    Configuration Manager Setup    11/16/2011 8:56:43 PM    1748 (0x06D4)
ERROR:  SQL Connection failed. Connection: CCAR_DB_ACCESS, Type: Unsecure    Configuration Manager Setup    11/16/2011 8:56:43 PM    1748 (0x06D4)
CSql Error: Cannot find type data, cannot get a connection.    Configuration Manager Setup    11/16/2011 8:56:46 PM    1748 (0x06D4)
ERROR:  SQL Connection failed. Connection: CCAR_DB_ACCESS, Type: Unsecure    Configuration Manager Setup    11/16/2011 8:56:46 PM    1748 (0x06D4)
CSql Error: Cannot find type data, cannot get a connection.    Configuration Manager Setup    11/16/2011 8:56:49 PM    1748 (0x06D4)
ERROR:  SQL Connection failed. Connection: CCAR_DB_ACCESS, Type: Unsecure    Configuration Manager Setup    11/16/2011 8:56:49 PM    1748 (0x06D4)
CSql Error: Cannot find type data, cannot get a connection.    Configuration Manager Setup    11/16/2011 8:56:52 PM    1748 (0x06D4)
ERROR:  SQL Connection failed. Connection: CCAR_DB_ACCESS, Type: Unsecure    Configuration Manager Setup    11/16/2011 8:56:52 PM    1748 (0x06D4)
INFO: Registered type CCAR_DB_ACCESS for sccm01.mybiz.loc CM_CEN    Configuration Manager Setup    11/16/2011 8:56:55 PM    1748 (0x06D4)
INFO:  SQL Connection succeeded. Connection: CCAR_DB_ACCESS, Type: Unsecure    Configuration Manager Setup    11/16/2011 8:56:55 PM    1748 (0x06D4)
INFO: Read CAS SQL Server information, stored CAS SQL Server certificate and registered connection to its database.    Configuration Manager Setup    11/16/2011 8:56:55 PM    1748 (0x06D4)

Someone else posted this same message when trying to do a CAS + Primary Site configuration. Next step: reinstall the primary site.

After re-installing the primary site, the same issues returned. Finally I decided to just install a stand-alone primary site without a CAS.

Posted in configmgr, ConfigMgr vNext, fixes | Tagged: , , , , , , , , | 8 Comments »

ConfigMgr 2012 Beta 2: WMI Namespace Documentation

Posted by Trevor Sullivan on 2011/08/02


I recently put together some documentation for the Microsoft System Center Configuration Manager 2012 Beta 2 WMI provider. This is a searchable, formatted Excel document that displays all the classes, properties, and methods for the SCCM 2012 provider. Hopefully this will help you to find the proper information for writing custom scripts and so on.

http://dl.dropbox.com/u/18088468/SCCM%202012%20WMI%20Namespace%20Documentation.xlsx

Please provide feedback if this was helpful, or if you’d like to see something else added to it!

Posted in configmgr, ConfigMgr vNext, tools, wmi | Tagged: , , , , , , , , , , | Leave a Comment »

ConfigMgr 2012: Deleting Advertisements

Posted by Trevor Sullivan on 2011/07/06


Where’d my Advertisements Go?

Advertisements aren’t quite what you think they are. At least, that’s probably the case if you’re talking in context of Microsoft System Center Configuration Manager (SCCM / ConfigMgr). At first glance you might see “deleting advertisements” and exclaim: “YES! Please do!” The difference here is that advertisements in SCCM are not product or service advertisements, but rather proclamations of the availability of software to a computer system. If you’re reading this, you most likely already knew that, but if not, then … now you do.

Back in the days of the SCCM 2007 console, there was a single Software Distribution –> Advertisements node which contained advertisements for both standard software distribution and operating system deployment (OSD) task sequences. This worked pretty well, but it was a little confusing since the operating system deployment node was entirely separate from software distribution. Hierarchically, it just didn’t make sense.

image

Read the rest of this entry »

Posted in configmgr, ConfigMgr vNext | Tagged: , , , , , , , , , , , , , , , , | Leave a Comment »

Extreme PowerShell / ConfigMgr: Extending Hardware Inventory

Posted by Trevor Sullivan on 2011/07/05


Introduction

In previous versions of Microsoft System Center Configuration Manager (ConfigMgr / SCCM), a common task for administrators, engineers, and consultants, was to extend the hardware inventory configuration. These inventory extensions were written in Managed Object Format (MOF) and allowed the SCCM client agents to report back a wider array of information to the central site database for reporting purposes, collection building, and other management tasks. Making changes to the configuration could be a tedious task, as MOF is not very forgiving, and rather quite strict, in its syntax.

In Microsoft Systems Management Server 2003 (SMS 2003), each time a configuration change was made, it was necessary to deploy the updated MOF file to the SMS clients — this made ensuring hardware inventory consistency across all clients a challenging task. In SCCM, Microsoft included changes to these MOF files (SMS_DEF.mof and Configuration.mof) as part of the machine policy refresh task, which is a client-side polling mechanism for configuration changes.

In SCCM 2012 Beta 2, Microsoft is taking it a step further and has eliminated the SMS_DEF.mof altogether, left the configuration.mof behind by itself, and stuck the WMI inventory configuration in … WMI. What is WMI? WMI stands for Windows Management Instrumentation, a service built into the Windows Operating System since Windows XP (and Windows 2000 Service Pack 4, I think). It provides a standard method of exposing hardware and software level system information to applications, such as storage, processor, memory, running processes, installed software, and other application configuration data. SCCM is built on top of this technology, and often makes developing software and scripts around the product much easier than it otherwise might be.

For the remainder of this article, we’re going to look at specifically how to extend hardware inventory in SCCM 2012 programmatically using Windows PowerShell with the SCCM WMI provider.

Read the rest of this entry »

Posted in .NET, configmgr, ConfigMgr vNext, powershell, scripting, tools, wmi | Tagged: , , , , , , , , , , , , , , , | 2 Comments »

ConfigMgr 2012 Beta 2: Setting the Network Access Account

Posted by Trevor Sullivan on 2011/06/28


You might need to set the Network Access Account (NAA) in System Center Configuration Manager 2012 Beta 2 if you are deploying operating systems with it. Windows Pre-Execution (WinPE) needs an account to access content on the distribution point. In order to set the network access account, open the SCCM console and navigate to this node: \Administration\Overview\Site Operations\Sites. Right-click on the SCCM site in the right-hand pane, and select Software Distribution. Click on the Network Access Account tab, and set the account you want to use.

image

image

Posted in configmgr, ConfigMgr vNext, OSD | Tagged: , , , , , , , , | 1 Comment »

ConfigMgr 2012: Client Side Software Center

Posted by Trevor Sullivan on 2011/06/15


Introduction

The System Center Configuration Manager 2012 Beta 2 client agent has been updated significantly. One of the things that has been changed a lot is the client-side interface that allows an end-user to select optional software to install, scripts to run, or operating systems to deploy.

In previous versions of ConfigMgr, there was a Control Panel applet called “Run Advertised Programs.” A user would open this in order to browse the list of optional program made available by a ConfigMgr administrator. There was another control panel applet called “Program Download Monitor” that would allow an end-user to monitor the download status of a program – admittedly, this was probably hardly ever touched by an end-user, realistically.

Read the rest of this entry »

Posted in configmgr, ConfigMgr vNext | Tagged: , , , , , , , , , | 3 Comments »

ConfigMgr 2012 Beta 2: Setup Doesn’t Like Spaces in Prerequisite Path

Posted by Trevor Sullivan on 2011/05/09


During the course of playing with ConfigMgr 2012 Beta 2, I noticed a bug in setup. It appears to not like it when you put spaces in the path to the prerequisite files that you’d pre-download using setupdl.exe. If you have spaces in the path, and try to click Next, it won’t proceed to the next screen. Removing the spaces from the folder name immediately seems to work – I noticed this behavior a couple of different times.

image

Posted in configmgr, ConfigMgr vNext | Tagged: , , , , , | Leave a Comment »

ConfigMgr 2012 Beta 2: Active Directory Schema Changes

Posted by Trevor Sullivan on 2011/05/09


For reference, here is the ExtADSch.log file from my ConfigMgr 2012 Beta 2 lab setup. I don’t see any glaring changes from ConfigMgr 2007, though I don’t have a 2007 log to compare it to.

<05-09-2011 15:00:02> Modifying Active Directory Schema – with SMS extensions.
<05-09-2011 15:00:02> DS Root:CN=Schema,CN=Configuration,DC=mybiz,DC=loc
<05-09-2011 15:00:03> Defined attribute cn=MS-SMS-Site-Code.
<05-09-2011 15:00:03> Defined attribute cn=mS-SMS-Assignment-Site-Code.
<05-09-2011 15:00:03> Defined attribute cn=MS-SMS-Site-Boundaries.
<05-09-2011 15:00:03> Defined attribute cn=MS-SMS-Roaming-Boundaries.
<05-09-2011 15:00:03> Defined attribute cn=MS-SMS-Default-MP.
<05-09-2011 15:00:03> Defined attribute cn=mS-SMS-Device-Management-Point.
<05-09-2011 15:00:03> Defined attribute cn=MS-SMS-MP-Name.
<05-09-2011 15:00:03> Defined attribute cn=MS-SMS-MP-Address.
<05-09-2011 15:00:03> Defined attribute cn=mS-SMS-Health-State.
<05-09-2011 15:00:03> Defined attribute cn=mS-SMS-Source-Forest.
<05-09-2011 15:00:03> Defined attribute cn=MS-SMS-Ranged-IP-Low.
<05-09-2011 15:00:03> Defined attribute cn=MS-SMS-Ranged-IP-High.
<05-09-2011 15:00:03> Defined attribute cn=mS-SMS-Version.
<05-09-2011 15:00:03> Defined attribute cn=mS-SMS-Capabilities.
<05-09-2011 15:00:04> Defined class cn=MS-SMS-Management-Point.
<05-09-2011 15:00:05> Defined class cn=MS-SMS-Server-Locator-Point.
<05-09-2011 15:00:05> Defined class cn=MS-SMS-Site.
<05-09-2011 15:00:05> Defined class cn=MS-SMS-Roaming-Boundary-Range.
<05-09-2011 15:00:05> Successfully extended the Active Directory schema.

<05-09-2011 15:00:05> Please refer to the ConfigMgr documentation for instructions on the manual
<05-09-2011 15:00:05> configuration of access rights in active directory which may still
<05-09-2011 15:00:05> need to be performed.  (Although the AD schema has now be extended,
<05-09-2011 15:00:05> AD must be configured to allow each ConfigMgr Site security rights to
<05-09-2011 15:00:05> publish in each of their domains.)

Posted in configmgr, ConfigMgr vNext | Tagged: , , , , , , | Leave a Comment »

PowerShell: AD / SCCM Workstation Cleanup Script Version 3.0

Posted by Trevor Sullivan on 2010/08/17


I just realized, I still haven’t posted the script that removes SCCM resources, alongside Active Directory cleanup. I had written a version 3.0 of a script I previously posted, but never posted it. So, here it is (I haven’t tested it in a while):

Disclaimer: I am not responsible for what you do with this script.

Update (2010-08-18): Shay Levy (PowerShell MVP) has noted that using Replace(“/”, “-“) will not work in all cultures. Rather, he suggests using this format: (Get-Date -f ‘M-d-yyyy hMMss tt’)

##############################################################################
#
# Author: Trevor Sullivan
#
# Date: October 28, 2009
#
# Lessons learned:
#
# 1. ADSI property names are lower case using DirectorySearcher or DirectoryEntry
# 2. Must explicitly cast 64-bit integers from AD
# 3. The Excel API is terrible (already knew that)
#
#
# Change Log:
#    2009-11-06
#        -Added: function to delete objects from SCCM (untested)
#        -Added: User variables at top of script to ease usage
#        -Added: function to auto-detect SCCM site code, based on server name
#        -Added: Windows Vista accounts to search criteria
#        -Fixed: Replaced -bxor operator with -bor to prevent computer accounts
#                from being re-enabled
#        -Fixed: Casted [void] from loading Excel Interop assembly to prevent
#                Assembly object from being written to pipeline
#
##############################################################################


### Populate these variables please. ###
$ExcelLog = $Env:USERPROFILE + '\' + (Get-Date).ToString().Replace("/","-").Replace(":","") + " AD Workstation Cleanup.xlsx" # Full path to save Excel log to
$TargetDn = 'cn=computers,dc=ts,dc=loc' # Top-level distinguishedName 
$DisabledDn    = 'ou=Disabled,ou=Workstations,dc=ts,dc=;pc' # OU to place disabled accounts into
$DisableAge = 60 # Age (in days) of computer account, to be disabled
$DeleteAge = 30 # Age of computer account (<DisabledDate> + X) to delete computer accounts
$SccmServer    = 'sccm01' # The server on which your SMS Provider component is installed
$BreakStuff = $false # If set to $true, the script WILL TAKE ACTION!
$Debug = $false # Enables additional logging to file and stdout
###  ### ############################# ###  ###

function DisableOldAccounts(${TargetDn}, ${DisableAge} = 60)
{
    ${Computers} = GetComputerList ${TargetDn}

    foreach (${Computer} in ${Computers})
    {
        # PwdLastSet is a 64-bit integer that indicates the number of 100-nanosecond intervals since 12:00 AM January 1st, 1601
        # The FromFileTime method converts a 64-bit integer to datetime
        # http://www.rlmueller.net/Integer8Attributes.htm
        ${PwdLastSet} = [DateTime]::FromFileTime([Int64]"$(${Computer}.Properties['pwdlastset'])")
        ${CompAge} = ([DateTime]::Now - $PwdLastSet).Days
        if (${CompAge} -gt ${DisableAge})
        {
            LogMessage "$($Computer.Properties['cn']) age is ${CompAge}. Account will be disabled" 2
            WriteDisabledEntry $Computer.Properties['cn'].Item(0) $CompAge $Computer.Properties['distinguishedname'].Item(0) $DisabledDn
            DisableAccount $Computer.Properties['distinguishedname'].Item(0)
        }
        else
        {
            LogMessage "$($Computer.Properties['cn'].Item(0)) age is ${CompAge}, $($Computer.Properties['pwdlastset'].Item(0)), ${PwdLastSet}" 1
        }
    }
}

# Gets a full list of computer accounts from the target distinguishedName defined at the top of the script
function GetComputerList($TargetDn)
{
    # Define the LDAP search syntax filter to locate workstation objects.
    # See this link for info: http://msdn.microsoft.com/en-us/library/aa746475(VS.85).aspx
    ${tFilter} = '(&(objectClass=computer)(|(operatingSystem=Windows 2000 Professional)(operatingSystem=Windows XP*)(operatingSystem=*Vista*)(operatingSystem=Windows 7*)))'

    # Create a DirectorySearcher using filter defined above
    ${Searcher} = New-Object System.DirectoryServices.DirectorySearcher $tFilter
    # Set the search root to the distinguishedName specified in the function parameter
    ${Searcher}.SearchRoot = "LDAP://${TargetDn}"
    # Search current container and all subcontainers
    ${Searcher}.SearchScope = [System.DirectoryServices.SearchScope]::Subtree
    # See this link for info on why this next line is necessary: http://www.eggheadcafe.com/software/aspnet/32967284/searchall-in-ad-ldap-f.aspx
    ${Searcher}.PageSize = 1000
    $Results = $Searcher.FindAll()
    LogMessage "Found $($Results.Count) computer accounts to evaluate for disablement" 1
    return $Results
}

# Set description on computer account, disable it, and move it to the Disabled OU
function DisableAccount($dn)
{
#    LogMessage "DisableAccount method called with param: ${dn}" 1
    # Get a reference to the object at <distinguishedName>
    $comp = [adsi]"LDAP://${dn}"
    # Disable the account
#    LogMessage "userAccountControl ($($comp.Name)) is: $($comp.userAccountControl)"
    $comp.userAccountControl = $comp.userAccountControl.Value -bor 2
    # Write the current date to the description field
    if ($comp.Description -ne '') { LogMessage "Description attribute of ($comp.Name) is set to: $($comp.Description)" 2 }
    $comp.Description = "$(([DateTime]::Now).ToShortDateString())"

    # Uncomment these lines to write changes to Active Directory
    if ($BreakStuff)
    {
        [Void] $comp.SetInfo()
        $comp.psbase.MoveTo("LDAP://${DisabledDn}")
    }
}

# Parameter ($DeleteAge): Days from disable date to delete computer account
function DeleteDisabledAccounts($DeleteAge)
{
    # Get reference to OU for disabled workstation accounts
    ${DisabledOu} = [adsi]"LDAP://${DisabledDn}"

    ${Searcher} = New-Object System.DirectoryServices.DirectorySearcher '(objectClass=computer)'
    ${Searcher}.SearchRoot = ${DisabledOu}
    ${Searcher}.SearchScope = [System.DirectoryServices.SearchScope]::Subtree
    # Page size is used to return result count > default size limit on domain controllers.
    # See: http://geekswithblogs.net/mnf/archive/2005/12/20/63581.aspx
    ${Searcher}.PageSize = 1000
    LogMessage "Finding computers to evaluate for deletion in container: ${DisabledDn}" 1
    ${Computers} = ${Searcher}.FindAll()

    foreach (${Computer} in ${Computers})
    {
        ${DisableDate} = [DateTime]::Parse(${Computer}.Properties['description'])
        trap {
            LogMessage "Couldn't parse date for $($Computer.Properties['cn'])" 3
            continue
        }
        ${CurrentAge} = ([DateTime]::Now - ${DisableDate}).Days
        if (${CurrentAge} -gt ${DeleteAge})
        {
            LogMessage "$(${Computer}.Properties['cn']) age is ${CurrentAge} and will be deleted" 2
            WriteDeletedEntry $Computer.Properties['cn'].Item(0) $CurrentAge $Computer.Properties['distinguishedname'].Item(0) "Note"
            if ($BreakStuff)
            {
                $DisabledOu.Delete('computer', 'CN=' + ${Computer}.Properties['cn'])
            }
            RemoveFromSccm ${Computer}.Properties['cn'] $SccmServer
        }
        else
        {
            LogMessage "$(${Computer}.Properties['cn']) age is ${CurrentAge} and will not be deleted" 1
        }
    }
}

# Purpose: This function deletes a resource from the Configuration Manager database
function RemoveFromSccm($tPcName, $tSiteServer)
{
    $tSysQuery = "select * from SMS_R_System where Name = '$tPcName'"
    $tWmiNs = "root\sms\site_" + $Global:SccmSiteCode
    if ($Debug)
    {
        LogMessage "Site code is: $Global:SccmSiteCode" 1
        LogMessage $tSysQuery 1
        LogMessage "tSiteServer is: $tSiteServer" 1
        LogMessage "tWmiNs is: $tWmiNs" 1
    }
    $Resources = Get-WmiObject -ComputerName $tSiteServer -Namespace $tWmiNs -Query $tSysQuery

    if ($Resources -eq $null) { return; }

    foreach ($Resource in $Resources)
    {
        $AgentTime = $($Resource.AgentTime | Sort-Object | Select-Object -Last 1)
        $UserName = $Resource.LastLogonUserDomain + '\' + $Resource.LastLogonUserName
        # Log the deleted SCCM resource to the Excel log
        WriteSccmDeletionEntry $Resource.ResourceID $Resource.Name $AgentTime $UserName
        # This line deletes records from the ConfigMgr database
        if ($BreakStuff)
        {
            # Delete the resource from the ConfigMgr site server
            $resource.Delete()
        }
    }
}

# Purpose: This function looks up the site code for the SMS Provider, given a server name
function GetSiteCode($tSiteServer)
{
    # Dynamically obtain SMS provider location based only on server name
    $tSiteCode = (Get-WmiObject -ComputerName $tSiteServer -Class SMS_ProviderLocation -Namespace root\sms).NamespacePath
    # Return only the last 3 characters of the NamespacePath property, which indicates the site code
    return $tSiteCode.SubString($tSiteCode.Length - 3).ToLower()
}

# This function logs a message to the console and a log file.
# Params:
#    $tMessage = A string representing the message to be logged to the file & console
#    $Severity = A integer from 1-3 representing the severity of the message: Info, Warning, Error
function LogMessage(${tMessage}, ${Severity})
{
    switch(${Severity})
    {
        1 {
            $LogPrefix = "INFO"
            $fgcolor = [ConsoleColor]::Blue
            $bgcolor = [ConsoleColor]::White
        }
        2 {
            $LogPrefix = "WARNING"
            $fgcolor = [ConsoleColor]::Black
            $bgcolor = [ConsoleColor]::Yellow
        }
        3 {
            $LogPrefix = "ERROR"
            $fgcolor = [ConsoleColor]::Yellow
            $bgcolor = [ConsoleColor]::Red
        }
        default {
            $LogPrefix = "DEFAULT"
            $fgcolor = [ConsoleColor]::Black
            $bgcolor = [ConsoleColor]::White
        }
    }

    if ($Debug)
    {
        Add-Content -Path "AD-Workstation-Cleanup.log" -Value "$((Get-Date).ToString()) ${LogPrefix}: ${tMessage}"
        Write-Host -ForegroundColor $fgcolor -BackgroundColor $bgcolor -Object "$((Get-Date).ToString()) ${LogPrefix}: ${tMessage}"
    }
}

function SetupExcel()
{
    LogMessage "Setting up Excel logging" 1
    [void] [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Interop.Excel")
    $Global:Excel = New-Object Microsoft.Office.Interop.Excel.ApplicationClass
    $Excel.Visible = $true
    $Global:Workbook = $Excel.Workbooks.Add()

    # Setup worksheet for deleted SCCM resource records
    $Global:SccmResourceLog = $Workbook.Worksheets.Item("Sheet3")
    $SccmResourceLog.Name = "SCCM Resources"
    $SccmResourceLog.Tab.ThemeColor = [Microsoft.Office.Interop.Excel.XlThemeColor]::xlThemeColorAccent3
    $SccmResourceLog.Cells.Item(1, 1).Value2 = "Date"
    $SccmResourceLog.Cells.Item(1, 2).Value2 = "Resource ID"
    $SccmResourceLog.Cells.Item(1, 3).Value2 = "Name"
    $SccmResourceLog.Cells.Item(1, 4).Value2 = "Last Agent Time"
    $SccmResourceLog.Cells.Item(1, 5).Value2 = "Username"
    $Global:tSccmResRow = 2

    # Setup worksheet for disabled accounts
    $Global:DisabledLog = $Workbook.Worksheets.Item("Sheet2")
    $DisabledLog.Tab.ThemeColor = [Microsoft.Office.Interop.Excel.XlThemeColor]::xlThemeColorAccent2
    $DisabledLog.Name = "Disabled"
    $DisabledLog.Cells.Item(1, 1).Value2 = "Date"
    $DisabledLog.Cells.Item(1, 2).Value2 = "Name"
    $DisabledLog.Cells.Item(1, 3).Value2 = "Age"
    $DisabledLog.Cells.Item(1, 4).Value2 = "Source Container"
    $DisabledLog.Cells.Item(1, 5).Value2 = "Destination Container"
    $Global:tDisabledRow = 2

    # Setup worksheet for deleted accounts log
    $Global:DeletedLog = $Workbook.Worksheets.Item("Sheet1")
    $DeletedLog.Tab.ThemeColor = [Microsoft.Office.Interop.Excel.XlThemeColor]::xlThemeColorAccent5
    $DeletedLog.Name = "Deleted"
    $DeletedLog.Cells.Item(1, 1).Value2 = "Date"
    $DeletedLog.Cells.Item(1, 2).Value2 = "Name"
    $DeletedLog.Cells.Item(1, 3).Value2 = "Age"
    $DeletedLog.Cells.Item(1, 4).Value2 = "DN"
    $DeletedLog.Cells.Item(1, 5).Value2 = "Note"
    $Global:tDeletedRow = 2
}

# Writes an entry to the global variable used to reference the log for disabled accounts
function WriteDisabledEntry([string] $tName, $tAge, [string] $tSourceDn, [string] $tDestinationDn)
{
    #LogMessage "Writing disabled computer to Excel log: $tName" 1
    #Write-Host "Value of tname is $tName"
    #Write-Host "Value of tage is $tAge"
    #Write-Host "Value of tsourcedn is $tSourceDn"
    #Write-Host "Value of tDestinationDn is $tDestinationDn"
    $tArrContainer = $tSourceDn.Split(",")
    $tContainer = [string]::Join(",", ($tArrContainer | select -Last ($tArrContainer.Length - 1)))
    $DisabledLog.Cells.Item($tDisabledRow, 1).Value2 = [DateTime]::Now.ToString()
    $DisabledLog.Cells.Item($tDisabledRow, 2).Value2 = $tName
    $DisabledLog.Cells.Item($tDisabledRow, 3).Value2 = $tAge
    $DisabledLog.Cells.Item($tDisabledRow, 4).Value2 = $tContainer
    $DisabledLog.Cells.Item($tDisabledRow, 5).Value2 = $tDestinationDn
    $Global:tDisabledRow++
}

# Writes an entry to the global variable used to reference the log for deleted accounts 
function WriteDeletedEntry($tName, $tAge, $tDN, $tNote)
{
    #LogMessage "Writing deleted computer to Excel log: $tName" 1
    #Write-Host "Value of tName is $tName"
    #Write-Host "Value of tAge is $tAge"
    #Write-Host "Value of tDN is $tDN"
    #Write-Host "Value of tNote is $tNote"
    $DeletedLog.Cells.Item($tDeletedRow,1).Value2 = [DateTime]::Now.ToString()
    $DeletedLog.Cells.Item($tDeletedRow,2).Value2 = $tName.ToString()
    $DeletedLog.Cells.Item($tDeletedRow,3).Value2 = $tAge.ToString()
    $DeletedLog.Cells.Item($tDeletedRow,4).Value2 = $tDN.ToString()
    $DeletedLog.Cells.Item($tDeletedRow,5).Value2 = $tNote.ToString()
    $Global:tDeletedRow++
    return
}

function WriteSccmDeletionEntry($tResourceId, $tName, $tLastAgentTime, $tUserName)
{
    $SccmResourceLog.Cells.Item($tSccmResRow, 1).Value2 = [DateTime]::Now.ToString()
    $SccmResourceLog.Cells.Item($tSccmResRow, 2).Value2 = $tResourceId
    $SccmResourceLog.Cells.Item($tSccmResRow, 3).Value2 = $tName
    $SccmResourceLog.Cells.Item($tSccmResRow, 4).Value2 = $tLastAgentTime
    $SccmResourceLog.Cells.Item($tSccmResRow, 5).Value2 = $tUserName
    $Global:tSccmResRow++
    return
}

function CloseExcel()
{
    # AutoFit the columns

    foreach ($tSheet in $Workbook.Worksheets)
    {
        $tSheet.Activate()
        [Void] $Excel.ActiveCell.CurrentRegion.Columns.AutoFit()
        [Void] $Excel.ActiveCell.CurrentRegion.Select()
        $Global:ListObject = $Excel.ActiveSheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $Excel.ActiveCell.CurrentRegion, $null ,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes)
        $ListObject.Name = "TableData"
        $ListObject.TableStyle = "TableStyleLight9"
    }

    LogMessage "Saving and closing Excel workbook" 1
    $Global:Workbook.SaveAs($ExcelLog)
    $Global:Excel.Quit()
}

function Main()
{
    Clear-Host
    LogMessage "Beginning workstation account cleanup script" 1

    # Retrieve SCCM site code from site server specified by user
    $Global:SccmSiteCode = GetSiteCode $SccmServer

    # Setup Excel logging
    SetupExcel

    # Delete accounts that have been disabled for X days
    DeleteDisabledAccounts $DeleteAge

    # Disable accounts that are older than X days
    DisableOldAccounts $TargetDn $DisableAge

    CloseExcel
    LogMessage "Completed workstation account cleanup script" 1
}

Main

Posted in configmgr, powershell, scripting, tools, wmi | Tagged: , , , , , , , , , | 11 Comments »