Create a table and load System, Network, Disk and Applications for each Win2003srv in your Computer Table

Description: Load server informations into a Database. This script requires Powersmo, Pstools and Computer Table

 

#

# This is the second step to build a completelly automatic Data Warehouse with all system informations

# Next steps will show how to create Dim and Fat tables,  schedule daily collection, using AMO ...

#

 

#------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

# Before running this script download computer table from http://www.microsoft.com/technet/scriptcenter/csc/scripts/ad/general/cscad108.mspx

# This script use PsTools that can be downloaded from http://www.microsoft.com/technet/sysinternals/Utilities/PsTools.mspx and installed in c:\PowerDW\PsTools

#------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

Set-ExecutionPolicy Unrestricted

c:\temp\InitPowerSMO.ps1

$server = SMO_Server

$db = SMO_Database $server "PowerDW"

 

 

#---------------------  Dynamic creation insert-$tablename function  --------------------------------------------------------------

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

}

#--------------------------------------------------------------------------------------

# Function to collect Disk information

#--------------------------------------------------------------------------------------

function global:PsDisk ($servername,$dn)

{

 $Tablename='disk'

$var=@'

$disk $SizeMB $FreeMB $PercFree

'@

 $out=''

 $out=c:\PowerDW\pstools\psinfo \\$servername -d Disk

 if ($out -LT '') {$Dischi='';} else {$Dischi=$out[3..$($out.count-1)]; }

 $dischi

 $dischi |

 %{

   $SizeMB=0

   $FreeMB=0

   $PercFree=''

   IF ($dischi -eq '')

            {

                        #======= inserisco la riga senza valori ====================================================================

                        Invoke-Expression $($("insert-"+$tablename+" "+$servername+" ""$dn"" "+$var) ) | %{$DB.ExecuteNonQuery($_)}

            }

   else

            {

                        $dischi2=$_.split(":")

                        if ($dischi2[1].trimstart().substring(0,5) -eq 'Fixed' )

                        {

                                   $DISCO=$dischi2[0].trimstart()+':'

                                   $INFO=$($dischi2[1].split(" ") | %{if ($_ -gt '') {$_}})

                                   $INFO[$($INFO.COUNT-1)]

                                   $PercFree=$INFO[$($INFO.COUNT-1)]

$esegui=@'

$FreeBites=

'@

                                   $esegui=$esegui+$($INFO[$($INFO.COUNT-3)]+$INFO[$($INFO.COUNT-2)])

                                   Invoke-Expression $($esegui)

$esegui=@'

$SizeBites=

'@

                                   $esegui=$esegui+$($INFO[$($INFO.COUNT-5)]+$INFO[$($INFO.COUNT-4)])

                                   Invoke-Expression $($esegui)

                                   $disk=$disco

                                   $SizeMB=[int] $($SizeBites/1MB)

                                   $FreeMB=[int] $($FreeBites/1MB)

 

                                   Invoke-Expression $($("insert-"+$tablename+" "+$servername+" ""$dn"" "+$var) ) | %{$DB.ExecuteNonQuery($_)}

                        }

            }

 }

}

 

#--------------------------------------------------------------------------------------

# Function to collect  System informations

#--------------------------------------------------------------------------------------

function global:PsSysinfo ($servername,$dn)

{

$psinfo=c:\PowerDW\pstools\psinfo \\$servername

$tablename="SysInfo"

$variabili=$($psinfo[1..$($psinfo.count-1)] | %{ $variabile,$valore=$_.split(":"); $variabile.tostring().trimstart().trimend().replace(" ","_")})

$valori=$($psinfo[1..$($psinfo.count-1)] | %{ $variabile,$valore=$_.split(":"); $valore.tostring().trimstart()})

$a=@'

$valori

'@;

$b="insert-"+$tablename+" "+$servername+" ""$dn"" "

$var=''

for ($j=0; $j -lt $valori.count; $j++) {$var=$var+' '+$a.trimend()+'['+$j+']';};

$b=$b+$var

write-host $dn

Invoke-Expression $($b ) | %{$DB.ExecuteNonQuery($_)}

}

 

 

#--------------------------------------------------------------------------------------

# Function to collect installed applications

#--------------------------------------------------------------------------------------

 

function global:PsApplication ($servername,$dn)

{

$out=''

$out=c:\PowerDW\pstools\psinfo \\$servername -s Applications

if ($out -LT '') {$Applicazioni='';} else {$Applicazioni=$out[3..$($out.count-1)]; }

$tablename="Application"

$all=insert-table $tablename

Invoke-Expression $($all)

write-host $all

 

$b="insert-"+$tablename+" "+$servername+" ""$dn"" "

$applicazioni | %{$applic=$_; $c=$b+" ""$applic"""; Invoke-Expression $($c); } | %{$DB.ExecuteNonQuery($_)}

 

}

 

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 to collect  Network informations

#--------------------------------------------------------------------------------------

function global:PsNetInfo($servername,$dn2)

