Skip navigation

Tag Archives: active directory

Get Service Pack from AD Objects…

So in an attempt to quickly extract OS version and Service Pack for a few machines in an environment the idea was presented to pull the data from active directory. The properties exist so the logic seemed sound; and as we’ve discussed this before it’s a pretty easy task with the active directory module in PowerShell, and here’s the code:

$list = gc computers.txt
Import-Module ActiveDirectory
ForEach($item in $list){
                $ado = (get-adcomputer $item -Properties *)
                $pso = New-Object PSObject
$pso | Add-Member -Name "Computer Name" -MemberType NoteProperty `
		-Value $ado.CN
$pso | Add-Member -Name "Operating System" -MemberType NoteProperty `
		-Value $ado.OperatingSystem
$pso | Add-Member -Name "Service Pack" -MemberType NoteProperty `
		-Value $ado.OperatingSystemServicePack
$pso
}

Assuming for the sake of example the name of this script is, get-adservicepack.ps1, and you’ve got your computers.txt file with your computer names in it then we’d run it like this.

./get-adservicepack.ps1 | export-csv -NoTypeInformation MyAdOutput.csv

So what’s happening?

First, we’re taking the get-content command to pull data from a local text file “computers.txt” into a data object and then iterating through it sequentially.  We are then using the computer name as the lookup name with the get-adcomputer cmdlet along with all it’s ad properties and assign it to a variable called ado.

Now we create a PowerShell object and begin to give it some noteproperties with values pulled from our ad object we created from the ad cmdlet then echo it’s contents out by calling it.

When we run the script and pipe it’s output to export-csv –NoTypeInformation we are taking that output and putting it directly into a csv without any of the object information, otherwise it’s a tabled console output.

PowerShell is so boss sometimes…

Maybe we just do all this in one line?

gc computers.txt|ForEach-object{Get-ADComputer $_ -properties *|select -Property name,operatingsystem,operatingsystemservicepack}|export-csv -notypeinformation output.csv

Scroll that line, like a boss.

Gallery entry on Script Center if you want to rate it

AD Asset Management tool…

Alright, so Active Directory Asset Management.  What is it?

Well a, not so, unique problem facing every enterprise large or small, is asset management.

Now asset management is not a one dimensional issue, there are requirements and facets to it that shift depending on the person you ask.  From an engineering and maintenance standpoint at my company we have needs of tying important information as closely to the asset as possible, yet removed enough that it’s accessible even when the asset is not.  So what does that mean?

I want to have information tied to the machine but not dependent on the machine.  Something that contains properties that can be modified by the machine, yet retrievable apart from the machine.  Well that’s a relatively simple solution, LDAP, or Active Directory as the case may be.

In this case, my co-worker David Renfrow has opted to utilize the AD Computer Object Description property to store the strings that uniquely identifies our asset.  At present the tool is used to search for specific values in objects, to get/set the string values, and lastly to export findings from the search to a csv report bundled into a single self contained HTA.

image

For the next release I intend to implement error handling, and a potential DB write out function as well as domain search base targeting. At present it only pulls from the Domain of the querying asset, so in a multi domain forest that can be a problem.

Current Version Code Below:

<html>
	<head>
		<title>Active Directory Asset Manager (ADAM)</title>
<!--<description>
	Tool:		Active Directory Asset Manager
	
	Authors: 	David Renfrow, Daniel Belcher 
	
	Tool Info: 	The tools purpose is to retrieve, query, and 
				set system specific information	to a string in 
				the ADO Description property.  This string is 
				specially formatted	to retrieve in a specific way:
				
		<Server>;<Sixdot>;<Purpose>;<Location>;<Domain>;<SLA>;
	<SrvContact>;<SrvManager>;<SGPrimary>;<SGBackup>;<DBAPrimary>;<DBABackup>
				
</description>-->
	<HTA:APPLICATION 
		ID="ADAM" 
		APPLICATIONNAME="ADAM"
		BORDER="thin"
		SINGLEINSTANCE="yes"
	>

<SCRIPT LANGUAGE="VBScript">
'-----------Application Initialization-----------------------------------------
'On Error Resume Next
Dim oConn, oCmd, item, oComputer, strDomain, Dict

Sub Window_onLoad
	window.resizeTo 625,725
	
	Set oConn = CreateObject("ADODB.Connection")
		oConn.Provider = "ADsDSOObject"
	Set oCmd =  CreateObject("ADODB.Command")
	Set Dict = New cls_Dict

	strDomain = Dict.DistinguishedDomainName
	
	
End Sub 
'----------------Controls------------------------------------------------------
Sub getproc()
	On Error Resume Next

	strComputer = ServerName.Value
	ADLookUp(strComputer)
	Description = oComputer.Get("Description")
	If Err.Number <> 0 Then
		Err.Clear
		Description = ";;;;;;;;;;;"
	End If
	strOut = Split(Description,";")

	DataArea.innerHtml =	"<table border=""""1"""">" & "<tr>" & _
		"<td>Server</td>"&"<td>"&UCase(strComputer)& "</td>" & "</tr><tr>" & _
		"<td>SixDot</td><td>"&strOut(1) & "</td>" & "</tr><tr>" & _
		"<td>Purpose</td><td>"&strOut(2) & "</td>" & "</tr><tr>" & _
		"<td>Location</td><td>"&strOut(3) & "</td>" & "</tr><tr>" & _
		"<td>Domain</td><td>"&strOut(4) & "</td>" & "</tr><tr>" & _
		"<td>SLA</td><td>"&strOut(5) & "</td>" & "</tr><tr>" & _
		"<td>Srv Contact</td><td>"&strOut(6) & "</td>" & "</tr><tr>" & _
		"<td>Srv Manager</td><td>"&strOut(7) & "</td>" & "</tr><tr>" & _
		"<td>SG Primary</td><td>"&strOut(8) & "</td>" & "</tr><tr>" & _
		"<td>SG Backup</td><td>"&strOut(9) & "</td>" & "</tr><tr>" & _
		"<td>DBA Primary</td><td>"&strOut(10) & "</td>" & "</tr><tr>" & _
		"<td>DBA Backup</td><td>"&strOut(11) & "</td>" & "</tr><tr>" & _
		"<td>Last Updated</td><td>"&strOut(12) & "</tr></table>"
			
	servername.value = UCase(strComputer)
	sixdot.value = strOut(1)
	purpose.value = strOut(2)
	loc.value = strOut(3)
	domain.value = strOut(4)
	sla.value = strOut(5)
	srvcontact.value = strOut(6)
	srvmanager.value = strOut(7)
	sgprimary.value = strOut(8)
	sgbackup.value = strOut(9)
	dbaprimary.value = strOut(10)
	dbabackup.value = strOut(11)

	oConn.Close
End Sub

Sub setproc()
	strComputer = ServerName.Value
	ADLookUp(strComputer)
	if sixdot.value = "" then sixdot.value = "NA"
	if purpose.value = "" then purpose.value = "NA"
	if loc.value = "" then loc.value = "NA"
	if domain.value = "" then domain.value = "NA"
	if sla.value = "" then sla.value = "NA"
	if srvcontact.value = "" then srvcontact.value = "NA"
	if srvmanager.value = "" then srvmanager.value = "NA"
	if sgprimary.value = "" then sgprimary.value = "NA"
	if sgbackup.value = "" then sgbackup.value = "NA"
	if dbaprimary.value = "" then dbaprimary.value = "NA"
	if dbabackup.value = "" then dbabackup.value = "NA"
	oComputer.Put "Description", servername.value&";"&sixdot.value&";"& _
			purpose.value&";"&loc.value&";"&domain.value&";"&SLA.value&";"& _
			srvcontact.value&";"&srvmanager.value&";"&sgprimary.value&";"& _
			sgbackup.value&";"&dbaprimary.value&";"&dbabackup.value&";"&date
	oComputer.SetInfo
	oConn.Close
End Sub

Sub clearproc
	dataarea.InnerHTML = ""
	servername.value = ""
	srvcontact.value = ""
	sixdot.value = ""
	srvmanager.value = ""
	purpose.value = ""
	sgprimary.value = ""
	loc.value = ""
	sgbackup.value = ""
	domain.value = ""
	dbaprimary.value = ""
	sla.value = ""
	dbabackup.value = ""
End Sub

Sub searchproc()

	If Dict.Exists("ADRecords") Then
		Dict.Remove("ADRecords")
	End If
	
	msg = Null
	
	Call ADSearch(search.value)
	For Each item In Dict.ReturnArray("ADRecords")
		temp = Split(item,"|x|")	
		msg = msg &"<b>Name:</b><em> " & UCase(temp(0)) & "</em><br>" _
				& "<b>Description:</b><em> " & temp(1) & "</em><br><br>"
	Next
	
	dataarea.innerhtml = "<input type="&Chr(34)&"button"&Chr(34)&" value="& _
	Chr(34)& "Export to CSV"&Chr(34)&" onclick="&Chr(34)&"export"&Chr(34)& _
	"/><br>" & msg
	
End Sub

Sub export()

	Dim oFso, FileHandle
	Set oFso = CreateObject("Scripting.FileSystemObject")
	temp = Split(Date,"/")
	sDate = temp(0)&temp(1)&temp(2)
		
	Set FileHandle = oFso.OpenTextFile _
		(Dict.CurrentDir&"ServerOut-"&sDate&".csv", 2, True)
		FileHandle.WriteLine "Server,SIXDOT,Purpose,Location,Domain,SLA," & _
"Srv Contact,Srv Manager,SG Primary,SG Backup,DBA Primary,DBA Backup,Modified"
		
		For Each item In Dict.ReturnArray("AdRecords")
			temp = Split(item,"|x|")
			strWrite = Replace(temp(1),",","-")
				strWrite = Replace(strWrite,";",",")
					strWrite = Replace(strWrite,"-",";")
			FileHandle.WriteLine strWrite
		Next
	FileHandle.Close
	
	dataarea.innerhtml = "<p>Report written to:<br>" & _
					"<em>"&Dict.CurrentDir&"serverout-"&sDate&".csv</em></p>"
End Sub

'-----------------Working Functions--------------------------------------------
Public Function ADLookUp(strComputer)

	oConn.Open "Active Directory Provider"
	Set oCmd.ActiveConnection = oConn
	oCmd.CommandText = _
		"Select * from 'LDAP://"&strDomain&"' " _
        & "Where objectCategory='computer' AND name = '"& strComputer &"'" 
	oCmd.Properties("searchscope") = 2
	oCmd.Properties("Page Size") = 1000
	Set oRecord = oCmd.Execute

	For Each item In oRecord.Fields
		Set oComputer = GetObject(item)
	Next
End Function

Public Function ADSearch(strProperty)

	oConn.Open "Active Directory Provider"
	Set oCmd.ActiveConnection = oConn
	oCmd.CommandText = "Select Name, Description, DistinguishedName from " &_
	"'LDAP://"&strDomain&"' Where objectCategory='computer'" 
	oCmd.Properties("Page Size") = 1000
	oCmd.Properties("searchscope") = 2
	Set oRecord = oCmd.Execute
	oRecord.MoveFirst
	
	Do Until oRecord.EOF
	On Error Resume Next 
		Set oComputer = GetObject _
		("LDAP://" & orecord.Fields("distinguishedName").value)
			Description = oComputer.Get("Description")
			If InStr(1,LCase(Description), LCase(strProperty)) <> 0 Then
				Call Dict.ItemList("ADRecords",orecord.Fields("name").value& _
				"|x|" & Description)
			End If
		Description = Null
		oRecord.MoveNext
	Loop
	oConn.Close
End Function
'----------------Class Objects-------------------------------------------------

Class cls_Dict
'Class wrapper for the scripting.dictionary
	Private oDict, oNet, Comparemode, strSplit, oFso, oWShell, oADSI

'---------------------------------------------------------

Private Sub Class_Initialize()
'Dictionary class init subroutine    
    If Debugmode Then On Error Goto 0 Else On Error Resume Next
		Set oDict 	= CreateObject("Scripting.Dictionary")
		Set oNet	= CreateObject("Wscript.Network")
		Set oFso	= CreateObject("Scripting.FileSystemObject")
		Set oWShell = CreateObject("Wscript.Shell")
		Set oADSI = CreateObject("ADSystemInfo")
		
		Dim strUserDomain : strUserDomain = oADSI.DomainDNSName
		Dim strDomain : strDomain = Split(strUserDomain,".")
		For Each item In strDomain
			strDNDomain = strDNDomain & "DC="&item&","
		Next
		oDict.CompareMode = 1
			strSplit = "|:|"
		Call oDict.Add("CurrentDir",oWShell.CurrentDirectory&"")
		Call oDict.Add("computername", oNet.Computername)
		Call oDict.Add("Windir",LCase(oWShell.ExpandEnvironmentStrings _
			("%windir%")))
		Call oDict.Add("CurrentUser",LCase(oNet.UserName))
		Call oDict.Add("Domain",LCase(oNet.UserDomain))
		Call oDict.Add("DomainDN",Left(strDNDomain,(Len(strDNDomain)-1)))
		Call SetOsVer
End Sub

'---------------------------------------------------------

Private Sub Class_Terminate()
'Dictionary class termination subroutine
	If IsObject(oDict) then Set oDict = Nothing
End Sub

'---------------------------------------------------------

Public Property Get CurrentDir
'Returns Current Directory for the script
	CurrentDir = oDict.Item("CurrentDir")
End Property

'---------------------------------------------------------

Public Property Get ComputerName
'Returns the machine name for the current machine
	ComputerName = oDict.Item("computername")
End Property

'---------------------------------------------------------

Public Property Get CurrentUser
'Returns the machine name for the current machine
	CurrentUser = oDict.Item("CurrentUser")
End Property

'---------------------------------------------------------

Public Property Get Domain
'Returns the machine name for the current machine
	Domain = oDict.Item("Domain")
End Property

'---------------------------------------------------------

Public Property Get DistinguishedDomainName
'Returns the Distinguished Name for the Domain
	DistinguishedDomainName = oDict.Item("DomainDN")
End Property

Public Property Get Windir
'Returns the windows directory for the local machine
	Windir = oDict.Item("windir")
End Property

Public Property Get SystemRoot
'Returns the appropriate system directory system32 or syswow64

	If InStr(StrReverse(oDict.Item("CurrentOsVer")), "46x") <> 0 Then 
		SystemRoot = Windir & "syswow64"
	Else
		SystemRoot = Windir & "system32"
	End If

End Property
'---------------------------------------------------------

Public Sub Add(strKey,strValue)
'Method to Add a key and item
	If Debugmode Then On Error Goto 0 Else On Error Resume Next

    Dim EnvVariable, strSplit

    	strSplit = Split(strValue, "%")
	If IsArray(strSplit) Then 
    	EnvVariable = oWShell.ExpandEnvironmentStrings _
    		("%" & strSplit(1) & "%")
    	strValue = strSplit(0) & EnvVariable & strSplit(2)
    			If strValue = "" Then
    				strValue = strSplit(0)
    			End If
    End If
	If oDict.Exists(strKey) Then
		oDict(strKey) = Trim(strValue)
	Else
		oDict.Add strKey, Trim(strValue)
	End If
End Sub

'---------------------------------------------------------

Public Function Exists( strkey)
'Method to check existance of a key
    If Debugmode Then On Error Goto 0 Else On Error Resume Next
	
	If oDict.Exists(strKey) then 
		Exists = True
	Else
		Exists = False
	End If

End Function

'---------------------------------------------------------

Public Function Keys()
'Method to retrieve an array of keys
    If Debugmode Then On Error Goto 0 Else On Error Resume Next

	If IsObject(oDict) Then
		Keys = oDict.Keys
	End If
End Function

'---------------------------------------------------------

Public Function Items()
'Method to retrieve an array of items
    If Debugmode Then On Error Goto 0 Else On Error Resume Next

	If IsObject(oDict) Then
		Items = oDict.Items
	End If
End Function

'---------------------------------------------------------
Private Sub SetOsVer()
'Sets a comparable OSVer key item into the dictionary
		If DebugMode Then On Error Goto 0 Else On Error Resume Next

		Dim x, VersionCheck	
		
	VersionCheck = owShell.RegRead("HKLMsoftwaremicrosoft" _
					& "windows ntcurrentversionproductname")

			If ofso.folderexists("c:windowssyswow64") Then
				x = "x64"
			Else
				x = "x86"
			End If
		Call oDict.Add("CurrentOsVer",VersionCheck & " " & x)
End Sub
	Public Property Get OsVer()
		OsVer = oDict.Item("CurrentOsVer")
	End Property

'---------------------------------------------------------
Public Property Get AppName()

	AppName = Left(WScript.ScriptName, Len(WScript.ScriptName) - 4)
End Property 

'---------------------------------------------------------

Public Property Get Key( strKey)
'Property to retrieve item value from specific key
    If Debugmode Then On Error Goto 0 Else On Error Resume Next

		Key = Empty
	If IsObject(oDict) Then
		If oDict.Exists(strKey) Then Key = oDict.Item(strKey)
	End If
End Property

'---------------------------------------------------------

Public Sub ItemJoin(strKey, strItem)
'Method to concactenate new items under one key at the end of the string
    If Debugmode Then On Error Goto 0 Else On Error Resume Next
	Dim concat
	If Not oDict.Exists(strKey) Then
		Call oDict.Add(strkey, stritem)
	Else
		concat = oDict.Item(strKey)
		concat = concat & " " & strItem
			oDict.Remove(strKey)
		Call oDict.Add(strKey,concat)
	End If
End Sub

'---------------------------------------------------------

Public Sub ItemList( strKey,  strItem)
'Method to concactenate new items under one key at the end of the string
    If Debugmode Then On Error Goto 0 Else On Error Resume Next
	Dim concat
	If Not oDict.Exists(strKey) Then
		Call oDict.Add(strkey, stritem)
	Else
		concat = oDict.Item(strKey)
		concat = concat & "|:|" & strItem
			oDict.Remove(strKey)
		Call oDict.Add(strKey,concat)
	End If
End Sub

'---------------------------------------------------------

Public Sub ItemJoinRev( strKey,  strItem)
'Method to concactenate new items under one key at the start of the string
    If Debugmode Then On Error Goto 0 Else On Error Resume Next
	Dim concat
	If Not oDict.Exists(strKey) Then
		Call oDict.Add(strkey, stritem)
		Exit Sub
	Else
		concat = oDict.Item(strKey)
		concat = strItem & " " & concat
			oDict.Remove(strKey)
		Call oDict.Add(strKey,concat)
	End If
End Sub
	
'---------------------------------------------------------	

Public Function ReturnArray( strKey)
'Method to return an item as an array
    If Debugmode Then On Error Goto 0 Else On Error Resume Next
	
	Dim ItemToSplit, ItemArray
	
	ItemToSplit = oDict.item(strKey)
	
	ItemArray = Split(ItemToSplit, strSplit)
	
	ReturnArray = ItemArray	
	
End Function
	
'---------------------------------------------------------	

Public Sub Remove( strKey)
'Method to remove a key value
	oDict.Remove(strKey)
End Sub

'---------------------------------------------------------

Public Sub RemoveAll()
'Method to remove all data from the dictionary
	oDict.RemoveAll
End Sub

End Class
'------------------------------------------------------------------------------
</script>

<body bgcolor="silver">
	<table>
	<tr>
	<td>Server</td><td><input type="text" name="servername" size="30"></td>
	<td>Srv Contact</td><td><input type="text" name="srvcontact" size="30"></td>
	</tr>
	<tr>
	<td>SIXDOT</td><td><input type="text" name="sixdot" size="30"></td>
	<td>Srv Manager</td><td><input type="text" name="srvmanager" size="30"></td>
	</tr>
	<tr>
	<td>Purpose</td><td><input type="text" name="purpose" size="30"></td>
	<td>SG Primary</td><td><input type="text" name="sgprimary" size="30"></td>
	</tr>
	<tr>
	<td>Location</td><td><input type="text" name="loc" size="30"></td>
	<td>SG Backup</td><td><input type="text" name="sgbackup" size="30"></td>
	</tr>
	<tr>
	<td>Domain</td><td><input type="text" name="domain" size="30"></td>
	<td>DBA Primary</td><td><input type="text" name="dbaprimary" size="30"></td>
	</tr>
	<tr>
	<td>SLA</td><td><input type="text" name="sla" size="30"></td>
	<td>DBA Backup</td><td><input type="text" name="dbabackup" size="30"></td>
	</tr>
	</table>
	<p>
	<input type="button" value="Get" onclick="getproc"/>
	<input type="button" value="Set" onclick="setproc"/>
	<input type="button" value="Clear" onclick="clearproc"/>
	<input type="button" value="Search" onclick="searchproc"/>
	<input type="text" value="keyword" name="search">
	</p>
	<hr>
	<div id = "DataArea"></div>
</body>

</html>

AD to DB

Ok, so as I stated at the tail end of my last post, here’s your PowerShell to export data from Active Directory to a MSSQL Database.  This code can be easily modified to pull whatever AD info you want and to insert it.  I make use of the System.Data.SqlClient namespace, so it’s worth mentioning that you can also use MySql.Data.MySqlClient or System.Data.OracleClient for your needs.  You can examine what I’m using via the SqlClient namespace and easily correlate it back to your respective need.

 

<#
Script: AD to DB
Author: Daniel Belcher
#>
#$ErrorActionPreference = "silentlycontinue"
$CHECK = Get-Module ActiveDirectory
IF(!$CHECK) {
	Write-Host -ForegroundColor Red `
	"Can't find the ActiveDirectory module, please insure it's installed.`n"
	Break}
