Developer42

2014-09-26

PowerShell Script :: Get AD Users by Email (Advanced)

Filed under: Microsoft, Technology — Tags: , , , , , , , — Developer42 @ 22:19

Here’s a script I knocked up today.

Auto.ps1 allows me to host the script on a server (or wherever), whilst others can use it by dropping input files into a queue folder, without needing to touch powershell (which may be scary to non-programmers, or may require additional setup or permissions).
ADGetUsersByEmailAdvanced.ps1 gets AD info based on email addresses; without requiring exchange modules, and includes workarounds to cope with missing information.

Auto.ps1

Monitors a folder for new text files. Once found, passes that file to a script to be processed. On completion moves the source file to the same directory & renames to begin with the same timestamp as the generated output file.

$infile = "\\myServer\myShare\Scripts\powershell\ADGetUsersByEmail\in\*.txt"

while (1 -eq 1) {
	#wait for a new file
	while(!(Test-Path $infile)) {Start-Sleep -s 30;}
	write-host "new input file found"
	Get-ChildItem $infile | %{
		$fileTimestamp = "{0:yyyy-MM-dd_HHmmss}" -f (get-date).ToUniversalTime()
		$inputFile = $_.fullname
		$exportFile = "{0}\out\{1}_output.csv" -f $PSScriptRoot,$fileTimestamp
		$inputFileMoved = "{0}\out\{1}_{2}" -f $PSScriptRoot,$fileTimestamp,$_.name
		write-host ("source: {0}" -f $inputFile)
		write-host ("output: {0}" -f $exportFile) 
		write-host ("archive: {0}" -f $inputFileMoved) 
		.\ADGetUsersByEmailAdvanced.ps1 -sourceFile $_.fullname -exportFile $exportFile 
		write-host "processed"
		Move-Item $inputFile $inputFileMoved
		write-host "archived"
	}
}

ADGetUsersByEmailAdvanced.ps1

Given a text file containing a list of email addresses, attempts to resolve those to corresponding AD users, taking advantage of email information in AD where available, then gracefully degrading to more hacky methods. Works its way through a list of domains in case the users are in the same company but on a different domain.

Param(
  [string]$sourceFile
  ,[string]$exportFile 
)
#$sourceFile = '.\sourceEmails.txt'
#$exportFile = ".\output_{0:yyyy-MM-dd_HHmmss}.csv" -f (get-date).ToUniversalTime()
$domains = 'eu','usa','myDomain','anotherDomain' # domain points to the GC; could equally list GC server names here, though this version's more user friendly

#create dummy; allows us to put in values for any unfound items (currently just using null, but we can easily amend if desired)
$adDummy = New-Object –TypeName PSObject –Prop @{
	emailSearched	= $null;
	notFound		= $true;
	sAmAccountName 	= $null;
	fullname		= $null;
	firstname		= $null;
	lastname		= $null;
	cn				= $null;
	countryCode		= $null;
	country			= $null;
	#title			= $null;
	title			= $null;
	department		= $null;
	company			= $null;
	email			= $null;	
	adEmail			= $null;
	proxyEmail		= $null;
}

