Filling Active Directory descriptions

At work we have the procedure that every computeraccount has a specific description containing location, pc model, username. People were filling all this info by hand, not really efficient but it sort of worked the first few weeks…ended up with having 1000+ computer accounts having no description at all.
Now the good part, last year we’ve purchased Lansweeper for some reporting on servers within out domains. Now we didn’t setup the filters so we would only scan servers so we now have all servers/desktops/notebooks into a SQL database for Lansweeper. All info we needed for setting the description is in there we just needed to glue it together so here we go.

  • First install Lansweeper and setup scanning
  • Add IP-locations so you can get the fysical address of a device (minor disadvantage that Lansweeper isn’t checking the site a client is in)
  • Copy paste script below and suit for your needs 😉
import-module ActiveDirectory

$con = new-object system.data.SqlClient.SqlConnection("Data Source=Databaseserver\Instance;Initial Catalog=lansweeperdb;Persist Security Info=True;User ID=lansweeperuser;Password=sqlpassword;Connection Timeout=400");
$con.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $con
$Command.CommandText = "Select DISTINCT dbo.tblComputers.Computername,
(Select dbo.tsysIPLocations.IPLocation From dbo.tsysIPLocations
Where dbo.tblComputers.IPNumeric Between dbo.tsysIPLocations.StartIP
And dbo.tsysIPLocations.EndIP) as IPLocation, dbo.tblComputers.ComputerUnique,
dbo.tblComputers.Domain, dbo.Web40OSName.OSname,
dbo.tblOperatingsystem.Description, dbo.tblComputers.Lastseen,
dbo.Web40OSName.Compimage As icon, dbo.tblComputers.Username,
tblComputersystem.Model, tblADComputers.Description As desc2,
dbo.tblComputers.LastknownIP, tblADComputers.DNSHostname,
tblComputersystem.Manufacturer, tblADusers.Name
From dbo.tblOperatingsystem Inner Join
dbo.tblComputers On dbo.tblOperatingsystem.Computername =
dbo.tblComputers.Computername Inner Join
dbo.web40ActiveComputers On dbo.tblComputers.Computername =
dbo.web40ActiveComputers.Computername Inner Join
dbo.Web40OSName On dbo.Web40OSName.Computername =
dbo.tblComputers.Computername Inner Join
tblComputersystem On dbo.tblComputers.Computername =
tblComputersystem.Computername Inner Join
tblADComputers On dbo.tblComputers.Computername = tblADComputers.Computername
Inner Join
tblADusers On dbo.tblComputers.Username = tblADusers.Username
Where dbo.tblComputers.Domain In ('YOURDOMAIN') And
dbo.Web40OSName.OSname Not Like '%server%' And tblADComputers.Description Is
Null And
tblComputersystem.Domainrole = 1
"

$Reader = $Command.ExecuteReader()

#Loop Through Results
while ($Reader.Read()) {
$newdesc = new-object System.Text.Stringbuilder

if($Reader['IPLocation'] -ne $null -and $Reader['IPLocation'].length -gt 5)
{
$newdesc.append($Reader['IPLocation']) | Out-Null
$newdesc.append(" - ") | Out-Null
}

$newdesc.append($Reader['Manufacturer']) | Out-Null
$newdesc.append($Reader['Model']) | Out-Null

if($Reader['Name'].length -gt 5)
{
$newdesc.append(" - ") | Out-Null
$newdesc.append($Reader['Name']) | Out-Null
}
elseif($Reader['Username'] -ne "Administrator")
{
$newdesc.append(" - ") | Out-Null
$newdesc.append($Reader['Username']) | Out-Null
}

#Finally set new description
Set-ADComputer $Reader['DNSHostname'].Split(".")[0] -Description $newdesc.ToString()
}

Example:
Computername: PC01
Description: London – Dell Latitude D630 – Barbara Contoso

Leave a Reply

Your email address will not be published. Required fields are marked *