ELSE {Import-Module ActiveDirectory}

#\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
<#Variable String values for customizing
	the DB and to write to and OU to lookup against
		as well as the time range in days#>
<#DataBase Name:#>
$DB = "Data"
<#SQL Server Name:#>
$SQLSRVR = "localhost\sqlexpress"
<#Table to Create:#>
$TABLE = "ADExport"	
<#Days old based on activity according to passwordlastset:#>
$DAYS = 180
<#Root OU to Search:#>
$ORGUNIT = "Root OU to Search"
<#Table Create String#>
$CREATE = "CREATE TABLE $TABLE (AD_Machine varchar(150) not null PRIMARY KEY, `
	OU varchar(100),DistinguishedName varchar(max),PasswordLastSet datetime, `
	OS varchar(100),ServicePack varchar(100))"
#/////////////////////////////////////////////////////////
<#Setting up object variables
	to be used for AD lookup#>
	$TIME = [datetime]::today.adddays(-$DAYS)
$LOOKUP = (Get-ADOrganizationalUnit -LDAPFilter "(Name=$ORGUNIT)")
	$ADARRAY = (Get-ADComputer -SearchBase $lookup.DistinguishedName -properties `
			name,passwordlastset,operatingsystem,operatingsystemservicepack `
			-Filter 'passwordlastset -ge $TIME')  
<#Setting up object variables
	to be used for AD lookup#>
	$TIME = [datetime]::today.adddays(-$DAYS)
$LOOKUP = (Get-ADOrganizationalUnit -LDAPFilter "(Name=$ORGUNIT)")
	$ADARRAY = (Get-ADComputer -SearchBase $lookup.DistinguishedName -properties `
			name,passwordlastset,operatingsystem,operatingsystemservicepack `
			-Filter 'passwordlastset -ge $TIME')  
<#Connect and cleanup the AD table
	Connection remains open for writting#>
$SQLCON = New-Object System.Data.SqlClient.SqlConnection("Data Source=$SQLSRVR; `
			Initial Catalog=$DB;Integrated Security=SSPI")
	$SQLCON.open()
		$SQL = $SQLCON.CreateCommand() 
			$SQL.CommandText ="DROP TABLE $TABLE"
				$SQL.ExecuteNonQuery() > $null
		$SQL.CommandText = $CREATE
			$SQL.ExecuteNonQuery() > $null