function RemoveEmailDomain($email) {
  return $email -replace "(\S*)@\S*", '$1'
}
function IsFirstDotLast($name) {
	return $name -like '*.*'
}
function GetFirstName($name) {
	return $name -replace "(\S*?)\.\S*", '$1'
}
function GetLastName($name) {
	return $name -replace "\S*?\.(\S*)", '$1'
}
function GetFirstNamePartial($name) {
	return $name.substring(0,[system.math]::min(3,$name.length))
}
function GetLastNamePartial($name) {
	return $name.substring([system.math]::max($name.length-3,0),[system.math]::min(3,$name.length))
}
function GetADUserByIdentity($id, $domain) {
	#trycatch since erroraction not recognised on this type of command & I don't want error messages polluting my output
	write-host "searching for id '$id' on domain '$domain'"
	try { 
		Get-ADUser -Identity $id -Server $domain -Properties sAmAccountName, displayName, givenName, surname, distinguishedName, countryCode, c, title, department, company, emailAddress, proxyAddresses
	} catch {}
}
function GetADUserFiltered($filter, $domain) {
	Get-ADUser -Filter $filter -Server $domain -Properties sAmAccountName, displayName, givenName, surname, distinguishedName, countryCode, c, title, department, company, emailAddress, proxyAddresses
}
function GetADUserByEmailAddress($email, $domain) {
	write-host "searching for email '$email' on domain '$domain'"
	$filter = {emailAddress -eq $email} 
	GetADUserFiltered $filter $domain
}
function GetADUserByProxyAddress($email, $domain) {
	write-host "searching for proxy '$email' on domain '$domain'"
	$psBugFixSearchUser = "*:$_*"
	$filter = {proxyAddresses -like $psBugFixSearchUser}
	GetADUserFiltered $filter $domain
}
function GetADUserByFullName($name, $domain) {
	$fn = GetFirstName $name
	$ln = GetLastName $name
	write-host "searching for name '$fn', '$ln' on domain '$domain'"
	$filter = {(givenname -eq $fn) -and (surname -eq $ln)}
	GetADUserFiltered $filter $domain
}
function GetADUserByPartialName($name, $domain) {
	$fn = "{0}*" -f (GetFirstNamePartial $name)
	$ln = "*{0}" -f (GetLastNamePartial $name)
	write-host "searching for partial name '$fn', '$ln' on domain '$domain'"
	$filter = {(givenname -like $fn) -and (surname -like $ln)}
	GetADUserFiltered $filter $domain | where { ($_.givenname + $_.surname) -eq $name }
}
function FindBestMatch($email) {
	$result = $null
	#$domains | %{ $result=GetADUserByEmailAddress $email $_; if($result) {return $result;} } #doesn't play as expected
	foreach($domain in $domains) { $result=GetADUserByEmailAddress $email $domain; if($result) {return $result;} }
	foreach($domain in $domains) { $result=GetADUserByProxyAddress $email $domain; if($result) {return $result;} }
	$name = RemoveEmailDomain $email
	foreach($domain in $domains) { $result=GetADUserByIdentity $name $domain; if($result) {return $result;} }
	if(IsFirstDotLast($name)) {
		foreach($domain in $domains) { $result=GetADUserByFullName $name $domain; if($result) {return $result;} }
	} else {
		foreach($domain in $domains) { $result=GetADUserByPartialName $name $domain; if($result) {return $result;} }
	}	
	return $adDummy;
}

#define a function for later use
#get list of emails (ignore blanks)
$emails = (Get-Content $sourceFile) | where{ $_ -gt '' }

#get data from ad and stick it in an csv (or error to console if not found)
$emails | %{ 
	#get ad user by email address
	$adUser = FindBestMatch($_);
	if($adUser.notFound) {
		write-host ":(" -ForegroundColor Red
	} else {
		write-host ":)" -ForegroundColor Green
	}
	#return object replresenting results.
	New-Object –TypeName PSObject –Prop @{
		emailSearched	= $_;
		found			= if($adUser.notFound){$false} else {$true};
		sAmAccountName 	= $adUser.sAmAccountName;
		fullname		= $adUser.displayName;
		firstname		= $adUser.givenname;
		lastname		= $adUser.surname;
		cn				= $adUser.distinguishedName;
		countryCode		= $adUser.countryCode;
		country			= $adUser.c;
		#title			= $adUser.personalTitle;
		title			= $adUser.title;
		department		= $adUser.department;
		company			= $adUser.company;
		adEmail			= $adUser.emailAddress;
		proxyEmail		= [string]$adUser.proxyAddresses; #string joins the array down to a single string value
	}
} | export-csv $exportFile -notype #stick output to file

Script could be improved by allowing auto to kick off jobs so multiple instances of the worker script can be run simultaneously. Also changing the main script to make use of workflows and take advantage of the parallel foreach method should significantly improve it’s performance. However I’m still pretty new to PowerShell, so those steps will have to come later.

2014-09-17

T-SQL: Generate Series: Getting a list of numbers in a given range.

Filed under: Microsoft, SQL Server, Technology — Tags: , , , , , , , , , — Developer42 @ 01:27

I recently came across the Postgres generate_series function whilst reading a blog post.
So far as I can tell, there’s no equivalent in T-SQL. To make up for this, I coded my own, making use of the recursive nature of common table expressions:

