Load Computer, Group and Person - Account Attributes into a Database

This script requires Powersmo (PowerShell Community Extension 1.1.1) and a SQL Server database with SMO locally installed.

 

#

# In my scripts, this is the first step to create a completely automatic DataWarehouse with all System information – other scripts will arrive soon

#

 

# --- Prepare the environment -----------------------------

Set-ExecutionPolicy Unrestricted

c:\temp\InitPowerSMO.ps1

$server = SMO_Server

$db = SMO_Database $server "PowerDW"

$db.DatabaseOptions.RecoveryModel="Simple"

$db.Create()

c:

cd \

md PowerDW

#---------------------  Dynamic insert table function creation  ----------------------------------------------------------

function global:insert-table ($tablename) {

$dataset = $DB.ExecuteWithResults( "sp_columns ["+$tablename+"]")

$tableCol=$DATASET.TABLES[0].ROWS | select column_name, type_name,char_octect_length

$all='function insert-'+$tablename+'  (';

$varall=''; $tableCol | %{$varall=$varall+'$'+$_.column_name+','};

$all=$($all+$varall+')').replace(",)",") ");

$all=$all+"{  "" insert into ["+$tablename+"] values ("

if ($tableCol.count -lt 1) {$tableCol | %{$apri="'{"; $chiudi="}',"; $oggetto=$_; if (($oggetto.type_name -eq 'bigint') -or ($oggetto.type_name -eq 'Int32')  -or ($oggetto.type_name -eq 'datetime')) {$apri="{";$chiudi="},";} $all=$all+$apri+'0'+$chiudi; }; } else {$num=0..($tableCol.count-1); $num | %{$apri="'{"; $chiudi="}',"; if (($tableCol[$_].type_name -eq 'bigint') -or ($tableCol[$_].type_name -eq 'Int32')  -or ($tableCol[$_].type_name -eq 'datetime')) {$apri="{";$chiudi="},";} $all=$all+$apri+$_+$chiudi; }; }$all=$($all+')').replace(",)",") ") + """ -f "+$varall.substring(0,$($varall.length-1)) + " }";

$all

}

#---------------------  Dynamic creation and import tables (Person,Group,Computer) attributes -------------------------

function global:AD-Table ($tablename,$Variabile,$errorpath) {

if ($errorpath -eq '') {$errorpath="c:\PowerDW\"}

# ----- Generate a dynamic query to create table Person,Group or Computer  ------

$query="CREATE TABLE ["+$tablename+"] ("

$variables |%{ $query=$query+$($_)+' varchar(500), '}

$query=$($query+')').replace("), )","))")

# ----- drop and recreate table -----

$DB.ExecuteNonQuery("drop table ["+$tablename+"]")

$DB.ExecuteNonQuery($query)

#-Dynamic retrieve DC name for each Ad domain (require WINS) if you don't use build static DC list in $AdditionalDomains -

$dom=Get-WmiObject Win32_NTDomain

$domall=$($dom | select DomainName, DomainControllerName, DnsForestName | where {$_.DomainControllerName -gt ''} | %{$foresta=$_.DnsForestName; $domfo=$foresta.split('.');$domfo1=$domfo[0];$DomainName=$_.DomainName;$root=$_.DomainControllerName.replace('\\','')+'.'+$_.DnsForestName; if($DomainName -ne $domfo1.ToUpper()) {$root=$_.DomainControllerName.replace('\\','')+'.'+$DomainName+'.'+$_.DnsForestName;} $root})

$AdditionalDomains='dc1.dom1.com','dc2.dom1.com'

# ----- elenco domini=domini dinamici + altri domini - domini da escludere

$domall=$($domall | where {$_ -ne 'DCExcludeDom1.dom1.com' }) +$AdditionalDomains

#------- dynamic creation of insert-$tablename function  ------

$all=insert-table $tablename

Invoke-Expression $($all)

write-host $all

# ------- generate code to insert all attributes dynamcally ----

$varall='insert-'+$tablename

$REP=@'

.REPLACE("'","''")

'@

$a=@'

 $o]

'@

# ------- start letter list to reduce memory usage with small ldap query result ------------------------------

$letters='$','0','1','2','3','4','5','6','7','8','9','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'

$variables |%{ $varall=$varall+' $($_.'+$_+'.tostring())'+$REP}

#$group | %{ if ($varall.indexof("member") -gt -1) {for ($o=0; $o -lt $_.member.count; $o++) { write-host $_.name ' ' $_.member[$o] }} else {write-host $_.name} }

# ------ for each DC - for each user -insert all selected attributes in DB table PowerDW.$tablename

$varall2=$varall.replace("$_.member","$_.member["+$a);

$errori=$error.count

"Inizio "+$(get-date)>$($errorpath+$tablename+".log")

"Inizio "+$(get-date)>$($errorpath+$tablename+"2.log")

$domall | %{

    for ($k=0; $k -lt $letters.count; $k++)

    {$interrogoAD="get-adobject  -server "+$_+" -filter ""(&("+$Variabile+"="+$letters[$k]+"*)(objectcategory="+$tablename+"))"" -PageSize 1000 "; Invoke-Expression $($interrogoAD) |

            %{

                        if ($varall.indexof("member") -gt -1) { for ( $o=0;$o -lt $_.member.count; $o++) { Invoke-Expression $($VARALL2) | %{$DB.ExecuteNonQuery($_)};  if ($errori -lt $error.count) {$_ >>$($errorpath+$tablename+"2.log"); $errori=$error.count; } }}

                        else {Invoke-Expression $($VARALL) | %{$DB.ExecuteNonQuery($_); if ($errori -lt $error.count) { $_>>$($errorpath+$tablename+"2.log"); $errori=$error.count; }}}

            }

            if ($errori -lt $error.count) {$_ >>$($errorpath+$tablename+"2.log"); $errori=$error.count; }

   }

  }

"Fine "+$(get-date)>>$($errorpath+$tablename+".log")

"Fine "+$(get-date)>>$($errorpath+$tablename+"2.log")

}

 

# --------- Script execution ----------------------------------------

# -- Create and import data - table Computer

$variables='cn','OperatingSystem','OperatingSystemServicePack','OperatingSystemversion','distinguishedname','objectclass','objectcategory','name','description'

AD-Table Computer name ''

# -- Create and import data - table Group

$variables='cn','name','distinguishedName','whenChanged','whenCreated','objectclass','groupType','description','member','mail','displayname'

AD-Table Group name ''

# -- Create and import data - table Person

$variables='Samaccountname','Mail','sn','TelephoneNumber','GivenName','Mobile','proxyAddresses','distinguishedName','PhysicalDeliveryOfficeName','description','displayname','Homemdb','MSExchHomeServerName','name','objectcategory','objectclass'

AD-Table Person displayname ''

 

 

 

Query a remote server network configuration

This script shows the network configuration of a remote server

function global:RegVal($keyname,$keyvalue)
{
#RegVal \\servername\hklm\System\CurrentControlSet\Services\tcpip\parameters Domain
$val=reg query $keyname /v $keyvalue
$val[2].replace($keyvalue,"").replace("REG_SZ","").trimstart()

}


function global:QueryIp($servername)
{
$tcpipparamloc = "\\$servername\hklm\System\CurrentControlSet\Services\tcpip\parameters"
$Hostname = RegVal $tcpipparamloc hostname
$Domainname = RegVal $tcpipparamloc domain
$Routing = RegVal $tcpipparamloc IPEnableRouter
$DomainNameD = RegVal $tcpipparamloc UseDomainNameDevolution

$netbtparamloc = "\\$servername\hklm\System\CurrentControlSet\Services\netbt\parameters"
$Nodetype = RegVal $netbtparamloc DHCPNodeType
$LMhostsEnab = RegVal $netbtparamloc EnableLMHosts

$nodetypestr="Unknown"
Switch ($Nodetype) {
4 {$NodeTypeStr = "Mixed"}
8 {$NodeTypestr = "Hybrid"}
else {$NodeTypestr = "Not known"}
}

$IPRouting="unknown"
if ($routing -eq 0) {$IPRouting="No"}
if ($routing -eq 1) {$IPRouting="Yes"}



$niccol = gwmi Win32_NetworkAdapterConfiguration -computerName $servername WHERE {$_.IPEnabled}


#check if DNS enabled for WINS Resolution anywhere
ForEach ($nic in $NicCol) {$DnsWins = $nic.DNSEnabledForWINSResolution}
If ($DnsWins)
{$winsproxy = "Yes"}
Else {$WinsProxy = "No"}

# Display global settings.

"Windows IP Configuration"
" Host Name . . . . . . . . . . . . : $Hostname"
" Primary DNS Suffix . . . . . . . : $DomainName"
" Node Type . . . . . . . . . . . . : $NodeTypeStr"
" IP Routing Enabled. . . . . . . . : $IPRouting"
" WINS Proxy Enabled. . . . . . . . : $WinsProxy"
" Use DNS Domain Name Devloution. . : $([boolean]$DomainNameD)"
" LMHosts Enabled . . . . . . . . . : $([boolean] $LMHostsEnab)"
" DNS Suffix Search List. . . . . . : $DomainName"
""

# Get os version number = 5.1 is XP.2k3
$OSVersion=[float]$(gwmi Win32_OperatingSystem -computerName $servername).version.substring(0,3)

# Finally Display per-adapter settings

$adapterconfigcol = gwmi Win32_NetworkAdapterConfiguration -computerName $servername
$adaptercol= gwmi Win32_NetworkAdapter -computerName $servername


For ($i=0; $i -lt $adaptercol.length; $i++)
{

$nic=$adaptercol[$i]
$config=$adapterconfigcol[$i]

# Display Information for IP enabled connections
If ($config.IPEnabled)
{

$Index = $nic.Index
$AdapterType = $Nic.AdapterType
If
($OsVersion -gt 5.0) {$Conn = $Nic.NetConnectionID}
Else
{$Conn = $nic.Index}

"$($Nic.AdapterType) - Adapter: $Conn"
"Connection-specific DNS Suffix . : $($config.DNSDomain)"
"Description . . . . . . . . . . . : $($Nic.Description)"
"Physical Address. . . . . . . . . : $($Nic.MACAddress)"
"DHCP Enabled. . . . . . . . . . . : $($Config.DHCPEnabled)"
"Autoconfiguration Enabled . . . . : $($Nic.AutoSense)"
"IP Address. . . . . . . . . . . . : $($config.IPAddress)"
"Subnet Mask . . . . . . . . . . . : $($Config.IPSubnet)"
"Default Gateway . . . . . . . . . : $($Config.DefaultIPGateway)"
"DHCP Server . . . . . . . . . . . : $($Config.DHCPServer)"
"DNS Servers . . . . . . . . . . . : $($Config.DNSServerSearchOrder)"
"Primary WINS Server . . . . . . . : $($Config.WINSPrimaryServer)"
"Secondary WINS Server . . . . . . : $($Config.WINSSecondaryServer)"
"Lease Obtained. . . . . . . . . . : $($Config.DHCPLeaseObtained)"
"Lease Expires . . . . . . . . . . : $($Config.DHCPLeaseExpires)"
""
}

}
}

Examples:
QueryIp server1
Or
QueryIp 10.20.10.99

Load all AD User in SqlServer with smo

This script loads all selected (NT user attributes) in a Sql Server Db Table for each Domain in your running forest.

To simplify script sharing I didn’t sign the script.

Script requirements:

Powersmo - Dan Sullivan

Powershell Comunity Extension 1.1.1

SqlServer whith SMO – locally installed

Script output:

Sql Server: local

Database: TempDb

Table: AdPerson (populated with all Ad User attributes)

=====================================================================================

Set-ExecutionPolicy Unrestricted

c:\temp\InitPowerSMO.ps1

$server = SMO_Server

$db = SMO_Database $server "tempdb"

# ----- Add here all AD person attribute to load in the database table ------------------------------

$variabili='Samaccountname','Mail','sn','TelephoneNumber','GivenName','ExtensionAttribute1','Mobile','proxyAddresses','distinguishedName','PhysicalDeliveryOfficeName','description','displayname','Homemdb','MSExchHomeServerName'

# ----- Generate a dynamic query to create table AdPerson ------

$query="CREATE TABLE AdPerson ("

$variabili %{ $query=$query+$($_)+' varchar(500), '}

$query=$($query+')').replace("), )","))")

# ----- drop and recreate table AdPerson -----

$DB.ExecuteNonQuery("drop table AdPerson")

$DB.ExecuteNonQuery($query)

#------ Dynamic retrieve DC name for each Ad domain ---------

$dom=Get-WmiObject Win32_NTDomain

$domall=$($dom select DomainName, DomainControllerName, DnsForestName where {$_.DomainControllerName -gt ''} %{$foresta=$_.DnsForestName; $domfo=$foresta.split('.');$domfo1=$domfo[0];$DomainName=$_.DomainName;$root=$_.DomainControllerName.replace('\\','')+'.'+$_.DnsForestName; if($DomainName -ne $domfo1.ToUpper()) {$root=$_.DomainControllerName.replace('\\','')+'.'+$DomainName+'.'+$_.DnsForestName;} $root})

#------- dynamic creation of insert-person function ------

$all='function insert-person (';

$varall=''; $variabili %{$varall=$varall+'$'+$_+','};

$all=$($all+$varall+')').replace(",)",") ");

$all=$all+"{ "" insert into AdPerson values ("

$num=0..($variabili.count-1); $num %{$all=$all+"'{"+$_+"}',"; };

$all=$($all+')').replace(",)",") ") + """ -f "+$varall.substring(0,$($varall.length-1)) + " }";