<#Begin loop through the ADARRAY for
	Variables and begin inserting Rows to table#>
	$X = 0
ForEach($OBJECT in $ADARRAY){
	
	$NAME = $OBJECT.Name
		$OU = $OBJECT.DistinguishedName.ToString().Substring($OBJECT.Name.ToString().Length+4)
			$DN = $OBJECT.DistinguishedName
				$PWDLS = $OBJECT.PasswordLastSet
					$OS = $OBJECT.OperatingSystem
						$SP = $OBJECT.OperatingSystemServicePack
						
#\\\\Any Table Data to be written goes here:\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
	$INSERT = "INSERT $TABLE VALUES ('$NAME','$OU','$DN','$PWDLS','$OS','$SP')"
	$SQL.CommandText = $INSERT
#////////////////////////////////////////////////////////////////////////////////////////	
		$SQL.ExecuteNonQuery() > $null
$X = $X + 1			
}
"$X Records written to $TABLE in database $DB on server $SQLSRVR"
<#Cleanup variables and close connections#>
$SQLCON.Close()

 

Overview: 

We have 5 key variables to work from here:

  1. $DB – Name of Database to write to
  2. $SQLSRVR – Name/IP of the SQL server
  3. $TABLE – Table to build this data to
  4. $DAYS – Age of machines to pull from AD
  5. $ORGUNIT – The base OU to search through recursively

 