create function dbo.generate_series
(
	  @start bigint
	, @stop bigint
	, @step bigint = 1
	, @maxResults bigint = 0 --0 = unlimited
)
returns @results table(n bigint)
as
begin

	--avoid infinite loop (i.e. where we're stepping away from stop instead of towards it)
	if @step = 0 return
	if @start > @stop and @step > 0 return
	if @start < @stop and @step < 0 return
	
	--ensure we don't overshoot
	set @stop = @stop - @step

	--treat negatives as unlimited
	set @maxResults = case when @maxResults < 0 then 0 else @maxResults end

	--generate output
	;with myCTE (n,i) as 
	(
		--start at the beginning
		select @start
		, 1
		union all
		--increment in steps
		select n + @step
		, i + 1
		from myCTE 
		--ensure we've not overshot (accounting for direction of step)
		where (@maxResults=0 or i<@maxResults)
		and 
		(
			   (@step > 0 and n <= @stop)
			or (@step < 0 and n >= @stop)
		)  
	)
	insert @results
	select n 
	from myCTE
	option (maxrecursion 0) --sadly we can't use a variable for this; however checks above should mean that we have a finite number of recursions / @maxResults gives users the ability to manually limit this 

	--all good	
	return
	
end

Example Usage:

--check we get expected results
select * from generate_series(1, 10, default, default)
select * from generate_series(10, 5, -1, default)
select * from generate_series(1, 10, 4, default)
select * from generate_series(1, 10, default, 2)
select * from generate_series(1, 10, 4, -1)

--check we don't get results if we have "invalid" input
select * from generate_series(1, 10, 0, default)
select * from generate_series(10, 1, default, default)
select * from generate_series(10, 5, 1, default)
select * from generate_series(1, 10, -4, default)

NB: Should you wish to generate a series of dates instead of a series of numbers, check my comments here: http://stackoverflow.com/questions/1478951/generate-a-resultset-of-incrementing-dates-in-tsql/25881077#25881077

2014-09-10

Powershell Script: Monitor Connection Status

Filed under: Technology, Uncategorized — Tags: , , , , , , — Developer42 @ 20:59

This script monitors a connection, reporting when the connection drops and when it’s recovered.
The script monitors by pinging an IP, or by running an HTTP Get against a defined IP/URL.
The URL option’s provided in case run from behind a firewall blocking ICMP.

function Run-ConnectionTest($site, $waitSecs, $firewall)
{
	$state = 'unknown'
	$previousState = 'unknown'

	while($true)
	{
		#get current connection state
		if(Test-MyConnection $site $firewall)
		{
			$state = 'up'
		}
		else
		{
			$state = 'down'
		}
		#report change in state
		if ($state -ne $previousState) 
		{
			$previousState = $state
			$now = Get-Date
			if($state -eq 'up')
			{
				Write-Host -ForegroundColor Green ("{0:yyyy-MM-dd hh:mm:ss}: Connection up!" -f $now)
			}
			else
			{
				Write-Host -ForegroundColor Red ("{0:yyyy-MM-dd hh:mm:ss}: Connection down..." -f $now)
			}
		}
		#wait before checking again
		Start-Sleep -Seconds $waitSecs
	}
}
function Test-MyConnection($site, $firewall)
{
	$result = $false
	if($firewall) #if there's a firewall blocking pings, pinging won't work
	{
		try 
		{ 
			$response = (Invoke-WebRequest -Uri $site)
			#write-host('OK')
			#write-host($response.StatusCode)
			if(($response.StatusCode -ge 200) -and ($response.StatusCode -lt 400)) #treat everything from 200 to 399 as connection successful
			{
				$result = $true
			}
			else #everything outside of 200-399 is treated as a connection issue
			{
				$result = $false
			}
		} 
		catch #all exceptions are treated as connection issues, regardless of http response status code
		{
			#write-host('KO')
			#write-host($_.Exception)
			#$_.Exception.Response.StatusCode.Value__
			$result = $false
		}
	}
	else
	{
		$result = Test-Connection -computer $site -count 1 -quiet
	}
	return $result
}

Clear
#Run-ConnectionTest '8.8.8.8' 5 $false
Run-ConnectionTest 'http://www.google.com' 30 $true

Blog at WordPress.com.

%d bloggers like this: