Developer42

2015-06-16

FTP Traffic Simulator / Test Script (PowerShell)

Filed under: Microsoft, Technology — Tags: , , , , , , , , , — Developer42 @ 01:38

We recently had an intermittent communication issue between an FTP server and the file share on which it stored its files.
In order to ensure frequent activity (with some variety to make it realistic) between the servers whilst investigating I created the below script.

The script connects to an FTP server, then loops (a given number of times) creating a dummy (Hunka.Junk) file of a random size, then uploading this file before pausing a random amount of time, then repeating for the next iteration.

cls
 
$fnTemplate = "hunka.junk.{0}.jpg"
$fnTempPath = "c:\temp\ftpTestScript\"
$user = "myFtpUser"
$pass = "myFtpPassword"
$ftp = "ftp://myFtpDns.myCompany.com/{0}"; 
$minFileSizeBytes =  20 * 1024 #min file size is 20kb
$maxFileSizeBytes = 100 * 1024 #max file size is 100kb
$minWaitSecs = 0 
$maxWaitSecs = 30
$stopConditionCounter = 1000000 #just in case we forget about the script have a stopping condition so it doesn't fill the server with junk
 
$webclient = New-Object -TypeName System.Net.WebClient;
$webclient.Credentials = New-Object System.Net.NetworkCredential($user,$pass) 
#$webclient.ContentType = "application/octet-stream";
#$webclient.Proxy = $null;
 
0..$stopConditionCounter | %{
    #setup variables for this iteration
    $filesize = Get-Random -Minimum $minFileSizeBytes -Maximum $maxFileSizeBytes #filesize in bytes
    $sleepTimeSecs = Get-Random -Minimum $minWaitSecs -Maximum ($maxWaitSecs-1)  #time to wait between uploads in seconds
    $sleepTimeSecs = Get-Random -Minimum $minWaitSecs -Maximum ($sleepTimeSecs+1) #increase liklihood of getting smaller wait times
    $fn = ($fnTemplate -f [string]([System.Guid]::NewGuid())) #get a unique filename
    write-output ("{0:0,000,000}: Generating {1:000} kb hunka.junk file ({2}), uploading to ftp, then sleeping {3} seconds" -f $_,($filesize/1024),$fn,$sleepTimeSecs)
    #create file
    $fnFullPath = (join-path $fnTempPath $fn)
    fsutil file createnew $fnFullPath $filesize
    #upload file
    $uri = New-Object -TypeName System.Uri -ArgumentList ($ftp -f $fn);
    $webclient.UploadFile($uri, $fnFullPath);
    #clean-up
    remove-item $fnFullPath
    #sleep
    Start-Sleep -seconds $sleepTimeSecs
}

2015-05-19

Hunka.Junk Files

Filed under: Microsoft, Movies, Technology — Tags: , , , , , , — Developer42 @ 17:21

A few times, even with capacity planning in place, we’ve hit disk capacity limits.
When this occurs you search for content which can be deleted to free up some space to get you moving again.
Here’s my new quick fix for this: HunkaJunk files.
Create dummy files which take up space, but can be quickly & easily deleted without risk; giving you teh wiggle room you need to get moving again.

There are various ways this could be done, but a useful tool is FSUtil.
This allows you to create blank files which take up a defined number of bytes.
e.g. to create a 1GB file you could run the following code from the Windows command line.

fsutil file createnew c:\PurgeForDiskSpace\Hunka.Junk 1073741824

– c:\PurgeForDiskSpace\Hunka.Junk – is the filename of your junk file (you can call it anything; just make it obvious)
– 1073741824 – is the filesize in bytes. NB: 1GB = 1024MB = 1024 * 1024 KB = 1024 * 1024 * 1024 bytes = 1,073,741,824 bytes.

2015-05-18

Microsoft Dynamics AX 2012 Chart of Accounts Advanced Validation Rules SQL

A follow up to an earlier article: Microsoft Dynamics AX 2012 Chart of Accounts Validation Rules SQL.