These of course are set to preference or need.

Now what the script does is, it figures out the date using the datetime function in .net factored by the number of days back you declare.

Then it looks up and stores the base OU we will search through then builds the object with the AD Objects it finds.

Next it instantiates the SqlClient object and makes a connection to the server and database we specified in our variables.  We drop the table specified, then recreate the table.

Now the real work begins, we loop through the AD Objects one by one and cherry pick the specific properties out that we want from the object (an area that PowerShell excels at I might add) and declare them as variables to be finally written to our database table.

And for good measure we count each loop and store that so we can see how many records were found and attempted, then cleanup all our variables used during the script.

PowerShell Profiles, GUN, and GUID. Not what you think…

As a Systems Engineer/SCCM Administrator I spend a lot of time parsing through data, and assisting support technicians in tracking down failing assets.  Now mind you, I have plenty of reports that give me the information I need to identify the machine and users and techs responsible etc, but what happens when I get a random list of employee names from a project manager that has 0 access to user ids or asset numbers for machines?  Well, I have to find that information, then spend time later pointing them to resources I’ve made available for them; but that’s another topic….

Anyway, I face both problems, I’ll receive a list of userids or usernames and have to resolve them one against another.  Well thanks to powershell I’m able to do so quickly and easily through profile functions.  Now, I’ll explain the benefits of profile functions after the code below:

 