Invoke-Expression $($all)

write-host $all

# ------- generate code to insert all attributes dynamcally ----

$varall='insert-person'

$REP=@'

.REPLACE("'","''")

'@

$variabili %{ $varall=$varall+' $($_.'+$_+'.tostring())'+$REP}

# ------ for each DC - for each user -insert all selected attributes in DB table TempDb.Adperson

$domall %{$users=get-adobject -server $_ -class "User" -outvariable users -PageSize 1000; $users %{Invoke-Expression $($VARALL)} %{$DB.ExecuteNonQuery($_)} }

Scaricare tutta la foresta Ad in SqlServer? Yes.. con Powersmo e le comunity extension!

Translate in

Lo script che riporto sotto consente di creare una tabella

Con i campi specificati nella variabile $variabili.

Ho usato le Comunity extension x utilizzare la pagesize di Get-adobject in questo modo riesco ad avere performance accettabili anche con decine di migliaia di utenti.

Per l’utilizzo di questo script è necessario:

1. Installare Windows Powershell su una macchina con un’installazione di SQL server (standard o enterprise).

2. Configurare Powersmo come documentato da Dan Sullivan

3. Scaricare ed installare le Powershell Comunity Extension 1.1.1

A questo punto si è pronti a lanciare lo script ….

Lo script crea

