Export to file and send with sftp

This might take you 4 minutes to read.

I have customers that need to send an export of users with some special rolegroup memberships to a sftp-server. I created this script and created a scheduled task.

It depends on psftp.exe is in the same directory as the script and that there is an export-directory.

the script is somewhat documented, and should be quite easily be understood, I think.

##
## Get all users that is memeber of a group
## iterera through the users and calculate numbers of groups the user is member of (starting with "eh_"
## if a user is member of two or more role-groups, deliver the user without a role and send an email to "senderMail"
## do a check that there is only one of each user_id in each file that is sent.
##

import-module activedirectory

function runScript {

	$path = "Path_To_Script_Folder"
	$exportPath = $path + 'exports'
	$companyname = "companyname"
	$companyid   = "companyid"
	$senderMail = "mail_to_user_that_will_have_error_messages"
	$strUN = "FTP_Username"
	$strPW = "FTP_Password"
	$strDomain = "FTP_fqdn"
	$smtpServer = "fqdn_mailserver"
	$keepExportFiles = 20
	$smtpSender = "sendingservername"
	$roleGroupPrefix = "eH_"
	$memberGroup = $roleGroupPrefix + "AllUsers" # will be "eH_Allusers"

	Set-Location $path
	$date = get-date
	$message = ""
	$result = @{}

	$members = get-adgroupmember $memberGroup
	foreach ($member in $members) {
		if ($member -ne $null) {
			$user = get-aduser $member -property sAMAccountName,givenname,surname,mail,homephone,mobile,MemberOf
			[array]$groups = (get-aduser $user -Properties MemberOf | Select-Object -ExpandProperty MemberOf | Get-ADGroup -Properties name | where {$_.name -match $roleGroupPrefix})

			if (-not($user.samaccountname -and $user.givenname -and $user.surname -and $user.mail)) {
				#user is not populated with mandatory data (samaccountname, givenname, surname och mail
			} else {

				$sAMAccountName = validateIdentity $user.sAMAccountName
				$givenName = validatName  $user.givenName
				$Surname =  validatName  $user.Surname
				$mail = validateMail $user.mail
				$homePhone = validatePhone $user.homephone
				$Mobile = validatePhone $user.mobile

				if ($groups.count -eq 2) {
					#user is in two groups and will be populated normally.
					$RoleGroup = ($groups | where {$_.name -ne $memberGroup}).name
				} elseif  ($groups.count -eq 1){
					#user is in one group and will be populated normally.
					$RoleGroup = ""
				} else {
					#user is in more than 2 groups and will not be populated normally, instead all roles will be removed and a mail will be sent.
					$RoleGroup = ""
					$message += "User: " + ($user.sAMAccountName) + " is member of wrong amount of groups: " + ( ($groups.count) - 1 ) + ". Please choose one of: " + $groupname
				}

				if ($result.ContainsKey($sAMAccountName) ){
					#samaccountname added twice.				
					$message += "User: " + ($givenname) + " " +  ($surname)  + " and " + ($result.Get_Item("$samaccountname"))[1] + " " + ($result.Get_Item("$samaccountname"))[2] + " is added twice, due to samaccountnames is to same (due to restrictions on the reciever-side)."
					$result.set_item($sAMAccountName, @($sAMAccountName, $givenName, $Surname, $mail, $homePhone, $Mobile, ""))

				} else {
					$result.add($sAMAccountName, @($sAMAccountName, $givenName, $Surname, $mail, $homePhone, $Mobile, $RoleGroup))

				}

			}
		}
	}

#skapa fil med content

	$filename = $exportPath + "USER" + $companyid + (get-date $date -format "yyyyMMddHHmmss") + ".csv"

	Add-Content $filename ("USER;"+ $companyname + ";"+ $companyid + ";" + (get-date -Format s) + ";" +$senderMail+ ";F;")  -encoding:UTF8

	foreach ($userhash in $result.getenumerator()){
			$user = $userhash.value
			$content = ""
			$content = ($user[0]) + ";" + $user[1] + ";" + $user[2] + ";" + $user[3] + ";" + $user[4] + ";" + $user[5] + ";;0;" + $user[6] + ";;;"
			Add-Content $filename  $content -encoding:UTF8
	}

	"cd $strUN `r`nput $filename `r`nbye" | out-file "batch.psftp" -force -Encoding ascii

	$colLS = & .psftp.exe -l $strUN -pw $strPW  $strDomain  -b batch.psftp -be
	$colLS

	if ($message -ne "" ) {

     $msg = new-object Net.Mail.MailMessage
     $smtp = new-object Net.Mail.SmtpClient($smtpServer)

     #Email structure 
     $msg.From = $smtpSender
     $msg.ReplyTo = $smtpSender
     $msg.To.Add($senderMail)
     $msg.subject = "Error found while exporting users to file."
     $msg.body = $message

     #Sending email 
     $smtp.Send($msg)

}

}

function validateIdentity($value) {
	if ($value.length -gt 12 ) {
		$value = $value.substring(0,12)
	}
	if ($value.substring(($value.length-1),1) -eq ".") {
		$value = $value.substring(0,$value.length-1)
	}
	return $value
}
function validatName ($value) {
	if ($value.length -gt 40 ) {
		$value = $value.substring(0,40)
	}	
	return $value
}
function validateMail ($value) {
	if ($value.length -gt 256 ) {
		$value = $value.substring(0,256)
	}	

	return $value
}
function validatePhone ($value) {
	if ($value -eq $null) {
		$value = ""
	}
	$value = $value.tostring()
	if ($value.length -gt 50 ) {
		$value = $value.substring(0,50)
	}
	if ($value.length -gt 0){
		if ($value.substring(0,1) -eq "0") {
			$value = $value.substring(1)
		}	
		if ($value.substring(0,1) -ne "+") {
			$value = "+" + $value
		}	
	}
	return $value

	$files = Get-ChildItem -Path $exportPath | Where-Object {-not $_.PsIsContainer}

	if ($files.Count -gt $keepExportFiles) {
	    $files | Sort-Object CreationTime | Select-Object -First ($files.Count - $keepExportFiles) | Remove-Item -Force 
	}

}

runScript

When you run psftp against a server, you get a prompt the first time to trust the certificate. That’s need to be done manually, with the same account as the script is triggered as.