Import-Module activedirectory

#-------------------------------------------------------------------- 
Function Get-UserName { 
[CmdletBinding()]

PARAM($USERID) 
Get-ADUser $USERID | select name 
} 
Set-Alias gun Get-UserName 
#-------------------------------------------------------------------- 
Function Get-Userid { 
[CmdletBinding()] 
PARAM([string]$NAME) 
$NAME = $NAME + "*" 
    Get-ADUser -Filter {Name -like $NAME} | select samaccountname,name 
} 
Set-Alias guid Get-Userid 
#--------------------------------------------------------------------

 How do I use profile functions?!?

Powershell, much like the BASH shell in Unix/Linux, has a profile “script” so to speak at startup.  There is a global one found at:

  • %WinDir%System32WindowsPowerShellv1.0Profile.ps1
  • %WinDir%System32WindowsPowerShellv1.0Microsoft.PowerShell_Profile.ps1
  • %WinDir%System32WindowsPowerShellv1.0Microsoft.PowerShellISE_Profile.ps1

The same filename syntax is used for the user profile versions:

  • %UserProfile%My DocumentsWindowsPowerShellProfile.ps1
  • %UserProfile%My DocumentsWindowsPowerShellMicrosoft.PowerShell_Profile.ps1
  • %UserProfile%My DocumentsWindowsPowerShellMicrosoft.PowerShellISE_Profile.ps1