{

$tcpipparamloc = "\\$servername\hklm\System\CurrentControlSet\Services\tcpip\parameters"

$Hostname = RegVal $tcpipparamloc hostname

if ($hostname -lt '') {

$inserisco="insert-NetInfo "+$servername +" "+"""$dn2"""

Invoke-Expression $($inserisco) |  %{$DB.ExecuteNonQuery($_)}

}

else

{

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

 

 

# 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)"

""

Invoke-Expression $($(insert-table NetInfo))

$allvaria=@'

$Hostname  $DomainName $NodeTypeStr $IPRouting $WinsProxy $([boolean]$DomainNameD) $([boolean] $LMHostsEnab) $DomainName $Conn $($config.DNSDomain) $($Nic.Description) $($Nic.MACAddress) $($Config.DHCPEnabled) $($Nic.AutoSense) "$($config.IPAddress)" "$($Config.IPSubnet)" $($Config.DefaultIPGateway) $($Config.DHCPServer) "$($Config.DNSServerSearchOrder)" $($Config.WINSPrimaryServer) $($Config.WINSSecondaryServer) $($Config.DHCPLeaseObtained) $($Config.DHCPLeaseExpires)

'@

$inserisco="insert-NetInfo "+$servername +" "+"""$dn2""" +" "+$allvaria

Invoke-Expression $($inserisco) |  %{$DB.ExecuteNonQuery($_)}

 

}

 

}

}

}

 

#---------------------------------------------------------------------------------------------------

# Function to collect all informations for each server in previously downloaded AD Computer table

#---------------------------------------------------------------------------------------------------

function global:collect($tablename)

{

# -- Create table if not exist --------------------------------------------------------

if ($($DB.ExecuteWithResults($("sp_columns "+$tablename)).TABLES[0].ROWS).count -lt 1)

{

    if ($tablename.toupper() -eq "NETINFO") {$DB.ExecuteNonQuery($("CREATE TABLE [dbo].[NetInfo]([ServerName] [varchar](50),[DistinguishedName] [varchar](500),[HostName] [varchar](100),[PrimaryDNSSuffix] [varchar](100),      [NodeType] [varchar](100),   [IPRoutingEnabled] [varchar](100),          [WINSProxyEnabled] [varchar](100),            [UseDNSDomainNameDevloution] [varchar](100),[LMHostsEnabled] [varchar](100),[DNSSuffixSearchList] [varchar](100),[Ethernet8023Adapter] [varchar](100),[ConnectionspecificDNSSuffix] [varchar](100),[Description] [varchar](100),[PhysicalAddress] [varchar](100),[DHCPEnabled] [varchar](100),[AutoconfigurationEnabled] [varchar](100),[IPAddress] [varchar](1000),[SubnetMask] [varchar](1000),[DefaultGateway] [varchar](100),[DHCPServer] [varchar](100),[DNSServers] [varchar](100),[PrimaryWINSServer] [varchar](100),[SecondaryWINSServer] [varchar](100),[LeaseObtained] [varchar](100),[LeaseExpires] [varchar](100))"));}

    if ($tablename.toupper() -eq "SYSINFO") {$DB.ExecuteNonQuery($("CREATE TABLE [dbo].[SysInfo]([ServerName] [varchar](50),[DistinguishedName] [varchar](500),            [Uptime] [varchar](50),   [Kernel_version] [varchar](50),    [Product_type] [varchar](50),[Product_version] [varchar](50),[Service_pack] [varchar](50),[Kernel_build_number] [varchar](50),[Registered_organization] [varchar](50),[Registered_owner] [varchar](50),[Install_date] [varchar](50),[Activation_status] [varchar](50),[IE_version] [varchar](50),[System_root] [varchar](50),[Processors] [varchar](50),[Processor_speed] [varchar](50),[Processor_type] [varchar](50),[Physical_memory] [varchar](50),[Video_driver] [varchar](100))"));}

    if ($tablename.toupper() -eq "APPLICATION") {$DB.ExecuteNonQuery($("CREATE TABLE [dbo].[Application]([ServerName] [varchar](50),[DistinguishedName] [varchar](500),[Application] [varchar](500))"));}

    if ($tablename.toupper() -eq "DISK") {$DB.ExecuteNonQuery($("CREATE TABLE [dbo].[Disk]([ServerName] [varchar](50),[DistinguishedName] [varchar](500),[Disk] [varchar](50),[SizeMB] [bigint] NULL,[FreeMB] [bigint] NULL,[PercFree] [varchar](50))"));}

}

 

$all=insert-table $tablename

Invoke-Expression $($all)

write-host $all

$DB.ExecuteNonQuery("delete from [$tablename]")

#----------------- query to collect info only win2003Server from computer table ------------------------

$servers=$($DB.ExecuteWithResults("select name,distinguishedname from computer where OperatingSystem='Windows Server 2003' ").TABLES[0].ROWS)

 

$servers | %{$dn2=$_.distinguishedname; $inserisco="Ps"+$tablename+" "+$_.name+" "+"""$dn2"""; write-host $inserisco; Invoke-Expression $($inserisco)}

 

 

$path="d:\ad\"+$tablename+"_delta.ps1"

&($path)

 

}

 

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

# -- Create and import data - table Application

collect Application

# -- Create and import data - table SysInfo

collect SysInfo

# -- Create and import data - table Disk

collect Disk

# -- Create and import data - table NetInfo

collect NetInfo

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($_)} }