Server: local

Database: TEMPDB

Tabella: AdPerson

e la riempie con gli attributi elencati per tutti gli utenti di tutti i domini.

Ecco lo script:

Set-ExecutionPolicy Unrestricted

c:\temp\InitPowerSMO.ps1

$server = SMO_Server

$db = SMO_Database $server "tempdb"

$variabili='Samaccountname','Mail','sn','TelephoneNumber','GivenName','ExtensionAttribute1','Mobile','proxyAddresses','distinguishedName','PhysicalDeliveryOfficeName','description','displayname','Homemdb','MSExchHomeServerName'

# ----- valorizzo la query di create table leggendo i campi sopra inseriti ------

$query="CREATE TABLE AdPerson ("

$variabili %{ $query=$query+$($_)+' varchar(500), '}

$query=$($query+')').replace("), )","))")

# ----- droppo e ricreo la tabella AdPerson -----

$DB.ExecuteNonQuery("drop table AdPerson")

$DB.ExecuteNonQuery($query)

#------ recupero dinamicamente l'elenco di 1 DC di ogni dominio ---------

$dom=Get-WmiObject Win32_NTDomain

$domall=$($dom select DomainName, DomainControllerName, DnsForestName where {$_.DomainControllerName -gt ''} %{$foresta=$_.DnsForestName; $domfo=$foresta.split('.');$domfo1=$domfo[0];$DomainName=$_.DomainName;$root=$_.DomainControllerName.replace('\\','')+'.'+$_.DnsForestName; if($DomainName -ne $domfo1.ToUpper()) {$root=$_.DomainControllerName.replace('\\','')+'.'+$DomainName+'.'+$_.DnsForestName;} $root})

$dominiAggiunti='DC1.dominio1','Dc2.Dominio2'

# ----- elenco DC domini=domini dinamici + altri domini - domini da escludere

$domall=$($domall where {$_ -ne 'DCEscluso.DominioEscluso' }) +$dominiAggiunti

#------- creo dinamicamente la funzione di insert nel DB degli utenti di AD recuperati dai DC ------

$all='function insert-person (';

$varall=''; $variabili %{$varall=$varall+'$'+$_+','};

$all=$($all+$varall+')').replace(",)",") ");

$all=$all+"{ "" insert into AdPerson values ("

$num=0..($variabili.count-1); $num %{$all=$all+"'{"+$_+"}',"; };

$all=$($all+')').replace(",)",") ") + """ -f "+$varall.substring(0,$($varall.length-1)) + " }";

Invoke-Expression $($all)

write-host $all

# ------- valorizzo in una variabile il codice x invocare la insert con l'elenco dinamico di variabili ----

$varall='insert-person'

$REP=@'

.REPLACE("'","''")

'@

$variabili %{ $varall=$varall+' $($_.'+$_+'.tostring())'+$REP}

# ------ interrogo ogni DC - X ogni utente recupero le variabili richieste e inserisco tutto nel DB

$domall %{$users=get-adobject -server $_ -class "User" -outvariable users -PageSize 1000; $users %{Invoke-Expression $($VARALL)} %{$DB.ExecuteNonQuery($_)} }