See a pattern?  Simple enough right?  None of these profiles exist by default though, they must be created.  The names are fairly indicative of what they control, but here’s a breakdown:

  • Profile.ps1
    • This governs startup of both the standard powershell, and the ISE.
  • Microsoft.PowerShell_Profile.ps1
    • This governs startup of the standard powershell console only.
  • Microsoft.PowerShellISE_Profile.ps1
    • This governs startup of the ISE powershell console only.

Simple enough right?  Now, for the sake of simplicity, lets build a current user version of the profile.ps1 and save the above code to it.  Make sure you’ve installed the activedirectory cmdlet module provided with windows 7. Now launch powershell and viola you should now have the cmdlets:

  • Get-UserName
  • Get-UserID

and their aliases:

  • GUN
  • GUID

Ok, now what?

Here’s the thing about profile functions.  You can treat them like cmd-lets now.  That also means that you can script against them.  Consider them a static variable for every powershell session that you have configured with this profile.

Pretty cool huh?  One of the most powerful features of the shell is it’s configurability, and profile functions and aliases are the tip of that spear.

In the case of user name capture, or id capture, I’m but a simple gc and for-each statement away from processing the list given to me.

I hope this helps broaden your practical understanding of profiles, and gets your creative juices flowing for building your own administrative tool kits.  Happy scripting.

Get-InactiveADComputers

So recently we had to perform an AD cleanup of aged computer objects in our environment.  For a lot of companies this is a pretty standard procedure, for others, it’s just ignored (as was the case for us for a while).

Anyway, in the course of completing this task we had looked at a few scripts and tools that others had written/published around the internet and I inevitably got the bright idea to sit and write a tool for us to use in PowerShell.  So I setup my goals and fired up the old editor and went to work.

Objectives:

  • Identify aged objects
    • To identify aged objects, I went with the PasswordLastSet property of the object. There really isn’t a much better one to utilize, and it works off a 30 day cycle so it’s close enough.
  • Write out data for records
    • For write out I built a switch (–report) that will utilize the export-csv cmdlet to export a record of machines to the current users desktop.
  • Move objects to different OU
    • For moving objects I used the Move-ADObject cmdlet from the activedirectory module available with win 7 and server 2008 features.
  • Generic (no non standard add ins)
    • To keep it generic I chose to use the activedirectory module since we are running on 2008 servers, and migrating to windows 7. This insures future state usage, and prevents additional downloads or installs for functionality.
  • Modular (sometimes you just want a report)
    • To keep it modular, I built the (-move) switch. Unless the user explicitly defines this then the script will only report what was requested. Either to console output, or to a csv on the desktop (-report).
  • Versatile for targeting object date ranges and OUs
    • To insure versatility, I left 3 variables to be declared. $TIME, $TARGETR, $TARGETM. Date range, target OU to read, and Target OU to move respectively.
  • Script initially with potential to integrate in profile as a function
    • To build it as a standalone script, I originally had a (-help) switch that would output similar information as the get-help blah-blah –examples. Now with it functionized, I’ve removed that and added an overall function name to the code.

How it works:

Once applied to the PowerShell profile it can be called using get-adinactivecomputer.  By default it requires a date range either specified in <number>d for days or <number>y for years and a target OU to search from.  If these variables aren’t applied at the command line the function will request them at run time.

If –move and –report are specified then the function will need a target OU to move the items to and will generate a list with Object names and PasswordLastSet dates on the desktop and attempt to move them to the specified OU.  The switches must be declared at the command line but all variables are requested at run time if not stated before hand.

Now the nuts and bolts…..

admove1

First we check for the $TIME variable and request if it’s not present, then insure it’s in string format.  We’ll check that string for a d or a y to determine how to handle the string and then convert it to an appropriate integer value.  We’ll also bailout if we can’t distinguish the range (since this is a has the potential of causing serious AD harm we want to insure the user is accurate in their input).

Second we’ll check for the –report switch and if it’s present we’ll run the internal ADCLEANUP function and pipe it to export-csv $homedesktopinactivecomputers.csv.  If it’s not then we’ll just generate output to the console.  We pass the $TIME and $TARGETR variable to this function.

Third we check for the –move switch, and if it’s present run the internal function MOVEOLD and pass it the $TARGETM variable.

Fourth and final step, we nullify a global variable set during the ADCLEANUP function.

 

admove2

First step of the ADCLEANUP function is to insure the activedirectory module is loaded.  Next it verifies that a $TARGETR variable exists, if not it will prompt for one.  It then will take and search AD for the distinguished name of the OU provided. 

Next we build a date range variable using .Net Date Math.  We will subtract the total number of days by the integer value provided in $TIME from Today’s date and store that in $COMPARE. 

Now we declare a global variable $ADLIST that grabs all computer objects with PasswordLastSet property less than or equal to $COMPARE from the $TARGETR OU.

We then process those objects with a ForEach into a PSObject for reporting purposes specifying the Name and PasswordLastSet properties.  I did this mainly to guarantee a clean csv export.

 

admove3

The MOVEOLD function is fairly straight forward.  We check for the $TARGETM variable, if it doesn’t exist we prompt for an input.  We then build $TARGETM into a manageable string, then again we set it as a proper OU.  We run the $ADLIST through another ForEach statement and use Move-ADObject to relocate each $ITEM to $TARGETM.

 

I posted this script on the script center repository.  I didn’t build in any error catching which I should have, and plan to revisit that at a later date.  I enjoyed writing it so much I wanted to take some time to dissect it and discuss it a bit.  Hopefully this information proves useful to someone with similar goals and or general interest. 

For the the Microsoft.Powershell_profile.ps1 and Microsoft.PowershellISE_Profile.ps1 version of the script, perform the following steps in order:

Uncomment lines 4 and 193

Delete lines 7 through 50

Delete: ,[switch]$HELP from line 6

Delete line 46

Modify line 60 to read:                    `n`nTry get-help Get-InactiveADComputer -Examples`n";break}