The Advanced Rules can be found in AX under: General Ledger > Setup > Chart of accounts > Configure account structures > select account structure > Advanced rule.
Here’s a small sample of our advanced rules from AX:

Advanced Rules in AX

Advanced Rules in AX

Here’s the same rule (results row 9), as produced by the below SQL:

AX Advanced Rules in SQL

AX Advanced Rules in SQL

Full SQL:

select dr.name AdvancedRule
, dr.description Name
, a.Name AccountStructure
, a.Description ASDescription
, case when dr.IsDraft =1 then 'Draft' else 'Active' end [Status] --seems to just duplicate the IsDraft info
, dr.IsDraft Draft
, drc.Filter
, dh.Name ARSAdvancedRuleStructure
, dh.DESCRIPTION ARSName
from DimensionRule dr
inner join DimensionHierarchy a on a.recid = dr.AccountStructure and a.Partition = dr.Partition
inner join (
       select drcO.DimensionRule
       , drcO.Partition
       , stuff
       (
              (
                     select ' And ' + da.Name + ' '
                     + case
                           when coalesce(drcI.WildCardString,'') > ''
                                  then case
                                         when drcI.WildCardString like '\%%\%' escape '\' then 'contains '
                                         when drcI.WildCardString like '%\%' escape '\' then 'begins with '
                                         when drcI.WildCardString like '\%%' escape '\' then 'ends with '
                                         else 'equals '
                                  end + quotename(replace(drcI.WildCardString,'%',''),'''')
                           when drcI.IsFromOpen = drcI.IsToOpen then --if both are 0 or both are 1 (second scenario seems weird, but that's how the app behaves)
                                  case
                                         when drcI.RangeFrom = drcI.RangeTo then 'Is ' + quotename(drcI.RangeFrom,'''')
                                         else 'Between ' + quotename(drcI.RangeFrom,'''') + ' through ' + quotename(drcI.RangeTo,'''')
                                  end
                                            --in the below statements IsFromOpen and IsToOpen seem to behave backwards to what you'd expect; but again that's what the app does
                           when drcI.IsFromOpen=1 and drcI.IsToOpen=0 then 'Greater than or equal to ' + quotename(drcI.RangeFrom,'''')
                           when drcI.IsFromOpen=0 and drcI.IsToOpen=1 then 'Less than or equal to ' + quotename(drcI.RangeTo,'''')
                           else '-UNEXPECTED SCENARIO; SPEAK TO JB-' --this should nevere happen
                     end 
                     FROM DimensionRuleCriteria drcI
                     inner join DimensionAttribute da on da.RecId = drcI.DimensionAttribute and da.Partition = drcI.Partition
                     where drcI.DimensionRule = drcO.DimensionRule and drcI.Partition = drcO.Partition
                     order by da.Name --drcI.RecId
                     for xml path(''), type
              ).value('.','nvarchar(max)')
       ,1,4,'Where') Filter
       FROM DimensionRuleCriteria drcO
       group by drcO.Partition
       , drcO.DimensionRule
) drc on drc.DimensionRule = dr.RecId and drc.Partition = dr.Partition
inner join DimensionRuleAppliedHierarchy drah on drah.DimensionRule = dr.RecId and drah.Partition = dr.Partition
inner join DimensionHierarchy dh on dh.recid = drah.DimensionHierarchy and dh.Partition = drah.Partition
order by a.Name, dr.name, dh.Name, dr.IsDraft

NB: One anomaly you may notice is some rules appear twice; once as Draft and once as Active. This is where an existing rule is being edited; the existing rule remains active whilst its replacement is created in draft status. When you browse the rules you’ll see the draft rule; however the active rule is the one being applied in any validation.

PowerShell Script :: Combine-Paths

Filed under: Microsoft, Technology — Tags: , , , , , , , — Developer42 @ 18:34

When you wish to create paths based on various combinations of strings, the following accepts a root plus an array of arrays and returns all combinations:

cls
function combine-paths([string]$root,[array]$paths)
{
    if(($paths -ne $null) -and ($paths.length -gt 0))
    {
        [array]$roots = $paths | select -first 1
        [array]$tail = @($paths | select -skip 1) #@() used to prevent final array from becoming an array of strings instead of a single-element array containing an array of strings
        $roots | %{combine-paths -root (join-path $root $_) -paths $tail}
    }
    else
    {
        $root
    }
}

$x = ('PROD','UAT','SIT'),('SystemA','SystemB','SystemC'),'Interfaces',('In','Out')
combine-paths -root '\\myFileShare\' -paths $x

NB: If you had multiple roots you could do the following:

'c:\','\\somewhere' | %{combine-paths -root $_ -paths $x}

Or amend the function to become a cmdlet taking root from the pipeline to simplify the above further / or add logic to allow the root to be blank/unspecified and to just pass `$_` instead of `(join-path $root $_)` where root is blank/null.
I’ve not done that here in order to keep the code short & simple.

2015-03-09

Microsoft Dynamics AX 2012 Chart of Accounts Validation Rules SQL

In doing some analysis work on performance, a consultant recently asked for an extract of all of our AX2012’s COA validation rules.
The below SQL is an attempt at regenerating the rules based off the data in the various related tables.
NB: I’m not 100% certain that this gives all rules as they’d appear in the application, but a few initial tests prove promising; so hopefully this SQL can be reused by others with similar requirements.

Here’s a small sample of our validation rules from AX:

AX 2012 Chart Of Accounts Validation Rules Screenshot

AX 2012 Chart Of Accounts Validation Rules Screenshot

Here’s the same rules, as produced by the below SQL:

AX 2012 Chart Of Accounts Validation Rules SQL Results Screenshot

AX 2012 Chart Of Accounts Validation Rules SQL Results Screenshot

Full SQL:

set transaction isolation level read uncommitted
go

;with c1 as
(
       select DimensionConstraintNode
       , STUFF
       (
              (
                     SELECT ';' + case 
                           when WILDCARDSTRING > '' then WILDCARDSTRING 
                           when RangeFrom=RangeTo then RangeFrom 
                           else concat(RangeFrom,'..',RangeTo) 
                     end 
                     FROM DimensionConstraintNodeCriteria inside
                     where inside.DimensionConstraintNode = outside.DimensionConstraintNode
                     ORDER BY ORDINAL, RECID
                     FOR XML PATH (''),TYPE
              ).value('.','nvarchar(max)')
              ,1,1,''
       ) AS Rules
       from DimensionConstraintNodeCriteria outside
       group by DimensionConstraintNode
)
, ActiveDCN as
(
       select dct.dimensionhierarchy 
       , dcn.DIMENSIONCONSTRAINTTREE
       , dhl.level_
       , dhl.dimensionattribute
       , da.name AttributeName
       , dcn.dimensionhierarchylevel
       , dcn.PARENTCONSTRAINTNODE
       , dcn.RecId
       , c1.Rules
       from DimensionConstraintTree dct 
       inner join DIMENSIONHIERARCHYLEVEL dhl on dhl.dimensionhierarchy = dct.dimensionhierarchy
       inner join DIMENSIONATTRIBUTE da on da.recid = dhl.dimensionattribute 
       inner join DimensionConstraintNode dcn on dcn.DIMENSIONCONSTRAINTTREE = dct.recid and dcn.dimensionhierarchylevel = dhl.RECID
       left outer join c1
       on c1.DimensionConstraintNode = dcn.recid
    where (dcn.activeto = '1900-01-01 00:00' or dcn.activeto >= getutcdate())
    and (dcn.activefrom = '1900-01-01 00:00' or dcn.activefrom <= getutcdate())
)
select dh.name HierarchyName
, concat(dcn1.AttributeName,': ',coalesce(dcn1.Rules,'*')) R1
, concat(dcn2.AttributeName,': ',coalesce(dcn2.Rules,'*')) R2
, concat(dcn3.AttributeName,': ',coalesce(dcn3.Rules,'*')) R3
, concat(dcn4.AttributeName,': ',coalesce(dcn4.Rules,'*')) R4
from DIMENSIONHIERARCHY dh
inner join ActiveDCN dcn1 on dcn1.dimensionhierarchy = dh.recid and dcn1.level_ = 1 --not sure if level’s required; it does affect results; keeping in for now as improves performance (though also reduces result set by 75%)
left outer join ActiveDCN dcn2 on dcn2.ParentConstraintNode = dcn1.Recid --and dcn2.level_ = 2
left outer join ActiveDCN dcn3 on dcn3.ParentConstraintNode = dcn2.Recid --and dcn2.level_ = 3
left outer join ActiveDCN dcn4 on dcn4.ParentConstraintNode = dcn3.Recid --and dcn2.level_ = 4
--where dcn1.Rules like '%640103%' --to produce screenshot
order by dh.name, dcn1.DIMENSIONCONSTRAINTTREE

2014-10-13

PowerShell Script :: Get HotFix Info from Local Machine & Web

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

In answering a question on Stack Overflow[1], I discovered that the Get-Hotfix cmdlet doesn’t list all hotfixes on your machine. Rather, if the fix is included in a CU, details of the installed CU will be presented, with no apparent way to drill down into the information and check for a specific fix.
So far as I can tell, there are no services / open databases which would allow you to programmatically retrieve this information. The only way currently is to manually head to the CU or fix’s related knowledge base site and read the contents.
As a very rough hack-around, I created a scraping script which will interrogate the MS knowledge-base for information on each hotfix, finding whether it’s a CU or not (based on the hotfix’s description in the page’s title) and if so try to find all related fixes. I only go down one level – to avoid the risk of an infinite loop & save time; should anyone want to go deeper, a caching mechanism could be used to store previous results and thus prevent the risk of a loop and also potentially improve performance.
As with most of the scripts on this site, this is more for me to play with PowerShell than to be of much practical use; but hopefully it could be to someone…

function get-hotfixInfo()
{
	process 
	{
		#$url = "http://support2.microsoft.com/kb/{0}" -f ($_.HotFixId -replace "KB(\d*)",'$1')
		$url = $_.Caption
		if($url -like "*.microsot.com")
		{
			$url = "{0}/{1}" -f $url,($_.HotFixId -replace "(KB)(\d*)",'$1/$2') 
		}
		try
		{
			$response = (Invoke-Webrequest $url -ea stop)
		} catch {
			$response = @{
				ParsedHTML = @{
					Title = "{0}`n`nURL: {1}" -f $error[0].Exception,$url 
				}
			}
		}
		$html = $response.ParsedHTML
		$isCU = $html.title -like "*cumulative*update*"
		$kblets = $null
		if($isCU) #this bit can be even slower than the above, hence only run if we believe we have a CU
		{
			$baseUri = $response.BaseResponse.ResponseURI
			$kblets = $html.getElementsByTagName('a') `
				| ? { ($_.parentNode.tagname -eq 'TD') -and ($_.parentNode.nextsibling.tagname = 'TD') } `
				| ? { $_.className -eq 'KBlink' } `
				| % { New-Object -TypeName PSObject -Prop @{
					Id = "KB{0}" -f $_.innerText
					Uri = (new-object System.URIBuilder($baseUri.scheme,$baseUri.dnssafehost,$baseUri.port,($_.href -replace "about:/(.*),'$1'"))).ToString()
					Title = $_.parentNode.nextsibling.innerText
				}}
		}
		New-Object -TypeName PSObject -Prop @{
			Id = $_.HotFixId
			IsCU = $isCU 
			Title = $html.title
			Source = $_.source
			Description = $_.description
			InstalledBy = $_.installedby
			InstalledOn = $_.installedon
			Uri = "http://support2.microsoft.com/kb/{0}" -f ($_.HotFixId -replace "KB(\d*)",'$1')
			KBlets = $kblets
		}
	}
}

#demos

#get the hotfix id for IE11 CU Feb 2014, and it's component hotfixes
get-hotfix -id kb2909921 | get-hotfixInfo | %{ $_; $_.KBlets | %{ $_; }} | ft -property id,title -autosize

#get the first 2 cumulative update hotfixes
get-hotfix | select -first 2 | get-hotfixInfo | ?{$_.isCU} 


2014-10-12

PowerShell Script: Demo of Calling a (StackExchange’s) Rest Web Service

Filed under: Microsoft, Technology — Tags: , , , , , , — Developer42 @ 01:25

Warning: this post is not useful unless you just like playing with scripts & APIs.

Reading a few posts on StackExchange’s Meta site, I saw that there was demand for, but no intent to, create some kind of synonym sub-type feature for tags.
e.g. Users looking for items tagged PowerShell would also see those tagged only PowerShell-1.0, PowerShell-2.0, PowerShell-ISE, etc.
Users looking for PowerShell-1.0 are being more specific though, so shouldn’t see those tagged simply PowerShell, and definitely not those tagged PowerShell-2.0.

Since SE have no intention of creating this, I figured it would be useful to have a site which could present a view over Stack Exchange sites, giving users these additional features.
Since I don’t have time to do that though, I satisfied my craving to build something by knocking up a quick script to play with the SE API.
…And since implementing synonyms would require a huge dictionary, which I’d have to create, I sufficed myself with simply playing with the API without the synonymn hierarchy feature.
So this is just me playing about with my new favourite scripting language and an API I hope one day to play more with (though then it would be with some other language).
Here’s what I came up with…

Code:

$tagList='c#','.net'
$tags = $taglist -join ';'
[Reflection.Assembly]::LoadWithPartialName("System.Web") | Out-Null
$tags = [System.Web.HttpUtility]::UrlEncode($tags)
$url = "http://api.stackexchange.com/2.2/search/advanced?page=1&amp;pagesize=10&amp;order=desc&amp;sort=creation&amp;ans
wers=0&amp;closed=False&amp;tagged={0}&amp;site=stackoverflow" -f $tags
$json = Invoke-RestMethod $url
$json.items | select title,link | %{ "{0}`n{1}`n" -f [System.Web.HttpUtility]::HtmlDecode($_.title), $_.link; }

Output:

ASP.NET Web Forms and MySql Entity Framework: "Nested transactions are not supported"
http://stackoverflow.com/questions/26320679/asp-net-web-forms-and-mysql-entity-framework-nested-transactions-are-not-su
ppo

.NET C# - Both Bitmap and Image Loads Images Smaller than Expected
http://stackoverflow.com/questions/26320637/net-c-both-bitmap-and-image-loads-images-smaller-than-expected

Mono TypeLoadException in an executable
http://stackoverflow.com/questions/26320146/mono-typeloadexception-in-an-executable

Developing a music player in C# without using com component
http://stackoverflow.com/questions/26319978/developing-a-music-player-in-c-without-using-com-component

Fastest way to get directory data
http://stackoverflow.com/questions/26319973/fastest-way-to-get-directory-data

c# datagridview multi-page print
http://stackoverflow.com/questions/26319852/c-datagridview-multi-page-print

How to access an object in c sharp from outside my method?
http://stackoverflow.com/questions/26319550/how-to-access-an-object-in-c-sharp-from-outside-my-method

.NET &amp; jQuery Jcrop Initialization Causing Double Page Load
http://stackoverflow.com/questions/26319428/net-jquery-jcrop-initialization-causing-double-page-load

Why i'm getting exception InvalidOperationException when using backgroundworker?
http://stackoverflow.com/questions/26319024/why-im-getting-exception-invalidoperationexception-when-using-backgroundwor
ker

Index was out of range error when I add a UserControl to a panel, Microsoft VS C#
http://stackoverflow.com/questions/26318864/index-was-out-of-range-error-when-i-add-a-usercontrol-to-a-panel-microsoft-
vs-c

So not that useful, but a fun play project.

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
Older Posts »

The WordPress Classic Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 776 other followers

%d bloggers like this: