2014/03/23

PowerShell - Find Inactive Computers in Active Directory with ADSI

Today I wanted to retrieve inactive computer accounts in the Active Directory without using the Quest Active Directory Snapin or the Active Directory Module. Yes... It happens that you work on a computer that don't have those tools once in a while, and I thought It would be fun to have a script without requirements...

Note: BTW, the following solution might not be the best or most efficient, so let me know if you know a faster/easier way to do this, I'm willing to learn more about querying AD.

Here are the key element of the script, I want:
  • Computer Inactive for >=90 days
  • Be able to specify a SearchRoot
  • Filter on the Operating System if possible (I want only Windows Servers, without the Domain controllers for example)
  • Return SamAccountName, Name, DN, Operating System, and Description
  • Limit the number of object to return (can be useful for large environment)



[adsisearcher]

I already talked about ADSISearcher in a previous post so I won't give too much details about it. [adsisearcher] type accelerator is used to search Active Directory Domain Services (ADDS)

After some research and tests I quickly got the following line which return the basic information of what I want:
([adsisearcher]"(&(objectcategory=computer)(lastlogontimestamp<=$((Get-Date).AddDays(-105).ToFileTime())))").findall()

Output:
Path                                              Properties
----                                              ----------
LDAP://CN=XAVIERLAPTOP,CN=Computers,DC=FX,DC=LAB  {logoncount, codepage, objectcategory, descrip...
LDAP://CN=LAB1VC01,OU=Servers,OU=TEST,DC=FX,DC... {logoncount, codepage, objectcategory, descrip...
LDAP://CN=LAB1VH02,OU=Servers,OU=TEST,DC=FX,DC... {logoncount, codepage, objectcategory, descrip...
LDAP://CN=LAB1VH01,OU=Servers,OU=TEST,DC=FX,DC... {logoncount, codepage, objectcategory, descrip...
LDAP://CN=DHCP1,CN=Computers,DC=FX,DC=LAB         {logoncount, codepage, objectcategory, descrip...
LDAP://CN=LAB1SQL01,OU=Servers,OU=TEST,DC=FX,D... {logoncount, codepage, objectcategory, descrip...
LDAP://CN=LAB1CM01,CN=Computers,DC=FX,DC=LAB      {logoncount, codepage, objectcategory, descrip...
LDAP://CN=LAB1OR01,OU=Servers,OU=TEST,DC=FX,DC... {logoncount, codepage, objectcategory, descrip...
LDAP://CN=LAB1VC02,OU=Servers,OU=TEST,DC=FX,DC... {logoncount, codepage, objectcategory, descrip...


Next the properties. If we a take look at the list of properties and methods available with this object we might be able to find what we need. We can do this using Get-Member
([adsisearcher]"(&(objectcategory=computer)(lastlogontimestamp<=$((Get-Date).AddDays(-105).ToFileTime())))") | Get-Member

Output:
   TypeName: System.DirectoryServices.DirectorySearcher

Name                      MemberType Definition
----                      ---------- ----------
Disposed                  Event      System.EventHandler Disposed(System.Object, System.EventArgs)
CreateObjRef              Method     System.Runtime.Remoting.ObjRef CreateObjRef(type requestedType)
Dispose                   Method     void Dispose(), void IDisposable.Dispose()
Equals                    Method     bool Equals(System.Object obj)
FindAll                   Method     System.DirectoryServices.SearchResultCollection FindAll()
FindOne                   Method     System.DirectoryServices.SearchResult FindOne()
GetHashCode               Method     int GetHashCode()
GetLifetimeService        Method     System.Object GetLifetimeService()
GetType                   Method     type GetType()
InitializeLifetimeService Method     System.Object InitializeLifetimeService()
ToString                  Method     string ToString()
Asynchronous              Property   bool Asynchronous {get;set;}
AttributeScopeQuery       Property   string AttributeScopeQuery {get;set;}
CacheResults              Property   bool CacheResults {get;set;}
ClientTimeout             Property   timespan ClientTimeout {get;set;}
Container                 Property   System.ComponentModel.IContainer Container {get;}
DerefAlias                Property   System.DirectoryServices.DereferenceAlias DerefAlias {get;set;}
DirectorySynchronization  Property   System.DirectoryServices.DirectorySynchronization DirectorySynchronization {get...
ExtendedDN                Property   System.DirectoryServices.ExtendedDN ExtendedDN {get;set;}
Filter                    Property   string Filter {get;set;}
PageSize                  Property   int PageSize {get;set;}
PropertiesToLoad          Property   System.Collections.Specialized.StringCollection PropertiesToLoad {get;}
PropertyNamesOnly         Property   bool PropertyNamesOnly {get;set;}
ReferralChasing           Property   System.DirectoryServices.ReferralChasingOption ReferralChasing {get;set;}
SearchRoot                Property   adsi SearchRoot {get;set;}
SearchScope               Property   System.DirectoryServices.SearchScope SearchScope {get;set;}
SecurityMasks             Property   System.DirectoryServices.SecurityMasks SecurityMasks {get;set;}
ServerPageTimeLimit       Property   timespan ServerPageTimeLimit {get;set;}
ServerTimeLimit           Property   timespan ServerTimeLimit {get;set;}
Site                      Property   System.ComponentModel.ISite Site {get;set;}
SizeLimit                 Property   int SizeLimit {get;set;}
Sort                      Property   System.DirectoryServices.SortOption Sort {get;set;}
Tombstone                 Property   bool Tombstone {get;set;}
VirtualListView           Property   System.DirectoryServices.DirectoryVirtualListView VirtualListView {get;set;}


Looks like the following properties will do just what we need:
  • SearchRoot (ADSI Object, Distinguished Name of the organization unit) this will be used to specify the root of the search
  • SizeLimit (Integer), to limit the number of object in the output (Can be useful in large environment),
  • PropertiesToLoad (String), to select the properties I want in the output,
  • Filter (String/LDAP Query), to limit the query to computer with a specific Operating System.


$searcher = [adsisearcher]"(&(objectcategory=computer)(lastlogontimestamp<=$((Get-Date).AddDays(-90).ToFileTime())))"
$searcher.searchRoot = [adsi]"LDAP://OU=Servers,OU=TEST,dc=fx,dc=lab"
$searcher.SizeLimit = "5"
$searcher.Filter = "(&(objectCategory=computer)(operatingSystem=*Windows*server*))"
$searcher.PropertiesToLoad.AddRange(('name','samaccountname','cn','operatingsystem','description'))
$searcher.FindAll()

Output:
Name                           Value
----                           -----
samaccountname                 {LAB1HYPE02$}
name                           {LAB1HYPE02}
operatingsystem                {Windows Server 2012 R2 Standard}
cn                             {LAB1HYPE02}
adspath                        {LDAP://CN=LAB1HYPE02,OU=Servers,OU=TEST,DC=FX,DC=LAB}
samaccountname                 {LAB1HYPE01$}
name                           {LAB1HYPE01}
operatingsystem                {Windows Server 2012 R2 Standard}
cn                             {LAB1HYPE01}
adspath                        {LDAP://CN=LAB1HYPE01,OU=Servers,OU=TEST,DC=FX,DC=LAB}
samaccountname                 {LAB1SQL01$}
description                    {SQL2012}
name                           {LAB1SQL01}
cn                             {LAB1SQL01}
operatingsystem                {Windows Server 2012 Standard}
adspath                        {LDAP://CN=LAB1SQL01,OU=Servers,OU=TEST,DC=FX,DC=LAB}
samaccountname                 {LAB1OR01$}
description                    {SCORCH2012}
name                           {LAB1OR01}
cn                             {LAB1OR01}
operatingsystem                {Windows Server 2012 Standard}
adspath                        {LDAP://CN=LAB1OR01,OU=Servers,OU=TEST,DC=FX,DC=LAB}
samaccountname                 {LAB1VC02$}
description                    {VMware vCenter}
name                           {LAB1VC02}
cn                             {LAB1VC02}
operatingsystem                {Windows Server 2012 Standard}
adspath                        {LDAP://CN=LAB1VC02,OU=Servers,OU=TEST,DC=FX,DC=LAB}


The output is poorly formated and we have some extra curly brackets that need to be take care of... Let's fix that by creating a new PowerShell object for each item retrieve by the query.

$searcher = [adsisearcher]"(&(objectcategory=computer)(lastlogontimestamp<=$((Get-Date).AddDays(-90).ToFileTime())))"
$searcher.searchRoot = [adsi]"LDAP://OU=Servers,OU=TEST,dc=fx,dc=lab"
$searcher.SizeLimit = "5"
$searcher.Filter = "(&(objectCategory=computer)(operatingSystem=*server*))"
$searcher.PropertiesToLoad.AddRange(('name','samaccountname','distinguishedname','operatingsystem','description'))
Foreach ($ComputerAccount in $searcher.FindAll()){
    New-Object -TypeName PSObject -Property @{
        Name = $ComputerAccount.properties.name -as [string]
        SamAccountName = $ComputerAccount.properties.samaccountname -as [string]
        DistinguishedName = $ComputerAccount.properties.distinguishedname -as [string]
        OperatingSystem = $ComputerAccount.properties.operatingsystem -as [string]
        Description = $ComputerAccount.properties.description -as [string]
    }
}


DistinguishedName : CN=LAB1HYPE02,OU=Servers,OU=TEST,DC=FX,DC=LAB
Name              : LAB1HYPE02
OperatingSystem   : Windows Server 2012 R2 Standard
Description       : 
SamAccountName    : LAB1HYPE02$

DistinguishedName : CN=LAB1HYPE01,OU=Servers,OU=TEST,DC=FX,DC=LAB
Name              : LAB1HYPE01
OperatingSystem   : Windows Server 2012 R2 Standard
Description       : 
SamAccountName    : LAB1HYPE01$

DistinguishedName : CN=LAB1SQL01,OU=Servers,OU=TEST,DC=FX,DC=LAB
Name              : LAB1SQL01
OperatingSystem   : Windows Server 2012 Standard
Description       : SQL2012
SamAccountName    : LAB1SQL01$

DistinguishedName : CN=LAB1OR01,OU=Servers,OU=TEST,DC=FX,DC=LAB
Name              : LAB1OR01
OperatingSystem   : Windows Server 2012 Standard
Description       : SCORCH2012
SamAccountName    : LAB1OR01$

DistinguishedName : CN=LAB1VC02,OU=Servers,OU=TEST,DC=FX,DC=LAB
Name              : LAB1VC02
OperatingSystem   : Windows Server 2012 Standard
Description       : VMware vCenter
SamAccountName    : LAB1VC02$

That's way better! Neat!



My previous posts on ADSI







Thanks for reading! If you have any questions, leave a comment or send me an email at fxcat@lazywinadmin.com. I invite you to follow me on Twitter @lazywinadm / Google+ / LinkedIn. You can also follow the LazyWinAdmin Blog on Facebook Page and Google+ Page.

28 comments:

  1. That's really awesome FX! i have much to learn with you!

    ReplyDelete
  2. Amazing! that will prove useful this week

    ReplyDelete
  3. Nice !! the next step is to make a loop to read certain columns for each row, until a row exist ..

    ReplyDelete
  4. Thanks ChrisK, $true! It's easily possible, with the only condition that your data will be always in the same column :-)

    ReplyDelete
  5. yep sure, when i'll have time i'll take a look, coz atualy i manualy export to .csv then script import data to a XML .. with this method, i think i can make things easier..

    ReplyDelete
  6. @Xavier what kind of model GPU have you in your esxi ? because this MB don.t have integrated GPU.

    ReplyDelete
  7. Hi Waldek, I used 2 old pci-e GPU that I had. You can probably get something cheap online with a DVI/VGA or hdmi port

    ReplyDelete
  8. Can you please share the Script....? My email id is nandhkumar@hotmail.com

    ReplyDelete
  9. Hi Nanda! Thanks for your comment.

    Actually most of the script is in this post, is there anything I can help you with ?


    Let me know

    ReplyDelete
  10. Hello Francois,
    I just need this script to execute for the remote systems, if you have the script please provide the same...

    ReplyDelete
  11. Hello Francois, I am not good in scripting. The Script that i copied from this post is not working. Could you please share the .ps1 to my email id (nanda.mcts@gmail.com). if you have the remote server option enabled, i would be happy... please help....

    ReplyDelete
  12. Hi Nanda,


    You need Excel installed on the machine that run the script.
    I will post another technique soon that doesn't require Excel.


    Fx

    ReplyDelete
  13. Hello Francois,

    I downloaded the Script as below, But i am unable to run it, if you already have the .ps1 Script, please forward it to my email id. (nanda.mcts@gmail.com). I would be very thankful for this....since i need this.....

    *************************************************************

    $objExcel = New-Object -ComObject Excel.Application
    $WorkBook = $objExcel.Workbooks.Open("C:\VIDEOSERVER01-BuildSpecs.xlsx")
    $objExcel.WorkBooks | Select-Object -Property name, path, author
    $objExcel.WorkBooks | Get-Member
    $WorkBook = $objExcel.Workbooks.Open("C:\VIDEOSERVER01-BuildSpecs.xlsx")
    $WorkBook | Get-Member -Name *sheet*
    $WorkBook.sheets | Select-Object -Property Name
    $WorkSheet = $WorkBook.sheets.item("BuildSpecs")
    # Specify the path to the Excel file and the WorkSheet Name
    $FilePath = "C:\VIDEOSERVER01-BuildSpecs.xlsx"
    $SheetName = "BuildSpecs
    # Create an Object Excel.Application using Com interface
    $objExcel = New-Object -ComObject Excel.Application
    # Disable the 'visible' property so the document won't open in excel
    $objExcel.Visible = $false
    # Open the Excel file and save it in $WorkBook
    $WorkBook = $objExcel.Workbooks.Open($FilePath)
    # Load the WorkSheet 'BuildSpecs'
    $WorkSheet = $WorkBook.sheets.item($SheetName)
    $worksheet.Range("C3").Text
    $worksheet.Range("C3:C3").Text
    $worksheet.Range("C3","C3").Text
    $worksheet.cells.Item(3, 3).text
    $worksheet.cells.Item(3, 3).value2
    $worksheet.Columns.Item(3).Rows.Item(3).Text
    $worksheet.Rows.Item(3).Columns.Item(3).Text
    $worksheet.UsedRange.Range("c3").Text
    $Output = [pscustomobject][ordered]@{
    ComputerName = $WorkSheet.Range("C3").Text
    Project = $WorkSheet.Range("C4").Text
    Ticket = $WorkSheet.Range("C5").Text
    Role = $WorkSheet.Range("C8").Text
    RoleType = $WorkSheet.Range("C9").Text
    Environment = $WorkSheet.Range("C10").Text
    Manufacturer = $WorkSheet.Range("C12").Text
    SiteCode = $WorkSheet.Range("C15").Text
    isDMZ = $WorkSheet.Range("C16").Text
    OperatingSystem = $WorkSheet.Range("C18").Text
    ServicePack = $WorkSheet.Range("C19").Text
    OSKey = $WorkSheet.Range("C20").Text
    Owner = $WorkSheet.Range("C22").Text
    MaintenanceWindow = $WorkSheet.Range("C23").Text
    NbOfProcessor = $WorkSheet.Range("C26").Text
    NbOfCores = $WorkSheet.Range("C27").Text
    MemoryGB = $WorkSheet.Range("C29").Text

    }

    ReplyDelete
  14. Hi Nanda, I just updated my post with an example at the end.
    Additionally i will upload the files tonight on github.


    Hope the piece of code helps for now


    Fx

    ReplyDelete
  15. "doesn't require Excel" that sound good ! i'm waiting for it !!

    ReplyDelete
  16. Hello Francois,
    Could you please share me the Link of the Script that you posted in Github....I still having issues in running the Script. Please help.


    Regards,
    Nandakumar R

    ReplyDelete
  17. Hello Francois,
    Could you please share me the Link of the Script that you posted in Github....I still having issues in running the Script. Please help.

    ReplyDelete
  18. Hello ! i did some test and i'm on the good way i think, but so you have a solution about quit the excel process used to access the file ? coz even with the usual close command from my script, the excel stay in action (see in process manager)..

    ReplyDelete
  19. Hi Nanda,


    That's weird... did you try to download the one from Technet ?
    http://gallery.technet.microsoft.com/Read-Excel-File-using-COM-809deb32


    Also make sure the file is not blocked once dowloaded.


    Hope this help

    ReplyDelete
  20. Good support that helped me to figure out the inactive computers on my domain.

    ReplyDelete
  21. Cláudio Roberto GonçalvesTuesday, May 13, 2014 at 11:13:00 AM EDT

    Very Nice indeed.

    And if I would like to add sometnig like "ConvertTo-HTML" to create a file?

    ReplyDelete
  22. Thanks for the comment Claudio, I actually have a more complex script that move the obsolete computers automatically, and send a daily report.


    This will be posted very soon, I need to polish the code first ;-)

    ReplyDelete
  23. Thanks for sharing your thoughts and suggestion related to find out inactive computers inactive directory environment. I found good active directory cleanup tool from http://www.lepide.com/active-directory-cleaner/ which allows to find out inactive computers and manage disable inactive computer that are currently inactive and move another OU. It helps to generate report which are based on inactive accounts, never logged on users and get accurate information about last logon of accounts.

    ReplyDelete