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…..