Anderson at myITforum

Part IV : Move Computers to OU based on IP Subnets [ http://ande.in ]

andersonk — Wed, 31 Mar 2010 09:52:12 GMT

To automate the movement of computers in the Default Computers Container to the Location OUs based on their IP Subnets defined in the Active Directory Sites and Subnets

Source OU

ande.in/Computers

Destination OUs

ande.in/Asia/India/Computers

ande.in/Asia/Japan/Computers

ande.in/Europe/Italy/Computers

ande.in/North America/Canada/Computers

ande.in/South America/Argentina/Computers

Note :

1. We are going to use 3 Functions

Find-Site_Move and Get-Site_Move : Used to identify Subnet and Site Details. Credit goes to /\/\o\/\/

Split-Job : Used to speed up the script execution. So the script will execute 10 times faster. Credit goes to Arnoud Jansveld

2. Quest Active Directory cmdlets : Get Active directory data

3. Script execution time approx 100000 computers = 1 hour 30 mins

4. Modifications required in script:

$FileName : Choose path for the output file. [Default : C:\AD_Data_Move_16022010.log]

$Log : Choose path for the log file. [Default : C:\Log_16022010.log]

“SiteCode_India" Change the respective Site Code as in Active Directory Sites and Services

"ande.in/Asia/India/Computers” Change the respective OU

     add-pssnapin Quest.ActiveRoles.ADManagement

    $Log = Get-Date -uformat "C:\Log_%d%m%Y.log"

    ######################

    $Date_Start = Get-Date

    echo "$Date_Start : Finding Respective Site Code for All Computers....." | Out-File -Append -FilePath $Log

    ######################

    ##### Finding Respective Site Code for All Computers and Output to CSV

    Function Find-Site_Move { 

      param ([net.ipAddress]$ip) 

      for ($bit = 30 ; $bit -ge 1; $bit--){ 

        [int]$octet = [math]::Truncate(($bit - 1 ) / 8) 

        $net = [byte[]]@() 

        for($o=0;$o -le 3;$o++) { 

          $ba = $ip.GetAddressBytes() 

          if ($o -lt $Octet) { 

            $Net += $ba[$o] 

          }ELSEIF ($o -eq $octet) { 

            $factor = 8 + $Octet * 8 - $bit 

            $Divider = [math]::pow(2,$factor) 

            $value = $divider * [math]::Truncate($ba[$o] /  $divider) 

            $Net += $value 

          }ELSE { 

            $Net += 0 

          } 

        } 

        $NetWork = [string]::join('.',$net) + "/$bit" 

        if ($verbose.IsPresent) {write-host -fore 'green' "Trying : $network"} 

        $de = New-Object directoryservices.directoryentry('LDAP://rootDSE') 

        $Root = New-Object directoryservices.directoryentry("LDAP://$($de.configurationNamingContext)") 

        $ds = New-Object directoryservices.directorySearcher($root) 

        $ds.filter = "(CN=$NetWork)" 

        $r = $ds.findone() 

        if ($r) { 

        $Site_C = $r.GetDirectoryEntry().siteObject

        $2Name = "$Site_C"

        $Site_Code = $2Name.split('=,')[1]

        $Output= "$Computer" + ',' + "$Site_Code"

        break}}

        if ($Output){$Output}

        else {"$Computer" + ',' + "NoSite"}

    }

    Function Get-Site_Move { 

      param ($Computer) 

      $ip = 0 

        &{TRAP{$ip = $null;continue} 

          $ip =[System.Net.Dns]::GetHostAddresses($Computer)

          if ($ip) { 

            $ips = "$ip"

            Find-Site_Move $ips

          }ELSE { 

            "$Computer" + ',' + "NoSite" 

          } 

        } 

      }

    #######################

    function Split-Job {

        param (

            $Scriptblock = $(throw 'You must specify a command or script block!'),

            [int]$MaxPipelines=10,

            [switch]$UseProfile,

            [string[]]$Variable,

            [string[]]$Function = @(),

            [string[]]$Alias = @(),

            [string[]]$SnapIn

        ) 

        function Init ($InputQueue){

            # Create the shared thread-safe queue and fill it with the input objects

            $Queue = [Collections.Queue]::Synchronized([Collections.Queue]@($InputQueue))

            $QueueLength = $Queue.Count

            # Do not create more runspaces than input objects

            if ($MaxPipelines -gt $QueueLength) {$MaxPipelines = $QueueLength}

            # Create the script to be run by each runspace

            $Script  = "Set-Location '$PWD'; "

            $Script += {

                $SplitJobQueue = $($Input)

                & {

                    trap {continue}

                    while ($SplitJobQueue.Count) {$SplitJobQueue.Dequeue()}

                } |

            }.ToString() + $Scriptblock

            # Create an array to keep track of the set of pipelines

            $Pipelines = New-Object System.Collections.ArrayList

            # Collect the functions and aliases to import

            $ImportItems = ($Function -replace '^','Function:') +

                ($Alias -replace '^','Alias:') |

                Get-Item | select PSPath, Definition

            $stopwatch = New-Object System.Diagnostics.Stopwatch

            $stopwatch.Start()

        }

        function Add-Pipeline {

            # This creates a new runspace and starts an asynchronous pipeline with our script.

            # It will automatically start processing objects from the shared queue.

            $Runspace = [System.Management.Automation.Runspaces.RunspaceFactory]::CreateRunspace($Host)

            $Runspace.Open()

            $Runspace.SessionStateProxy.SetVariable('SplitJobRunSpace', $True)

            function CreatePipeline {

                param ($Data, $Scriptblock)

                $Pipeline = $Runspace.CreatePipeline($Scriptblock)

                if ($Data) {

                    $Null = $Pipeline.Input.Write($Data, $True)

                    $Pipeline.Input.Close()

                }

                $Null = $Pipeline.Invoke()

                $Pipeline.Dispose()

            }

            # Optionally import profile, variables, functions and aliases from the main runspace

            if ($UseProfile) {

                CreatePipeline -Script "`$PROFILE = '$PROFILE'; . `$PROFILE"

            }

            if ($Variable) {

                foreach ($var in (Get-Variable $Variable -Scope 2)) {

                    trap {continue}

                    $Runspace.SessionStateProxy.SetVariable($var.Name, $var.Value)

                }

            }

            if ($ImportItems) {

                CreatePipeline $ImportItems {

                    foreach ($item in $Input) {New-Item -Path $item.PSPath -Value $item.Definition}

                }

            }

            if ($SnapIn) {

                CreatePipeline (Get-PSSnapin $Snapin -Registered) {$Input | Add-PSSnapin}

            }

            $Pipeline = $Runspace.CreatePipeline($Script)

            $Null = $Pipeline.Input.Write($Queue)

            $Pipeline.Input.Close()

            $Pipeline.InvokeAsync()

            $Null = $Pipelines.Add($Pipeline)

        }

        function Remove-Pipeline ($Pipeline) {

            # Remove a pipeline and runspace when it is done

            $Pipeline.RunSpace.Close()

            $Pipeline.Dispose()

            $Pipelines.Remove($Pipeline)

        }

        # Main 

        # Initialize the queue from the pipeline

        . Init $Input

        # Start the pipelines

        while ($Pipelines.Count -lt $MaxPipelines -and $Queue.Count) {Add-Pipeline} 

        # Loop through the runspaces and pass their output to the main pipeline

        while ($Pipelines.Count) {

            # Only update the progress bar once a second

            if (($stopwatch.ElapsedMilliseconds - $LastUpdate) -gt 1000) {

                $Completed = $QueueLength - $Queue.Count - $Pipelines.count

                $LastUpdate = $stopwatch.ElapsedMilliseconds

                $SecondsRemaining = $(if ($Completed) {

                    (($Queue.Count + $Pipelines.Count)*$LastUpdate/1000/$Completed)

                } else {-1})

                Write-Progress 'Split-Job' ("Queues: $($Pipelines.Count)  Total: $($QueueLength)  " +

                "Completed: $Completed  Pending: $($Queue.Count)")  `

                -PercentComplete ([Math]::Max((100-[Int]($Queue.Count+$Pipelines.Count)/$QueueLength*100),0)) `

                -CurrentOperation "Next item: $(trap {continue}; if ($Queue.Count) {$Queue.Peek()})" `

                -SecondsRemaining $SecondsRemaining

            }

            foreach ($Pipeline in @($Pipelines)) {

                if ( -not $Pipeline.Output.EndOfPipeline -or -not $Pipeline.Error.EndOfPipeline ) {

                    $Pipeline.Output.NonBlockingRead()

                    $Pipeline.Error.NonBlockingRead() | Out-Default

                } else {

                    # Pipeline has stopped; if there was an error show info and restart it

                    if ($Pipeline.PipelineStateInfo.State -eq 'Failed') {

                        $Pipeline.PipelineStateInfo.Reason.ErrorRecord |

                            Add-Member NoteProperty writeErrorStream $True -PassThru |

                                Out-Default

                        # Restart the runspace

                        if ($Queue.Count -lt $QueueLength) {Add-Pipeline}

                    }

                    Remove-Pipeline $Pipeline

                }

            }

            Start-Sleep -Milliseconds 100

        }

    }

    ######################

    $FileName = Get-Date -uformat 'C:\AD_Data_Move_%d%m%Y.log'

    get-QADComputer -searchroot "ande.in/Computers" -SizeLimit 0 | select Name `

    | Split-Job -Function "Get-Site_Move","Find-Site_Move" {%{ (Get-Site_Move $_.Name) }} `

    | Out-File -Append -FilePath $FileName

    ######################

    $Date_Move = Get-Date

    echo "$Date_Move : Moving Computers to Respective Site OU....." | Out-File -Append -FilePath $Log

    ######################

    ##### Moving StaleOU Computers to Location Specific Stale OU

    $OU_Names = @{"SiteCode_India" = "ande.in/Asia/India/Computers";

    "SiteCode_Japan" = "ande.in/Asia/Japan/Computers";

    "SiteCode_Italy" = "ande.in/Europe/Italy/Computers";

    "SiteCode_Canada" = "ande.in/North America/Canada/Computers";

    "SiteCode_Argentina" = "ande.in/South America/Argentina/Computers";}

    $Sites ='SiteCode_India','SiteCode_Japan','SiteCode_Italy','SiteCode_Canada','SiteCode_Argentina'

    foreach ($Site in $Sites)

    {

    $SearchOU ="ande.in/Computers"

    $DestOU = $OU_Names.$Site

    $StaleOU = "StaleOU_$Site"

    $StaleOU = get-content $FileName `

    |select-object @{e={$_.split('",')[0]};n='Name'},@{e={$_.split(',"')[1]};n='Site'} `

    |Where-Object {$_.Site -eq $Site}

    foreach ($S in $StaleOU)

    {

    Get-QADComputer -SearchRoot $SearchOU -Name $S.Name | Move-QADObject -ErrorAction SilentlyContinue -NewParentContainer $DestOU

    }

    }

    ######################

    $Date_Finish = Get-Date

    echo "$Date_Finish : Movement Completed Successfully....." | Out-File -Append -FilePath $Log

    ######################

Part III : Move all Disabled computers to Standard Disabled OU [ http://ande.in ]

andersonk — Wed, 31 Mar 2010 09:40:05 GMT

Here we move all Disabled computers from OUs to a standard OU.

Note : Chenge the following names in the script.

Standard Disabled OU : “OU=Disabled,DC=ande,DC=in”

Standard Disabled OU : "ande.in/Disabled"

$log [Default : C:\Log_310310.log]

     add-pssnapin Quest.ActiveRoles.ADManagement

    $Log = Get-Date -uformat "C:\Log_%d%m%Y.log"

    ######################

    $Date_Move2Disabled = Get-Date

    echo "$Date_Move2Disabled : Moving all Disabled computers to Standard Disabled OU....." | Out-File -Append -FilePath $Log

    ######################

    ##### Move All Disabled Computers to Standard Disabled OU

    Get-QADComputer -SizeLimit 0 -ldap "(userAccountControl:1.2.840.113556.1.4.803:=2)" ` 

    | where {$_.dn -notmatch "OU=Disabled,DC=ande,DC=in"} `

    | Move-QADObject -NewParentContainer "ande.in/Disabled"

Part II : Identifying and Disabling Stale Computers [ http://ande.in ]

andersonk — Wed, 31 Mar 2010 09:21:35 GMT

By Using the table we created from Part I, we identify the computers with password age and lastlogontimestamp > 60 days and disable them.

Stale Computers can be identified based on their Password Age and LastLogonTimeStamp

Go Further :

Machine Account Password Process

“The LastLogonTimeStamp Attribute” – “What it was designed for and how it works”

SQL Query to find Stale Computers

Note : Change the following names in the script

Standard Disabled OU [Default : ande.in/Disabled]

SQL_Server_Name

SQL_DB_Name

     SELECT Name FROM AD_Data WHERE ParentContainer != 'ande.in/Disabled'

    AND ((pwd_age >60 and llts_age >60) or (pwd_age >60 and llts_age is null))

    AND Name NOT IN (SELECT name FROM AD_Data GROUP BY Name HAVING ( COUNT(Name) > 1 ))

Power Shell Script to Identify and Disable Stale Computers, This Excludes Standard Disabled Computers OU

    add-pssnapin Quest.ActiveRoles.ADManagement

    $LogName_Stale_60 = Get-Date -uformat "C:\Log_%d%m%Y.log"

    ######################

    $Date_Stale_60 = Get-Date

    echo "$Date_Stale_60 : Disabling Stale Computers in Active Directory....." | Out-File -Append -FilePath $Log

    ######################

    #####Get Computers with Password Age and Last Logon > 60 from SQL

    $SqlQuery_Stale = "SELECT Name FROM AD_Data WHERE ParentContainer != 'ande.in/Disabled'

    AND ((pwd_age >60 and llts_age >60) or (pwd_age >60 and llts_age is null))

    AND Name NOT IN (SELECT name FROM AD_Data GROUP BY Name HAVING ( COUNT(Name) > 1 ))"

    ###

    $SqlConnection_Stale = New-Object System.Data.SqlClient.SqlConnection

    $SqlConnection_Stale.ConnectionString = "Data Source=SQL_Server_Name;Initial Catalog=SQL_DB_Name;Integrated Security=TRUE;"

    $SqlCmd_Stale = New-Object System.Data.SqlClient.SqlCommand

    $SqlCmd_Stale.CommandText = $SqlQuery_Stale

    $SqlCmd_Stale.Connection = $SqlConnection_Stale

    $SqlAdapter_Stale = New-Object System.Data.SqlClient.SqlDataAdapter

    $SqlAdapter_Stale.SelectCommand = $SqlCmd_Stale

    $DataSet_Stale = New-Object System.Data.DataSet

    $SqlAdapter_Stale.Fill($DataSet_Stale)

    $Stale_Computers_60 = $DataSet_Stale.Tables | Select-Object -Expand Rows

    $SqlConnection_Stale.Close()

    ##

    foreach ($Computer_60 in $Stale_Computers_60)

    {

    Get-QADComputer -Name $Computer_60.Name | Disable-QADComputer

    }

Part I : Bridging the GAP between AD, DNS, Sites and IP Subnets [ http://ande.in ]

andersonk — Wed, 31 Mar 2010 09:17:48 GMT

Imagine if you get the data of all Computers with Name,IP,Subnet and Site details in a single script !!! That is what we are going to do…

Note :

1. We are going to use 3 Functions

Find-Site and Get-Site : Used to identify Subnet and Site Details. Credit goes to /\/\o\/\/

Split-Job : Used to speed up the script execution. So the script will execute 10 times faster. Credit goes to Arnoud Jansveld

2. Quest Active Directory cmdlets : Get Active directory data with Password Age and Last Logon Time Stamp, used to identify stale computers

3. Script execution time approx 100000 computers = 1 hour 30 mins

4. You can upload the csv file to SCCM database and compare the discrepancies as shown in the example section.

5. Modifications required in script:

$FileName : Choose path for the output file. [Default : C:\AD_Data_16022010.log]

$LogName : Choose path for the log file. [Default : C:\AD_Log_16022010.log]

"Data Source=SQL_Server_Name;Initial Catalog=DB_Name;Integrated Security=TRUE;"

Table Name AD_Data [Default : AD_Data]

     $LogName = Get-Date -uformat "C:\AD_Log_%d%m%Y.log"

    #####Used to identify Subnet and Site Details.

    Function Find-Site { 

      param ([net.ipAddress]$ip) 

      for ($bit = 30 ; $bit -ge 1; $bit--){ 

        [int]$octet = [math]::Truncate(($bit - 1 ) / 8) 

        $net = [byte[]]@() 

        for($o=0;$o -le 3;$o++) { 

          $ba = $ip.GetAddressBytes() 

          if ($o -lt $Octet) { 

            $Net += $ba[$o] 

          }ELSEIF ($o -eq $octet) { 

            $factor = 8 + $Octet * 8 - $bit 

            $Divider = [math]::pow(2,$factor) 

            $value = $divider * [math]::Truncate($ba[$o] /  $divider) 

            $Net += $value 

          }ELSE { 

            $Net += 0 

          } 

        } 

        $NetWork = [string]::join('.',$net) + "/$bit"

        if ($verbose.IsPresent) {write-host -fore 'green' "Trying : $network"} 

        $de = New-Object directoryservices.directoryentry('LDAP://rootDSE') 

        $Root = New-Object directoryservices.directoryentry("LDAP://$($de.configurationNamingContext)") 

        $ds = New-Object directoryservices.directorySearcher($root) 

        $ds.filter = "(CN=$NetWork)" 

        $r = $ds.findone() 

        if ($r) { 

        $Site_C = $r.GetDirectoryEntry().siteObject 

        $2Name = "$Site_C" 

        $Site_Code = $2Name.split('=,')[1] 

        $Output= "$ip" + ',' + "$Network" + ',' + "$Site_Code" 

        break}} 

        if ($Output){$Output} 

        else {"$ip" + ',,'} 

    } 

    Function Get-Site { 

      param ($Computer) 

      $ip = 0 

        &{TRAP{$ip = $null;continue} 

          $ip =[System.Net.Dns]::GetHostAddresses($Computer) 

          if ($ip) { 

            $ips = "$ip" 

            Find-Site $ips 

            }ELSE { 

            ',,' 

           } 

        } 

    }

    ##### Used to speed up the script execution. So the script will execute 10 times faster.

    Function Split-Job { 

        param ( 

            $Scriptblock = $(throw 'You must specify a command or script block!'), 

            [int]$MaxPipelines=10, 

            [switch]$UseProfile, 

            [string[]]$Variable, 

            [string[]]$Function = @(), 

            [string[]]$Alias = @(), 

            [string[]]$SnapIn 

        )

        function Init ($InputQueue){ 

            # Create the shared thread-safe queue and fill it with the input objects 

            $Queue = [Collections.Queue]::Synchronized([Collections.Queue]@($InputQueue)) 

            $QueueLength = $Queue.Count 

            # Do not create more runspaces than input objects 

            if ($MaxPipelines -gt $QueueLength) {$MaxPipelines = $QueueLength} 

            # Create the script to be run by each runspace 

            $Script  = "Set-Location '$PWD'; " 

            $Script += { 

                $SplitJobQueue = $($Input) 

                & { 

                    trap {continue} 

                    while ($SplitJobQueue.Count) {$SplitJobQueue.Dequeue()} 

                } | 

            }.ToString() + $Scriptblock

            # Create an array to keep track of the set of pipelines 

            $Pipelines = New-Object System.Collections.ArrayList

            # Collect the functions and aliases to import 

            $ImportItems = ($Function -replace '^','Function:') + 

                ($Alias -replace '^','Alias:') | 

                Get-Item | select PSPath, Definition 

            $stopwatch = New-Object System.Diagnostics.Stopwatch 

            $stopwatch.Start() 

        }

        function Add-Pipeline { 

            # This creates a new runspace and starts an asynchronous pipeline with our script. 

            # It will automatically start processing objects from the shared queue. 

            $Runspace = [System.Management.Automation.Runspaces.RunspaceFactory]::CreateRunspace($Host) 

            $Runspace.Open() 

            $Runspace.SessionStateProxy.SetVariable('SplitJobRunSpace', $True)

            function CreatePipeline { 

                param ($Data, $Scriptblock) 

                $Pipeline = $Runspace.CreatePipeline($Scriptblock) 

                if ($Data) { 

                    $Null = $Pipeline.Input.Write($Data, $True) 

                    $Pipeline.Input.Close() 

                } 

                $Null = $Pipeline.Invoke() 

                $Pipeline.Dispose() 

            }

            # Optionally import profile, variables, functions and aliases from the main runspace 

            if ($UseProfile) { 

                CreatePipeline -Script "`$PROFILE = '$PROFILE'; . `$PROFILE" 

            } 

            if ($Variable) { 

                foreach ($var in (Get-Variable $Variable -Scope 2)) { 

                    trap {continue} 

                    $Runspace.SessionStateProxy.SetVariable($var.Name, $var.Value) 

                } 

            } 

            if ($ImportItems) { 

                CreatePipeline $ImportItems { 

                    foreach ($item in $Input) {New-Item -Path $item.PSPath -Value $item.Definition} 

                } 

            } 

            if ($SnapIn) { 

                CreatePipeline (Get-PSSnapin $Snapin -Registered) {$Input | Add-PSSnapin} 

            } 

            $Pipeline = $Runspace.CreatePipeline($Script) 

            $Null = $Pipeline.Input.Write($Queue) 

            $Pipeline.Input.Close() 

            $Pipeline.InvokeAsync() 

            $Null = $Pipelines.Add($Pipeline) 

        }

        function Remove-Pipeline ($Pipeline) { 

            # Remove a pipeline and runspace when it is done 

            $Pipeline.RunSpace.Close() 

            $Pipeline.Dispose() 

            $Pipelines.Remove($Pipeline) 

        }

        # Main 

        # Initialize the queue from the pipeline 

        . Init $Input 

        # Start the pipelines 

        while ($Pipelines.Count -lt $MaxPipelines -and $Queue.Count) {Add-Pipeline}

        # Loop through the runspaces and pass their output to the main pipeline 

        while ($Pipelines.Count) { 

            # Only update the progress bar once a second 

            if (($stopwatch.ElapsedMilliseconds - $LastUpdate) -gt 1000) { 

                $Completed = $QueueLength - $Queue.Count - $Pipelines.count 

                $LastUpdate = $stopwatch.ElapsedMilliseconds 

                $SecondsRemaining = $(if ($Completed) { 

                    (($Queue.Count + $Pipelines.Count)*$LastUpdate/1000/$Completed) 

                } else {-1}) 

                Write-Progress 'Split-Job' ("Queues: $($Pipelines.Count)  Total: $($QueueLength)  " + 

                "Completed: $Completed  Pending: $($Queue.Count)")  ` 

                -PercentComplete ([Math]::Max((100-[Int]($Queue.Count+$Pipelines.Count)/$QueueLength*100),0)) ` 

                -CurrentOperation "Next item: $(trap {continue}; if ($Queue.Count) {$Queue.Peek()})" ` 

                -SecondsRemaining $SecondsRemaining 

            } 

            foreach ($Pipeline in @($Pipelines)) { 

                if ( -not $Pipeline.Output.EndOfPipeline -or -not $Pipeline.Error.EndOfPipeline ) { 

                    $Pipeline.Output.NonBlockingRead() 

                    $Pipeline.Error.NonBlockingRead() | Out-Default 

                } else { 

                    # Pipeline has stopped; if there was an error show info and restart it 

                    if ($Pipeline.PipelineStateInfo.State -eq 'Failed') { 

                        $Pipeline.PipelineStateInfo.Reason.ErrorRecord | 

                            Add-Member NoteProperty writeErrorStream $True -PassThru | 

                                Out-Default 

                        # Restart the runspace 

                        if ($Queue.Count -lt $QueueLength) {Add-Pipeline} 

                    } 

                    Remove-Pipeline $Pipeline 

                } 

            } 

            Start-Sleep -Milliseconds 100 

        } 

    }

    ###################### 

    $Date_Start = Get-Date 

    echo "$Date_Start : Importing Data from AD, DNS and Site....." | Out-File -Append -FilePath $LogName 

    ######################

    $FileName = Get-Date -uformat 'C:\AD_Data_%d%m%Y.log' 

    Get-QADComputer -IncludedProperties pwdLastSet,lastLogonTimestamp -SizeLimit 0 | ` 

    Select -ErrorAction SilentlyContinue ` 

    Name, ` 

    dnsHostName, ` 

    ParentContainer, ` 

    @{n="PWD_Age";e={((get-date)- $_.pwdLastSet).days}}, ` 

    @{n="LLTS_Age";e={((get-date)- $_.lastLogonTimestamp).days}} ` 

    | Split-Job -Function "get-site","find-site" {%{$_.Name + ',' + $_.dnsHostName + ',' + $_.ParentContainer + ',' + $_.PWD_Age + ',' + $_.LLTS_Age + ',' + (Get-Site $_.Name) }} ` 

    | Out-File -Append -FilePath $FileName

Uploading this data to SQL

    ###################### 

    $Date_Run = Get-Date 

    echo "$Date_Run : Uploading Data to SQL....." | Out-File -Append -FilePath $LogName 

    ######################

    $SqlQuery_CSV2SQL = "IF EXISTS(SELECT name FROM sys.tables WHERE name = 'AD_Data') 

    BEGIN DROP TABLE AD_Data END 

    CREATE TABLE AD_Data (Name varchar(128),dnsHostName varchar(128),ParentContainer varchar(255),PWD_Age int,LLTS_Age int,IPAddress varchar(64),IPSegment varchar(128),Site varchar(30))" 

    ## 

    $SqlConnection_CSV2SQL = New-Object System.Data.SqlClient.SqlConnection 

    $SqlConnection_CSV2SQL.ConnectionString = "Data Source=SQL_Server_Name;Initial Catalog=DB_Name;Integrated Security=TRUE;" 

    $SqlCmd_CSV2SQL = New-Object System.Data.SqlClient.SqlCommand 

    $SqlCmd_CSV2SQL.CommandText = $SqlQuery_CSV2SQL 

    $SqlCmd_CSV2SQL.Connection = $SqlConnection_CSV2SQL 

    $SqlAdapter_CSV2SQL = New-Object System.Data.SqlClient.SqlDataAdapter 

    $SqlAdapter_CSV2SQL.SelectCommand = $SqlCmd_CSV2SQL 

    $DataSet_CSV2SQL = New-Object System.Data.DataSet 

    $SqlAdapter_CSV2SQL.Fill($DataSet_CSV2SQL) 

    $SqlConnection_CSV2SQL.Close() 

    ## 

    sqlcmd -S "SQL_Server_Name" -E -Q "BULK INSERT DB_Name.dbo.AD_Data FROM '$FileName' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR ='\n', DATAFILETYPE='widechar')"

    ###################### 

    $Date_Finish = Get-Date 

    echo "$Date_Finish : Data Uploaded to SQL Successfully!" | Out-File -Append -FilePath $LogName 

    ######################

We can use this table in the following process of Active Directory Cleanup.

Active Directory / SCCM Cleanup and Baselining [ http://ande.in ]

andersonk — Wed, 31 Mar 2010 08:31:06 GMT

Here we are going to see some methods for automating Active Directory cleanup process.

Part I

Bridging the GAP between AD, DNS, Sites and IP Subnets.

Here we are going to map IP Address, Subnet and Site Code for all Computers in Active Directory and upload the data to SQL database.

Part II

Identifying and Disabling Stale Computers.

By Using the table we created from Part I, we identify the computers with password age and lastlogontimestamp > 60 days and disable them.

Stale Computers can be identified based on their Password Age and LastLogonTimeStamp

Go Further :

Machine Account Password Process

“The LastLogonTimeStamp Attribute” – “What it was designed for and how it works”

Part III

Move all Disabled computers to Standard Disabled OU.

Here we move all Disabled computers from other OUs to a standard OU.

Part IV

Move Computers to OU based on IP Subnets.

To automate the movement of computers in the Default Computers Container to the Location OUs based on their IP Subnets defined in the Active Directory Sites and Subnets

Source OU

ande.in/Computers

Destination OUs

ande.in/Asia/India/Computers

ande.in/Asia/Japan/Computers

ande.in/Europe/Italy/Computers

ande.in/North America/Canada/Computers

ande.in/South America/Argentina/Computers

Part V

Keep Active Directory and SCCM in Sync.

1. Comparing Computers in Active Directory vs Clients in SCCM

2. Identifying Computers in Active Directory without DNS Records

3. Identify Stale Computers in Active Directory [ etc ]

Bridging the GAP between AD, DNS, Sites, IP Subnets and SCCM using Powershell

andersonk — Tue, 16 Feb 2010 09:34:00 GMT

Imagine if you get the data of all Computers with Name,IP,Subnet and Site details in a single script !!! That is what we are going to do…

Note :

1. We are going to use 3 Functions

Find-Site and Get-Site : Used to identify Subnet and Site Details. Credit goes to /\/\o\/\/

Split-Job : Used to speed up the script execution. So the script will execute 10 times faster. Credit goes to Arnoud Jansveld

2. Quest Active Directory cmdlets : Get Active directory data with Password Age and Last Logon Time Stamp, used to identify stale computers

3. Script execution time approx 100000 computers = 1 hour 30 mins

4. You can upload the csv file to SCCM database and compare the discrepancies as shown in the example section.

5. Modifications required in script:

$FileName : Choose path for the output file. [Default : C:\AD_Data_16022010.log]

$LogName : Choose path for the log file. [Default : C:\AD_Log_16022010.log]

"Data Source=SQL_Server_Name;Initial Catalog=DB_Name;Integrated Security=TRUE;"

Table Name AD_Data [Default : AD_Data]

$LogName = Get-Date -uformat "C:\AD_Log_%d%m%Y.log"

#####Used to identify Subnet and Site Details.

Function Find-Site {
param ([net.ipAddress]$ip)
for ($bit = 30 ; $bit -ge 1; $bit--){
    [int]$octet = [math]::Truncate(($bit - 1 ) / 8)
    $net = [byte[]]@()
    for($o=0;$o -le 3;$o++) {
      $ba = $ip.GetAddressBytes()
      if ($o -lt $Octet) {
        $Net += $ba[$o]
      }ELSEIF ($o -eq $octet) {
        $factor = 8 + $Octet * 8 - $bit
        $Divider = [math]::pow(2,$factor)
        $value = $divider * [math]::Truncate($ba[$o] / $divider)
        $Net += $value
      }ELSE {
        $Net += 0
      }
    }
    $NetWork = [string]::join('.',$net) + "/$bit"

    if ($verbose.IsPresent) {write-host -fore 'green' "Trying : $network"}
    $de = New-Object directoryservices.directoryentry('LDAP://rootDSE')
    $Root = New-Object directoryservices.directoryentry("LDAP://$($de.configurationNamingContext)")
    $ds = New-Object directoryservices.directorySearcher($root)
    $ds.filter = "(CN=$NetWork)"
    $r = $ds.findone()
    if ($r) {
    $Site_C = $r.GetDirectoryEntry().siteObject
    $2Name = "$Site_C"
    $Site_Code = $2Name.split('=,')[1]
    $Output= "$ip" + ',' + "$Network" + ',' + "$Site_Code"
    break}}
    if ($Output){$Output}
    else {"$ip" + ',,'}
}
Function Get-Site {
param ($Computer)
$ip = 0
    &{TRAP{$ip = $null;continue}
      $ip =[System.Net.Dns]::GetHostAddresses($Computer)
      if ($ip) {
        $ips = "$ip"
        Find-Site $ips
        }ELSE {
        ',,'
       }
    }
}

##### Used to speed up the script execution. So the script will execute 10 times faster.

Function Split-Job {
    param (
        $Scriptblock = $(throw 'You must specify a command or script block!'),
        [int]$MaxPipelines=10,
        [switch]$UseProfile,
        [string[]]$Variable,
        [string[]]$Function = @(),
        [string[]]$Alias = @(),
        [string[]]$SnapIn
    )

    function Init ($InputQueue){
        # Create the shared thread-safe queue and fill it with the input objects
        $Queue = [Collections.Queue]::Synchronized([Collections.Queue]@($InputQueue))
        $QueueLength = $Queue.Count
        # Do not create more runspaces than input objects
        if ($MaxPipelines -gt $QueueLength) {$MaxPipelines = $QueueLength}
        # Create the script to be run by each runspace
        $Script = "Set-Location '$PWD'; "
        $Script += {
            $SplitJobQueue = $($Input)
            & {
                trap {continue}
                while ($SplitJobQueue.Count) {$SplitJobQueue.Dequeue()}
            } |
        }.ToString() + $Scriptblock

# Create an array to keep track of the set of pipelines
$Pipelines = New-Object System.Collections.ArrayList

        # Collect the functions and aliases to import
        $ImportItems = ($Function -replace '^','Function:') +
            ($Alias -replace '^','Alias:') |
            Get-Item | select PSPath, Definition
        $stopwatch = New-Object System.Diagnostics.Stopwatch
        $stopwatch.Start()
    }

    function Add-Pipeline {
        # This creates a new runspace and starts an asynchronous pipeline with our script.
        # It will automatically start processing objects from the shared queue.
        $Runspace = [System.Management.Automation.Runspaces.RunspaceFactory]::CreateRunspace($Host)
        $Runspace.Open()
        $Runspace.SessionStateProxy.SetVariable('SplitJobRunSpace', $True)

        function CreatePipeline {
            param ($Data, $Scriptblock)
            $Pipeline = $Runspace.CreatePipeline($Scriptblock)
            if ($Data) {
                $Null = $Pipeline.Input.Write($Data, $True)
                $Pipeline.Input.Close()
            }
            $Null = $Pipeline.Invoke()
            $Pipeline.Dispose()
        }

        # Optionally import profile, variables, functions and aliases from the main runspace
        if ($UseProfile) {
            CreatePipeline -Script "`$PROFILE = '$PROFILE'; . `$PROFILE"
        }
        if ($Variable) {
            foreach ($var in (Get-Variable $Variable -Scope 2)) {
                trap {continue}
                $Runspace.SessionStateProxy.SetVariable($var.Name, $var.Value)
            }
        }
        if ($ImportItems) {
            CreatePipeline $ImportItems {
                foreach ($item in $Input) {New-Item -Path $item.PSPath -Value $item.Definition}
            }
        }
        if ($SnapIn) {
            CreatePipeline (Get-PSSnapin $Snapin -Registered) {$Input | Add-PSSnapin}
        }
        $Pipeline = $Runspace.CreatePipeline($Script)
        $Null = $Pipeline.Input.Write($Queue)
        $Pipeline.Input.Close()
        $Pipeline.InvokeAsync()
        $Null = $Pipelines.Add($Pipeline)
    }

    function Remove-Pipeline ($Pipeline) {
        # Remove a pipeline and runspace when it is done
        $Pipeline.RunSpace.Close()
        $Pipeline.Dispose()
        $Pipelines.Remove($Pipeline)
    }

    # Main
    # Initialize the queue from the pipeline
    . Init $Input
    # Start the pipelines
    while ($Pipelines.Count -lt $MaxPipelines -and $Queue.Count) {Add-Pipeline}

    # Loop through the runspaces and pass their output to the main pipeline
    while ($Pipelines.Count) {
        # Only update the progress bar once a second
        if (($stopwatch.ElapsedMilliseconds - $LastUpdate) -gt 1000) {
            $Completed = $QueueLength - $Queue.Count - $Pipelines.count
            $LastUpdate = $stopwatch.ElapsedMilliseconds
            $SecondsRemaining = $(if ($Completed) {
                (($Queue.Count + $Pipelines.Count)*$LastUpdate/1000/$Completed)
            } else {-1})
            Write-Progress 'Split-Job' ("Queues: $($Pipelines.Count) Total: $($QueueLength) " +
            "Completed: $Completed Pending: $($Queue.Count)") `
            -PercentComplete ([Math]::Max((100-[Int]($Queue.Count+$Pipelines.Count)/$QueueLength*100),0)) `
            -CurrentOperation "Next item: $(trap {continue}; if ($Queue.Count) {$Queue.Peek()})" `
            -SecondsRemaining $SecondsRemaining
        }
        foreach ($Pipeline in @($Pipelines)) {
            if ( -not $Pipeline.Output.EndOfPipeline -or -not $Pipeline.Error.EndOfPipeline ) {
                $Pipeline.Output.NonBlockingRead()
                $Pipeline.Error.NonBlockingRead() | Out-Default
            } else {
                # Pipeline has stopped; if there was an error show info and restart it
                if ($Pipeline.PipelineStateInfo.State -eq 'Failed') {
                    $Pipeline.PipelineStateInfo.Reason.ErrorRecord |
                        Add-Member NoteProperty writeErrorStream $True -PassThru |
                            Out-Default
                    # Restart the runspace
                    if ($Queue.Count -lt $QueueLength) {Add-Pipeline}
                }
                Remove-Pipeline $Pipeline
            }
        }
        Start-Sleep -Milliseconds 100
    }
}

######################
$Date_Start = Get-Date
echo "$Date_Start : Importing Data from AD, DNS and Site....." | Out-File -Append -FilePath $LogName
######################

$FileName = Get-Date -uformat 'C:\AD_Data_%d%m%Y.log'
Get-QADComputer -IncludedProperties pwdLastSet,lastLogonTimestamp -SizeLimit 0 | `
Select -ErrorAction SilentlyContinue `
Name, `
dnsHostName, `
ParentContainer, `
@{n="PWD_Age";e={((get-date)- $_.pwdLastSet).days}}, `
@{n="LLTS_Age";e={((get-date)- $_.lastLogonTimestamp).days}} `
| Split-Job -Function "get-site","find-site" {%{$_.Name + ',' + $_.dnsHostName + ',' + $_.ParentContainer + ',' + $_.PWD_Age + ',' + $_.LLTS_Age + ',' + (Get-Site $_.Name) }} `
| Out-File -Append -FilePath $FileName

######################
$Date_Run = Get-Date
echo "$Date_Run : Uploading Data to SQL....." | Out-File -Append -FilePath $LogName
######################

$SqlQuery_CSV2SQL = "IF EXISTS(SELECT name FROM sys.tables WHERE name = 'AD_Data')
BEGIN DROP TABLE AD_Data END
CREATE TABLE AD_Data (Name varchar(128),dnsHostName varchar(128),ParentContainer varchar(255),PWD_Age int,LLTS_Age int,IPAddress varchar(64),IPSegment varchar(128),Site varchar(30))"
##
$SqlConnection_CSV2SQL = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection_CSV2SQL.ConnectionString = "Data Source=SQL_Server_Name;Initial Catalog=DB_Name;Integrated Security=TRUE;"
$SqlCmd_CSV2SQL = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd_CSV2SQL.CommandText = $SqlQuery_CSV2SQL
$SqlCmd_CSV2SQL.Connection = $SqlConnection_CSV2SQL
$SqlAdapter_CSV2SQL = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter_CSV2SQL.SelectCommand = $SqlCmd_CSV2SQL
$DataSet_CSV2SQL = New-Object System.Data.DataSet
$SqlAdapter_CSV2SQL.Fill($DataSet_CSV2SQL)
$SqlConnection_CSV2SQL.Close()
##
sqlcmd -S "SQL_Server_Name" -E -Q "BULK INSERT DB_Name.dbo.AD_Data FROM '$FileName' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR ='\n', DATAFILETYPE='widechar')"

######################
$Date_Finish = Get-Date
echo "$Date_Finish : Data Uploaded to SQL Successfully!" | Out-File -Append -FilePath $LogName
######################

Example:

1. Computers in AD but don`t have a SCCM client.

SELECT * FROM v_R_System
WHERE Name0 NOT IN (SELECT Name FROM AD_Data)

2. Computers in SCCM but don`t have account in AD [Stale Client]

SELECT * FROM AD_Data
WHERE Name0 NOT IN (SELECT Name FROM v_R_System)

3. To find Stale Computers based on their Password Age

SELECT * FROM AD_Data WHERE PWD_Age > 60 AND LLTS_Age > 60

Just to name a few…You can do a lot, using this table !!! As always comments and suggestions are welcome…..

SCCM Query for total count of OS and Service Pack

andersonk — Fri, 22 Jan 2010 07:15:00 GMT

This SQL query can be used to find the overall count of Operating systems and their Service packs

SELECT Caption0 as 'Operating System',CSDVersion0 as 'Service Pack', COUNT(*) AS 'Count'
FROM v_GS_OPERATING_SYSTEM,v_R_System
WHERE v_GS_OPERATING_SYSTEM.Resourceid=v_R_System.Resourceid
GROUP BY Caption0,CSDVersion0
ORDER BY Caption0,CSDVersion0

SCCM Query for identifying Software based on Executables

andersonk — Fri, 22 Jan 2010 07:11:00 GMT

This SQL query can be used to search for installed software based on their executables

SELECT VRS.Name0 as 'Computer Name',
VRS.User_Name0 as 'User Name',
SF.FileName as 'File Name',
SF.FileDescription as 'File Description',
SF.FilePath as 'File Path',
SF.ModifiedDate as 'Modified Date'
FROM v_R_System VRS,v_GS_SoftwareFile SF
WHERE SF.ResourceID = VRS.ResourceId
AND SF.FileDescription = 'Google Talk'
ORDER BY VRS.Name0