Over simplified Active Directory GUI

An interesting and frustrating use case. I have a service desk and provisioning team that needs to know all Active Directory group memberships that dictate Application assignment. In my current work environment all AD application groups start with a prefix of "APP", so this should be easy enough. We also use Application Role Groups to ensure that if you are in department X you get all the needed software. Then there are always one-off application requests and such. Unfortunately opening Active Directory Users and Computers and looking at the MemberOf tab proves to be too complicated. I tried giving them a one liner that would simplify the process.

((Get-ADUser UserID -Properties memberof).memberof | Get-ADGroup | Where-Object {$_.name -like "APP*"}).name | Sort-Object

But this also proved too cumbersome or complex. This left me with one choice and that was to dumb it down as much as I could think of. The result is this

Enter the user's Samaccountname in the little box and click the button.

If this isn't easy enough I don't know what is.

Now, about the script that does this.


I've never done a GUI interface for PowerShell so of couse I looked to Google. What I found was a plethora of references to Sapien's PrimalForms CE. What I also found was that Sapien discontinued the product and removed it from all download sites :(

Instead of wasting more time and possibly money to find another forms/GUI editor/builder, I started piecing things together from the different examples of the code generated by these types of products. All I have to say is thank goodness I can make heads or tails of what I found. Now, I'm sure there is extra unneeded code in here, I'm just not anal enough to go in line by line to see what it works without right now. I have a functional tool that serves my purpose.

	#*=============================================
	#* Script Name: Get-AppGroups.ps1
	#* Created: 05/18/2016-published
	#* Author: David S. Elias
	#* Email: daveselias@gmail.com
	#* Requirements: PowerShell v.3.0, ActiveDirectory Module
	#*=============================================
	function GenerateForm {
	[reflection.assembly]::loadwithpartialname("System.Drawing") | Out-Null
	[reflection.assembly]::loadwithpartialname("System.Windows.Forms") | Out-Null
	$form1 = New-Object System.Windows.Forms.Form
	$richTextBox1 = New-Object System.Windows.Forms.RichTextBox
	$textBox1 = New-Object System.Windows.Forms.TextBox
	$button1 = New-Object System.Windows.Forms.Button
	$handler_button1_Click = {
	$richTextBox1.Text = ''
	########################################
	IF($PSVersionTable.PSversion.Major -ge 3){
	Try{
	Import-Module -Name ActiveDirectory -ErrorAction Stop
	Try{
	$GroupNames = ((Get-ADUser $($textBox1.Text) -Properties memberof -ErrorAction Stop).memberof | Where-Object {$_ -like "CN=app*"} | Get-ADGroup | Sort-Object name).name
	$Groups = $GroupNames | Where-Object {($_ -like "APP-*") -and ($_ -notlike "*ROLE*")} | Sort-Object
	$Role = $GroupNames | Where-Object {$_ -like "APP *ROLE*"} | Sort-Object
	$Roles = (($GroupNames | Where-Object {$_ -like "APP *ROLE*"} | Get-ADGroup -Properties memberof).memberof | Get-ADGroup).name | Sort-Object
	$Duplicates = $Groups | Where-Object {$Roles -contains $_} | Sort-Object
	IF(-not ($Groups) -and -not ($Roles)){
	Write-Warning -Message "ID $($textBox1.Text) is not a member of any application groups or roles"
	}
	IF($Groups){
	Write-Host "----User $($textBox1.Text) is a direct member of----" -ForegroundColor Cyan
	Write-TextBox "----User $($textBox1.Text) is a direct member of----"
	ForEach($GP in $Groups){
	Write-TextBox "$GP"
	}
	}
	IF($Roles){
	Write-Host "----User $($textBox1.Text) is a member of ($Role) Which is a member of----" -ForegroundColor Cyan
	Write-TextBox "----User $($textBox1.Text) is a member of ($Role) Which is a member of----"
	ForEach($R in $Roles){
	Write-TextBox "$R"
	}
	}
	IF($Duplicates){
	Write-Host "----The following memberships are duplicated----" -ForegroundColor Yellow
	Write-TextBox "----The following memberships are duplicated----"
	ForEach($Dup in $Duplicates){
	Write-TextBox $Dup
	}
	}
	}Catch{
	Write-Warning -Message "No user with ID $($textBox1.Text) could be found"
	Write-TextBox "No user with ID $($textBox1.Text) could be found"
	}
	}Catch{
	Write-Warning -Message "Module Active Directory is not available to load"
	Write-TextBox "!!!!!Module Active Directory is not available to load!!!!!!"
	}
	}
	########################################
	}
	$handler_form1_Load = {
	$textBox1.Select()
	}
	$form1.Name = 'form1'
	$form1.Text = 'Find Application Groups'
	$form1.BackColor = [System.Drawing.Color]::FromArgb(255,227,227,227)
	$form1.DataBindings.DefaultDataSourceUpdateMode = 0
	$System_Drawing_Size = New-Object System.Drawing.Size
	$System_Drawing_Size.Width = 528
	$System_Drawing_Size.Height = 404
	$form1.ClientSize = $System_Drawing_Size
	$form1.add_Load($handler_form1_Load)
	$richTextBox1.Text = ''
	$richTextBox1.TabIndex = 2
	$richTextBox1.Name = 'richTextBox1'
	$richTextBox1.Font = New-Object System.Drawing.Font("Courier New",10,0,3,0)
	$System_Drawing_Size = New-Object System.Drawing.Size
	$System_Drawing_Size.Width = 443
	$System_Drawing_Size.Height = 315
	$richTextBox1.Size = $System_Drawing_Size
	$richTextBox1.DataBindings.DefaultDataSourceUpdateMode = 0
	$System_Drawing_Point = New-Object System.Drawing.Point
	$System_Drawing_Point.X = 40
	$System_Drawing_Point.Y = 61
	$richTextBox1.Location = $System_Drawing_Point
	$form1.Controls.Add($richTextBox1)
	$textBox1.Text = ''
	$textBox1.Name = 'UserID'
	$textBox1.TabIndex = 1
	$System_Drawing_Size = New-Object System.Drawing.Size
	$System_Drawing_Size.Width = 250
	$System_Drawing_Size.Height = 20
	$textBox1.Size = $System_Drawing_Size
	$System_Drawing_Point = New-Object System.Drawing.Point
	$System_Drawing_Point.X = 40
	$System_Drawing_Point.Y = 21
	$textBox1.Location = $System_Drawing_Point
	$textBox1.DataBindings.DefaultDataSourceUpdateMode = 0
	$form1.Controls.Add($textBox1)
	$button1.UseVisualStyleBackColor = $True
	$button1.Text = 'Lookup'
	$button1.DataBindings.DefaultDataSourceUpdateMode = 0
	$button1.TabIndex = 0
	$button1.Name = 'button1'
	$System_Drawing_Size = New-Object System.Drawing.Size
	$System_Drawing_Size.Width = 75
	$System_Drawing_Size.Height = 23
	$button1.Size = $System_Drawing_Size
	$System_Drawing_Point = New-Object System.Drawing.Point
	$System_Drawing_Point.X = 308
	$System_Drawing_Point.Y = 19
	$button1.Location = $System_Drawing_Point
	$button1.add_Click($handler_button1_Click)
	$form1.Controls.Add($button1)
	$form1.ShowDialog()| Out-Null
	}
	function Write-TextBox {
	param([string]$text)
	$richTextBox1.Text += "$text`n"
	}
	# Launch the form
	GenerateForm

view raw Get-AppGroups.ps1 hosted with ❤ by GitHub

Oh wait, there's more. After a little trial and error with my target audience, an interesting development has come to light. Even this isn't easy enough. No, why you might ask? Because it requires them to:

1. Open PowerShell
2. Type the path and script name.
3. Press Enter

How dare I think they should have to do that! So how do we make this easy enough for even the untrained Monkey?

We make it so they can just click on something and not have any of those distracting and scary black or blue boxes pop up. Enter the command file and shortcut:

powershell -noprofile -Noninteractive -command \\%ScriptLocation%\Get-AppGroups.ps1

Don't forget to set the shortcut to run Minimized else you will confuse them.

I'll give it another week before I let you know if I now have a group of Happy Monkeys or if they start throwing excrement at me...

As always, let me know if you have any questions, comments, suggestions or the like.

Crawling AD group membership to create a CSV that imports into Visio

WOW, I guess this has been neglected for quite awhile. This explains just how busy I've been. Well to make up for it, here is an awesome module I created a few years back that searches Active Directory for a user or a group name, finds it's memberships through 4 levels of nesting and converts it to a CSV that Visio 2010 can import as an org chart. This worked great all on it's own, but I wasn't happy.

Where I work they tend to also add AD security groups to local server groups as well, so I wanted to know which ones. Then what about duplicate memberships through nested memberships? or circular memberships? Then there are SQL securities, and file share securities.


I did a lot of work to make this both as user friendly and as much of a one-stop-shop for everything audit departments want regarding this kid of thing.

It will create this:

And allow you to create this:

And now the Code:

	#* FileName: My-ADTools_v1.psm1
	#*=============================================
	#* Script Name: Create-ADVisio
	#* Created: 07/01/2014-published
	#* Author: David S. Elias
	#* Email: daveselias@gmail.com
	#* Requirements: Read rights in Active Directory, Server Local Admin rights, SQL SysAdmin rights
	#* Keywords: AD, SQL, share, groups, nested
	#*=============================================
	#*=============================================
	#* REVISION HISTORY
	#*=============================================
	#* Date: 07/01/2014
	#* Time: 12:30PM
	#* Issue: Local Server Share rights missing
	#* Solution: Added function Get-SharePermissions
	#* to the "-ServerCheck" switch
	#*=============================================
	#*=============================================
	#* FUNCTION LISTINGS
	#*=============================================
	#* Function: Get-LocalGroups
	#* Created: 06/25/2014
	#* Author: David S. Elias
	#* Arguments: $ComputerName (Server)
	#*=============================================
	#* Purpose: Gathers users and groups that are
	#* members of local server groups
	#*
	#*=============================================
	#* Function: Get-SQLUsers
	#* Created: 06/26/2014
	#* Author: David S. Elias
	#* Arguments: $ComputerName (Server) $GroupName (AD Group)
	#*=============================================
	#* Purpose: Interrogates SQL Server security login list
	#* gets server security and all databases
	#* containing the AD Group in it's securities
	#*=============================================
	#* Function: Get-SharePermissions
	#* Created: 07/01/2014
	#* Author: David S. Elias
	#* Arguments: $ComputerName (Server)
	#*=============================================
	#* Purpose: Retrieves Share securities (not NTFS)
	#* from all shares on server that have
	#* more than default accounts added
	#*=============================================
	#* Function: Create-ADVisio
	#* Created: 07/01/2014
	#* Author: David S. Elias
	#* Arguments: $ObjectName, $OutputPath, $DisplayDuplicates,
	#* $DuplicatesFilePath, $GroupMembers, $ServerCheck
	#*=============================================
	#* Purpose: Using the above functions creates a CSV
	#* that shows Nested AD Group Memberships
	#* that can be imported into MS Visio 2010
	#* without any additional data manipulation
	#*=============================================
	Function Get-LocalGroups{
	param(
	[parameter(Mandatory=$true,
	ValueFromPipeline=$true,
	ValueFromPipelineByPropertyName=$true)]
	[string]$ComputerName
	)
	Begin{
	# Test that the computer is powered on and available on the network
	Try{
	$Connected = (Test-Connection -ComputerName $ComputerName -Count 1 -Quiet -ErrorAction Stop)
	}Catch{
	Write-Error "Unable to connect to $ComputerName" -Category ConnectionError -TargetObject $ComputerName
	PAUSE
	#EXIT 1
	}
	# Test that the user has rights to make WMI calls to the Computer
	Try{
	$hostname = (Get-WmiObject -ComputerName $ComputerName -Class Win32_ComputerSystem).Name
	}Catch{
	Write-Error "Unable to make a WMI call to $ComputerName" -Category AuthenticationError -TargetObject $ComputerName
	PAUSE
	#EXIT 1
	}
	}
	Process{
	$GroupList=$Allmembers=$CorpMembers=@()
	$LocalGroupNames = (Get-WmiObject -ComputerName $hostname -Query "SELECT * FROM Win32_Group WHERE Domain='$Hostname'").name
	ForEach($GroupName in $LocalGroupNames){
	$wmi = Get-WmiObject -ComputerName $hostname -Query "SELECT * FROM Win32_GroupUser WHERE GroupComponent=`"Win32_Group.Domain='$Hostname',Name='$GroupName'`""
	if($wmi -ne $null) {
	ForEach($item in $wmi) {
	$data = $item.PartComponent -split "\,"
	$domain = ($data[0] -split "=")[1]
	$name = ($data[1] -split "=")[1]
	$User = ("$domain\$name").Replace("""","")
	$member=@()
	$member=New-Object PSObject
	$member | Add-Member -MemberType NoteProperty -Name "ServerName" -Value $Hostname
	$member | Add-Member -MemberType NoteProperty -Name "User/Group" -Value $User
	$member | Add-Member -MemberType NoteProperty -Name "LocalGroup" -Value $GroupName
	$Allmembers += $member
	}
	}
	}
	}
	End{
	$CorpMembers += $Allmembers | Where-Object {$_.'User/Group' -like "CORP*"} | Sort-Object localgroup
	Return $CorpMembers
	}
	}
	Function Get-SQLUsers{
	[CmdletBinding()]
	[OutputType([Object])]
	Param(
	# Computer Name to be checked for SQL.
	[Parameter(Mandatory=$true,
	ValueFromPipelineByPropertyName=$true,
	Position=0)]
	[string]
	$ComputerName,
	# Group Name to be looked for in SQL Server.
	[Parameter(Mandatory=$true,
	ValueFromPipelineByPropertyName=$true,
	Position=1)]
	[string]
	$GroupName
	)
	Begin{
	# Check for SQL PSsnappin and Cmdlets
	$Registered = (Get-PSSnapin -Registered).Name
	IF($Registered -notcontains "SqlServerCmdletSnapin100"){
	Write-Error "SQL Server Snap-in is not present" -Category NotInstalled
	PAUSE
	# EXIT 1
	}ELSEIF($Registered -contains "SqlServerCmdletSnapin100"){
	Add-PSSnapin -Name SqlServerCmdletSnapin100 -ErrorAction Stop
	$Registered = $true
	}
	Try{
	Get-Command -Name invoke-sqlcmd | Out-Null
	}Catch{
	Write-Error "Unable to find Command invoke-sqlcmd" -Category ObjectNotFound invoke-sqlcmd
	PAUSE
	#EXIT 1
	}
	# Test Server Connection
	Write-host "Testing Server Connectivity - $ComputerName" -ForegroundColor Green
	Try{
	$Alive = Test-Connection -ComputerName $ComputerName -Count 2 -Quiet -ErrorAction Stop
	}Catch{
	Write-Error "Unable to connect to Server $ComputerName" -Category ConnectionError -TargetObject $ComputerName
	PAUSE
	#EXIT 1
	}
	# Test SQL Presence
	IF($Alive -eq $true){
	Write-host "Testing SQL Service Presence on Server - $ComputerName" -ForegroundColor Green
	Try{
	$SQLService = Get-Service -Name MSSQLSERVER -ComputerName $ComputerName -ErrorAction Stop
	}Catch{
	Write-Error "MSSQLSERVER is not on Server $ComputerName" -Category InvalidResult
	PAUSE
	#EXIT 1
	}
	}ELSEIF($Alive -eq $false){
	Write-Error "Can Not Contact Server" -Category ConnectionError -TargetObject $ComputerName
	PAUSE
	Exit 1
	}
	Write-host "Checking that SQL Service is running - $ComputerName" -ForegroundColor Green
	# Test SQL Connection and Database name
	IF($SQLService.Status -like "Running"){
	Try{
	$DatabaseList = ( invoke-sqlcmd -query "SELECT name FROM master..sysdatabases ORDER BY Name" -serverinstance "$ComputerName" -Database master -QueryTimeout 100)
	}Catch{
	Write-Error "The account you are running does not have SQL access on Server $ComputerName" -Category AuthenticationError -TargetObject $ComputerName
	PAUSE
	#EXIT 1
	}
	}
	}
	Process{
	IF($Registered -eq $true){
	Try{
	$SQLUserList = (invoke-sqlcmd -query "SELECT name, sysadmin FROM master..syslogins WHERE NAME LIKE 'CORP\%' ORDER BY Name" -database "master" -serverinstance "$ComputerName" -QueryTimeout 100)
	}Catch{
	$SQLUserList = $null
	Write-Error "Unable to retrieve list of users from $ComputerName" -Category ConnectionError -TargetObject $ComputerName
	}
	IF($SQLUserList -ne $null){
	IF($SQLUserList.Name -contains "CORP\$GroupName"){
	$FoundUser = $SQLUserList | Where-Object {$_.name -like "CORP\$GroupName"}
	IF($FoundUser.sysadmin -eq 1){
	$SysAdmin = "SysAdmin"
	}ELSE{
	$SysAdmin = "N/A"
	}
	Try{
	$DBGroups=@()
	$ob = New-Object -TypeName PSObject
	$ob | Add-Member -MemberType NoteProperty -Name "Server" -Value $ComputerName
	$ob | Add-Member -MemberType NoteProperty -Name "GroupName" -Value $FoundUser.name
	$ob | Add-Member -MemberType NoteProperty -Name "Database" -Value $SysAdmin
	$DBGroups += $ob
	ForEach($DB in $DatabaseList){
	$DB=$DB.name
	$SQLGroup=$null
	$SQLGroup = (invoke-sqlcmd -query "SELECT * FROM sys.database_principals WHERE name LIKE 'CORP\$GroupName'" -database "$DB" -serverinstance "$ComputerName" -QueryTimeout 100)
	IF(($SQLGroup -as [bool]) -eq $true){
	$ob = New-Object -TypeName PSObject
	$ob | Add-Member -MemberType NoteProperty -Name "Server" -Value $ComputerName
	$ob | Add-Member -MemberType NoteProperty -Name "GroupName" -Value $SQLGroup.name
	$ob | Add-Member -MemberType NoteProperty -Name "Database" -Value $DB
	$DBGroups += $ob
	}ELSEIF(($SQLGroup -as [bool]) -eq $false){
	#Write-Output "No User Group named CORP\$GroupName found in $DB"
	}
	}
	}catch{
	Write-Output "Unable to connect to $ComputerName.$Database"
	}
	}ELSEIF($SQLUserList.Name -notcontains "CORP\$GroupName"){
	Write-Error "CORP\$GroupName was not found in SQL Server on $ComputerName" -Category ObjectNotFound $GroupName
	}
	}ELSEIF($SQLUserList -eq $null){
	Write-Error "Unable to retrieve list of users from $ComputerName" -Category ConnectionError -TargetObject $ComputerName
	}
	}
	}
	End{
	IF($DBGroups.count -gt 0){
	Return $DBGroups
	}ELSEIF($DBGroups.count -lt 1){
	Write-Error "Ain't nothing worked"
	}
	}
	}
	Function Get-SharePermissions{
	[CmdletBinding()]
	[OutputType([Object])]
	Param(
	# ComputerName to look for Shares on.
	[Parameter(Mandatory=$true,
	ValueFromPipelineByPropertyName=$true,
	Position=0)]
	[string]
	$ComputerName
	)
	Begin{
	$Shares=@()
	Try{
	$Alive = Test-Connection -ComputerName $ComputerName -Count 1 -Quiet -ErrorAction Stop
	IF($Alive -eq $true){
	Try{
	# retrieve all share names from target computer
	$Shares = Get-WmiObject -Class win32_Share -ComputerName $ComputerName | select -ExpandProperty Name
	}Catch{
	Write-Error "WMI Access denied on $ComputerName." -Category AuthenticationError -TargetObject $ComputerName
	PAUSE
	# EXIT 1
	}
	}
	}Catch{
	Write-Error "Unable to connect to $ComputerName" -Category ConnectionError -TargetObject $ComputerName
	PAUSE
	# EXIT 1
	}
	}
	Process{
	$Master=@()
	ForEach ($shareinstance in $shares){
	$ACL=@()
	# Retrieve security data from each Share in the $Shares array
	$SecuritySettings = Get-WMIObject -ComputerName $ComputerName -Class win32_LogicalShareSecuritySetting -Filter "name='$Shareinstance'"
	Try{
	$SecurityDescriptor = $SecuritySettings.GetSecurityDescriptor().Descriptor
	# Decipher UserIDs from share security data
	ForEach($ace in $SecurityDescriptor.DACL){
	$UserName = $ace.Trustee.Name
	IF($ace.Trustee.Domain -ne $Null){
	$UserName = "$($ace.Trustee.Domain)\$UserName"
	}
	IF($ace.Trustee.Name -eq $Null){
	$UserName = $ace.Trustee.SIDString
	}
	# Cheating Hash object
	[Array]$ACL += New-Object Security.AccessControl.FileSystemAccessRule($UserName, $ace.AccessMask, $ace.AceType)
	}
	}Catch{
	# This is where a log entry should go as it does not necessarily mean an error.
	}
	ForEach($Record in $ACL){
	$obj=New-Object PSObject
	$obj | Add-Member -MemberType NoteProperty -Name "Server" -Value $Computername
	$obj | Add-Member -MemberType NoteProperty -Name "ShareName" -Value $shareinstance
	$obj | Add-Member -MemberType NoteProperty -Name "IdentityReference" -Value $Record.IdentityReference
	$obj | Add-Member -MemberType NoteProperty -Name "AccessControlType" -Value $Record.AccessControlType
	$obj | Add-Member -MemberType NoteProperty -Name "FileSystemRights" -Value $Record.FileSystemRights
	$obj | Add-Member -MemberType NoteProperty -Name "IsInherited" -Value $Record.IsInherited
	$Master += $obj
	}
	}
	}
	End{
	IF($Master.count -gt 0){
	Return $Master
	}ELSEIF(($Master.count -lt 1) -and ($Shares -lt 1)){
	# No Shares were found on this server This is nor an error
	}ELSEIF(($Master.count -lt 1) -and ($Shares -gt 0)){
	ForEach($Share in $Shares){
	Write-Host "Unable to retrieve Share data from $ComputerName for Share Name $Share" -ForegroundColor Yellow
	}
	}
	}
	}
	<#
	.Synopsis
	Enter a Valid Active Directory Group name or UserID and receive a CSV output of all memberships with an option of listing the user members of each of those sub-groups.
	.DESCRIPTION
	Create-ADVisio creates a CSV file formatted for importation into Visio 2010.
	By entering a valid Active Directory UserID (samaccountname) or Group Name an organizational chart can then be generated showing memberships to within 4 nesting levels.
	As an option you can also list any user objects that are a member of each of the subsequent sub groups.
	.EXAMPLE
	Create-ADVisio -ObjectName TestUser -OutputPath 'C:\temp\Groups'
	This Example uses a UserID (samaccountname)
	GlobalOversight_INT is not a member of any other groups
	IWB_Operations_INT is not a member of any other groups
	Unique_ID Department E-mail Name Telephone Title Reports_To Master_Shape
	--------- ---------- ------ ---- --------- ----- ---------- ------------
	ID0 TestUser 0
	ID1 Domain Users ID0 0
	ID2 IWB_Operations_DEV ID0 0
	ID3 GlobalOversight_DEV ID0 0
	ID4 GlobalOversight_INT ID0 0
	ID5 IWB_Operations_INT ID0 0
	ID6 IWB_Operations_UAT ID0 0
	.EXAMPLE
	Create-ADVisio -ObjectName IWB_Operations_UAT -OutputPath 'C:\temp\Groups'
	This Example uses a Group Name
	GENERICAPP61_LOGS_RW is not a member of any other groups
	GENERICAPP62_LOGS_RW is not a member of any other groups
	GENERICAPP128_LOGS_RW is not a member of any other groups
	GENERICAPP130_LOGS_RW is not a member of any other groups
	GENERICSQL164_DatabaseName_RO is not a member of any other groups
	GENERICSQL164_DatabaseName_RWE is not a member of any other groups
	Unique_ID Department E-mail Name Telephone Title Reports_To Master_Shape
	--------- ---------- ------ ---- --------- ----- ---------- ------------
	ID0 IWB_Operations_UAT 0
	ID1 GENERICAPP61_LOGS_RW ID0 0
	ID2 GENERICAPP62_LOGS_RW ID0 0
	ID3 GENERICAPP128_LOGS_RW ID0 0
	ID4 GENERICAPP130_LOGS_RW ID0 0
	ID5 GENERICSQL164_DatabaseName_RO ID0 0
	ID6 GENERICSQL164_DatabaseName_RWE ID0 0
	.EXAMPLE
	Create-ADVisio -ObjectName IWB_Operations_UAT -OutputPath 'C:\temp\Groups' -GroupMembers
	This Example showes the difference between the previous example and the addition of the -GroupMembers switch.
	GENERICAPP61_LOGS_RW is not a member of any other groups
	GENERICAPP62_LOGS_RW is not a member of any other groups
	GENERICAPP128_LOGS_RW is not a member of any other groups
	GENERICAPP130_LOGS_RW is not a member of any other groups
	GENERICSQL164_DatabaseName_RO is not a member of any other groups
	GENERICSQL164_DatabaseName_RWE is not a member of any other groups
	Checking for User Members of GENERICAPP61_LOGS_RW
	Checking for User Members of GENERICAPP62_LOGS_RW
	Checking for User Members of GENERICAPP128_LOGS_RW
	Checking for User Members of GENERICAPP130_LOGS_RW
	Checking for User Members of GENERICSQL164_DatabaseName_RO
	Checking for User Members of GENERICSQL164_DatabaseName_RWE
	Unique_ID Department E-mail Name Telephone Title Reports_To Master_Shape
	--------- ---------- ------ ---- --------- ----- ---------- ------------
	ID0 IWB_Operations_UAT 0
	ID1 GENERICAPP61_LOGS_RW ID0 0
	ID2 GENERICAPP62_LOGS_RW ID0 0
	ID3 GENERICAPP128_LOGS_RW ID0 0
	ID4 GENERICAPP130_LOGS_RW ID0 0
	ID5 GENERICSQL164_DatabaseName_RO ID0 0
	ID6 GENERICSQL164_DatabaseName_RWE ID0 0
	ID7 GENERICAPP61_LOGS_RW SVC-FinanceAppUAT2 | SVC-FinanceAppUAT2 ID1 4
	ID8 GENERICAPP62_LOGS_RW SVC-FinanceAppUAT2 | SVC-FinanceAppUAT2 ID2 4
	ID9 GENERICAPP128_LOGS_RW HMSATH | Samual Thomas ID3 4
	ID10 GENERICAPP128_LOGS_RW SVC-FinanceAppUAT4 | SVC-FinanceAppUAT4 ID3 4
	ID11 GENERICAPP130_LOGS_RW HMSATH | Samual Thomas ID4 4
	ID12 GENERICAPP130_LOGS_RW SVC-FinanceAppUAT4 | SVC-FinanceAppUAT4 ID4 4
	ID13 GENERICSQL164_DatabaseName_RO Svc-FinanceAppUATETL | Svc-FinanceAppUATETL ID5 4
	ID14 GENERICSQL164_DatabaseName_RO SQLSERVSQL163 | SQLSERVSQL163 ID5 4
	ID15 GENERICSQL164_DatabaseName_RO SVCFinanceAppSSRSUAT5 | SVCFinanceAppSSRSUAT5 ID5 4
	ID16 GENERICSQL164_DatabaseName_RWE Svc-FinanceAppUATETL | Svc-FinanceAppUATETL ID6 4
	.EXAMPLE
	Create-ADVisio -ObjectName FinanceApp_support -OutputPath 'C:\temp\Groups' -DisplayDuplicates
	Adding the -DisplayDuplicates switch will look for and display any duplicated group memberships.
	Duplicated membership inheritances can cause extreme difficulty when attempting to troubleshoot security issues.
	GENERICSQL91_ALL_DBs_RO is not a member of any other groups
	GENERICSQL51_ALL_DBs_RO is not a member of any other groups
	GENERICSQL136_ALL_DBs_RO is not a member of any other groups
	GENERICSQL169_ALL_DBs_RO is not a member of any other groups
	GENERICSQL94_All_DBs_RO is not a member of any other groups
	GENERICSQL68_ALL_DBs_RO is not a member of any other groups
	GENERICSQL96_ALL_DBs_RO is not a member of any other groups
	GENERICSQL164_ALL_DBs_RO is not a member of any other groups
	GENERICSQL90_ALL_DBs_RO is not a member of any other groups
	GENERICSQL133_ALL_DBs_RO is not a member of any other groups
	BEGIN DUPLICATE GROUP MEMBERSHIPS
	Unique_ID Department E-mail Name Telephone Title Reports_To Master_Shape
	--------- ---------- ------ ---- --------- ----- ---------- ------------
	ID6 GENERICSQL82_LOCALADMIN ID0 0
	ID7 GENERICSQL83_LOCALADMIN ID0 0
	ID8 GENERICSQL84_LOCALADMIN ID0 0
	ID9 GENERICAPP89_LOCALADMIN ID0 0
	ID35 GENERICAPP90_LOCALADMIN ID0 0
	ID38 GENERICSQL84_STAGING_File_AIA_RO ID1 1
	ID41 GENERICSQL84_STAGING_File_EIA_RO ID2 1
	ID58 GENERICAPP178_LOGS$_RO ID13 1
	ID59 GENERICAPP179_LOGS$_RO ID13 1
	ID108 GENERICSQL82_LOCALADMIN ID34 1
	ID109 GENERICSQL83_LOCALADMIN ID34 1
	ID110 GENERICSQL84_LOCALADMIN ID34 1
	ID111 GENERICAPP89_LOCALADMIN ID34 1
	ID112 GENERICAPP90_LOCALADMIN ID34 1
	ID137 GENERICAPP178_LOGS$_RO ID57 2
	ID138 GENERICAPP179_LOGS$_RO ID57 2
	END DUPLICATE GROUP MEMBERSHIPS
	Unique_ID Department E-mail Name Telephone Title Reports_To Master_Shape
	--------- ---------- ------ ---- --------- ----- ---------- ------------
	ID0 FinanceApp_SUPPORT 0
	ID1 Superusers_DEV ID0 0
	ID2 GlobalOversight_DEV ID0 0
	ID3 GlobalOversight_INT ID0 0
	ID4 Superusers_INT ID0 0
	ID5 DATA-FMG-FinanceApp-EXTRACT-RO ID0 0
	ID6 GENERICSQL82_LOCALADMIN ID0 0
	ID7 GENERICSQL83_LOCALADMIN ID0 0
	ID8 GENERICSQL84_LOCALADMIN ID0 0
	#>
	Function Create-ADVisio{
	[CmdletBinding()]
	[OutputType([object])]
	Param(
	# A valid Active Directory User ID or Group Name (samaccountname)
	[Parameter(Mandatory=$true,
	ValueFromPipelineByPropertyName=$true,
	Position=0)]
	[string]
	$ObjectName,
	# UNC or local path to create the CSV output file in
	[Parameter(Mandatory=$true,
	ValueFromPipelineByPropertyName=$true,
	Position=1)]
	[string]
	$OutputPath,
	# Enables looking for and displaying duplicate group memberships. Does not function if -GroupMembers is used
	[switch]
	$DisplayDuplicates,
	# UNC or local path to create a CSV output file of Duplicate memberships
	[Parameter(Mandatory=$false,
	ValueFromPipelineByPropertyName=$true,
	Position=3)]
	[string]
	$DuplicatesFilePath,
	# This checks all groups
	[switch]
	$GroupMembers,
	# Enables looking for Group Membership in Server local security groups and SQL Server securities
	[switch]
	$ServerCheck,
	# Server name prefix (Assumes standard naming conventions for servers)
	[Parameter(Mandatory=$false,
	ValueFromPipelineByPropertyName=$true)]
	$Preefix
	)
	Begin{
	$ID = $ObjectName
	$FileLocation = $OutputPath
	<################################################################
	Check that the ID entered exists in Active Directory
	################################################################>
	Try{
	$ID = Get-ADUser -Identity $ID -ErrorAction Stop
	}Catch{
	Try{
	$ID = Get-ADGroup -Identity $ID -ErrorAction Stop
	}Catch{
	Write-Error "The ID entered can not be found in Active Directory" -Category ObjectNotFound -ErrorAction Stop
	PAUSE
	Break
	}
	}
	# Set ID back to proper name after validation
	$ID = ($ID).samaccountname
	#Set Number to be used
	$Number = 0
	# Create Table
	$tabName = "VisioTest01"
	$table = New-Object system.Data.DataTable “$tabName”
	# All Columns to be created
	$Col1 = New-Object system.Data.DataColumn Unique_ID,([string])
	$Col2 = New-Object system.Data.DataColumn Department,([string])
	$Col3 = New-Object system.Data.DataColumn E-mail,([string])
	$Col4 = New-Object system.Data.DataColumn Name,([string])
	$Col5 = New-Object system.Data.DataColumn Telephone,([string])
	$Col6 = New-Object system.Data.DataColumn Title,([string])
	$Col7 = New-Object system.Data.DataColumn Reports_To,([string])
	$Col8 = New-Object system.Data.DataColumn Master_Shape,([string])
	# Add Columns to Table
	$table.columns.add($Col1)
	$table.columns.add($Col2)
	$table.columns.add($Col3)
	$table.columns.add($Col4)
	$table.columns.add($Col5)
	$table.columns.add($Col6)
	$table.columns.add($Col7)
	$table.columns.add($Col8)
	# Create Master Row Record
	$Row = $table.NewRow()
	# The hyphen in "E-Mail" can't be used in this context
	$email = 'E-Mail'
	# Shape is a field designator for Visio to distinguish between objects and also designates membership relationship in Org chart
	$Shape = 0
	$Row.Unique_ID = "ID$Number"
	$Row.Department = ""
	$Row.$email = ""
	$Row.Name = "$ID"
	$Row.Telephone = ""
	$Row.Title = ""
	$Row.Reports_To = ""
	$Row.Master_Shape = "$Shape"
	# Add the row to the table
	$table.Rows.Add($Row)
	}
	Process{
	<################################################################
	Table has been created and first record populated
	Now Checking Active Directory and populate table based
	on Principal Group Memberships found
	################################################################>
	# Start gathering Group Names at Level 1
	$List1 = Get-ADPrincipalGroupMembership -Identity $ID
	$List2 = ($List1).name
	$SubGroups=@()
	ForEach($L in $List2){
	$Number = $Number + 1
	$Row = $table.NewRow()
	$Row.Unique_ID = "ID$Number"
	$Row.Department = ""
	$Row.$email = ""
	$Row.Name = "$L"
	$Row.Telephone = ""
	$Row.Title = ""
	$Row.Reports_To = "ID0"
	$Row.Master_Shape = "$Shape"
	$table.Rows.Add($Row)
	}
	# Start gathering Group Names at Level 2
	$SubGroups = $Table | Where-Object {$_.Unique_ID -notlike "ID0"}
	$List10=$Empty1=$SubGroups1=$SubGroups2=$SubGroups3=$SubGroups4=@()
	$Shape = $Shape + 1
	ForEach($SubGroup in $SubGroups){
	$SubSubGroups = (Get-ADPrincipalGroupMembership -Identity $SubGroup.name)
	IF($SubSubGroups.count -ge 1){
	ForEach($Sub in $SubSubGroups){
	$List10 += $Sub
	$Number = $Number + 1
	$Row = $table.NewRow()
	$Row.Unique_ID = "ID$Number"
	$Row.Department = ""
	$Row.$email = ""
	$Row.Name = "$($Sub.name)"
	$Row.Telephone = ""
	$Row.Title = ""
	$Row.Reports_To = "$($SubGroup.Unique_ID)"
	$Row.Master_Shape = "$Shape"
	$table.Rows.Add($Row)
	}
	}ELSE{
	Write-host "$($SubGroup.name) is not a member of any other groups" -ForegroundColor Cyan
	$Empty1 += $SubGroup.name
	}
	}
	# Start gathering Group Names at Level 3
	$SubGroups2 = $Table | Where-Object {($_.name -in $list10.name) -and ($_.name -notin $Empty)}
	$List11=$Empty2=@()
	$Shape = $Shape + 1
	ForEach($SubGroup in $SubGroups2){
	$SubSubGroups = (Get-ADPrincipalGroupMembership -Identity $SubGroup.name)
	IF($SubSubGroups.count -ge 1){
	ForEach($Sub in $SubSubGroups){
	$List11 += $Sub
	$Number = $Number + 1
	$Row = $table.NewRow()
	$Row.Unique_ID = "ID$Number"
	$Row.Department = ""
	$Row.$email = ""
	$Row.Name = "$($Sub.name)"
	$Row.Telephone = ""
	$Row.Title = ""
	$Row.Reports_To = "$($SubGroup.Unique_ID)"
	$Row.Master_Shape = "$Shape"
	$table.Rows.Add($Row)
	}
	}ELSE{
	Write-host "$($SubGroup.name) is not a member of any other groups" -ForegroundColor Cyan
	$Empty2 += $SubGroup.name
	}
	}
	# Start gathering Group Names at Level 4
	$SubGroups3 = $Table | Where-Object {($_.name -in $list11.name) -and ($_.name -notin $Empty)}
	$List12=$Empty3=@()
	$Shape = $Shape + 1
	ForEach($SubGroup in $SubGroups3){
	$SubSubGroups = (Get-ADPrincipalGroupMembership -Identity $SubGroup.name)
	IF($SubSubGroups.count -ge 1){
	ForEach($Sub in $SubSubGroups){
	$List12 += $Sub
	$Number = $Number + 1
	$Row = $table.NewRow()
	$Row.Unique_ID = "ID$Number"
	$Row.Department = ""
	$Row.$email = ""
	$Row.Name = "$($Sub.name)"
	$Row.Telephone = ""
	$Row.Title = ""
	$Row.Reports_To = "$($SubGroup.Unique_ID)"
	$Row.Master_Shape = "$Shape"
	$table.Rows.Add($Row)
	}
	}ELSE{
	Write-host "$($SubGroup.name) is not a member of any other groups" -ForegroundColor Cyan
	$Empty3 += $SubGroup.name
	}
	}
	<##################################################
	Checking for Duplicate Group Memberships
	##################################################>
	IF(($DisplayDuplicates -eq $true) -and ($GroupMembers -eq $false)){
	$Single = $table | select name -Unique
	$Duplicates = Compare-Object -ReferenceObject ($Single).name -DifferenceObject ($table).name
	$Duplicates = $Duplicates.inputobject
	IF($Duplicates.count -ge 1){
	$AllDups = $Table | Where-Object {$_.name -in $Duplicates}
	Write-Host "BEGIN DUPLICATE GROUP MEMBERSHIPS" -ForegroundColor Magenta
	$AllDups | FT -AutoSize
	Write-Host "END DUPLICATE GROUP MEMBERSHIPS" -ForegroundColor Magenta
	$tabCsv = $AllDups | export-csv $DuplicatesFilePath\$ID-Duplicates.csv -noType
	}
	}
	<##################################################
	Checking for Group User Members
	##################################################>
	Try{
	$IU = Get-ADUser -Identity $ID -ErrorAction Stop
	}Catch{
	$IU = Get-ADGroup -Identity $ID -ErrorAction Stop
	}
	IF(($IU).ObjectClass -eq "User"){
	$IDisUser = $true
	}ELSEIF(($IU).ObjectClass -ne "User"){
	$IDisUser = $false
	}
	IF($IDisUser -eq $false){
	IF($GroupMembers -eq $true){
	$Groups = $table | Where-Object {($_.name -ne $ID) -and ($_.name -notlike "Domain Users") -and ($_.name -notlike "APP*") -and ($_.name -notlike "DATA-ISS-ONCALL-RO")}
	ForEach($Group in $Groups){
	$Memebers=$Users=@()
	$Memebers = (Get-ADGroupMember -Identity ($Group).name).samaccountname
	Write-Host "Checking for User Members of $($Group.name)" -ForegroundColor Green
	ForEach($User in $Memebers){
	Try{
	$IsUser = $false
	$IsUser = (Get-ADUser -Identity $User -ErrorAction SilentlyContinue) -as [bool]
	IF($IsUser -eq $true){
	$Users += $User
	}
	}Catch{
	$IsUser = $false
	}
	}
	ForEach($UserID in $Users){
	$NN = Get-ADUser -Identity $UserID
	$NiceName = $NN.SamAccountName + ' ' + '|' + ' ' + $NN.GivenName + ' ' + $NN.Surname
	$Number = $Number + 1
	$Row = $table.NewRow()
	$Row.Unique_ID = "ID$Number"
	$Row.Department = "$($Group.Name)"
	$Row.$email = ""
	$Row.Name = "$NiceName"
	$Row.Telephone = ""
	$Row.Title = ""
	$Row.Reports_To = "$($Group.Unique_ID)"
	$Row.Master_Shape = "4"
	$table.Rows.Add($Row)
	}
	}
	}
	}ELSEIF($IDisUser -eq $true){
	}
	<######################################################
	Checking for local computer group memberships
	based on AD Resource Group Names
	#######################################################>
	IF($ServerCheck -eq $true){
	$TableGroups = $Table
	$GroupNames = $TableGroups | Where-Object {($_.name -like "$Preefix*") -and ($_.name -match "\d_")}
	$ServerList=$ServerGroups=@()
	ForEach($TG in $GroupNames){
	# Separate the Server name from the group name
	$Name = $TG.name.tostring()
	$Server = $name.Split('_')[0]
	$ServerList += $Server
	$Lookup = $TG.Name.ToString()
	Write-Host "Checking $Server for the presence of $Lookup"
	# Look through members of all local server groups for the AD Group
	Try{
	$FoundGroup = Get-LocalGroups -ComputerName $Server -ErrorAction SilentlyContinue | Where-Object {$_.'User/Group' -like "*$Name"}
	}Catch{
	$FoundGroup = $null
	}
	IF($FoundGroup -ne $null){
	#$ServerGroups += $FoundGroup
	$Number = $Number + 1
	$Row = $table.NewRow()
	$Row.Unique_ID = "ID$Number"
	$Row.Department = ""
	$Row.$email = ""
	$Row.Name = "$($FoundGroup.ServerName)"
	$Row.Telephone = ""
	$Row.Title = "$($FoundGroup.LocalGroup)"
	$Row.Reports_To = "$($TG.Unique_ID)"
	$Row.Master_Shape = "4"
	$table.Rows.Add($Row)
	}ELSEIF($FoundGroup -eq $null){
	# AD Group was not found in local windows group so look in SQL Server
	<######################################################
	Checking for SQL Server Logins
	based on AD Resource Group Names
	#######################################################>
	Try{
	$SQLGroup = Get-SQLUsers -ComputerName $Server -GroupName $lookup -ErrorAction Stop
	}Catch{
	$SQLGroup = $null
	}
	IF($SQLGroup -ne $null){
	#$ServerGroups += $SQLGroup
	IF (($SQLGroup.count -gt 1)-and ($SQLGroup.database -notcontains "SysAdmin")){
	$SG = $SQLGroup | Where-Object {$_.Database -notlike "N/A"}
	ForEach($Login in $SG){
	$Number = $Number + 1
	$Row = $table.NewRow()
	$Row.Unique_ID = "ID$Number"
	$Row.Department = ""
	$Row.$email = ""
	$Row.Name = "$($Login.Server)"
	$Row.Telephone = ""
	$Row.Title = "$($Login.Database)"
	$Row.Reports_To = "$($TG.Unique_ID)"
	$Row.Master_Shape = "4"
	$table.Rows.Add($Row)
	}
	}ELSEIF(($SQLGroup.count -eq 1) -and ($SQLGroup.database -contains "SysAdmin")){
	ForEach($Login in $SQLGroup){
	$Number = $Number + 1
	$Row = $table.NewRow()
	$Row.Unique_ID = "ID$Number"
	$Row.Department = ""
	$Row.$email = ""
	$Row.Name = "$($Login.Server)"
	$Row.Telephone = ""
	$Row.Title = "$($Login.Database)"
	$Row.Reports_To = "$($TG.Unique_ID)"
	$Row.Master_Shape = "4"
	$table.Rows.Add($Row)
	}
	}ELSE{
	ForEach($Login in $SQLGroup){
	$Number = $Number + 1
	$Row = $table.NewRow()
	$Row.Unique_ID = "ID$Number"
	$Row.Department = ""
	$Row.$email = ""
	$Row.Name = "$($Login.Server)"
	$Row.Telephone = ""
	$Row.Title = "$($Login.Database)"
	$Row.Reports_To = "$($TG.Unique_ID)"
	$Row.Master_Shape = "4"
	$table.Rows.Add($Row)
	}
	}
	}
	}
	}
	<######################################################
	Checking Server Shares
	based on AD Resource Group Names
	#######################################################>
	ForEach($GN in $GroupNames){
	# Separate the Server name from the group name
	$Name = $GN.name.tostring()
	$Server = $name.Split('_')[0]
	$SharesFound = Get-SharePermissions -ComputerName $Server | Where-Object {$_.IdentityReference -like "*\$($GN.name)"}
	Write-output "Checking Shares on $Server"
	ForEach($Share in $SharesFound){
	$Number = $Number + 1
	$Row = $table.NewRow()
	$Row.Unique_ID = "ID$Number"
	$Row.Department = ""
	$Row.$email = ""
	$Row.Name = "\\$($Share.Server)\$($Share.ShareName)"
	$Row.Telephone = ""
	$Row.Title = "$($Share.AccessControlType) - $($Share.FileSystemRights)"
	$Row.Reports_To = "$($GN.Unique_ID)"
	$Row.Master_Shape = "4"
	$table.Rows.Add($Row)
	}
	}
	}
	}
	End{
	# TEST BY DISPLAYING TABLE #
	$table | format-table -AutoSize -Wrap
	Try{
	$TP = Test-path -path $FileLocation\$ID.csv -ErrorAction stop
	IF($TP -eq $true){
	Try{
	Remove-Item -Path $FileLocation\$ID.csv -ErrorAction stop
	}Catch{
	Write-Error "Unable to remove existing File $FileLocation\$ID.csv" -Category PermissionDenied -ErrorAction Stop
	}
	}
	}Catch{
	$TP = Test-Path -path $FileLocation -ErrorAction stop
	IF($TP -ne $true){
	Write-Error "Unable to connect to $FileLocation" -Category ConnectionError -ErrorAction Stop
	}
	}
	<####################################################################
	Create output File in proper CSV format for Visio consumption
	####################################################################>
	$tabCsv = $table | export-csv $FileLocation\$ID.csv -noType
	}
	}

view raw My-ADTools_v2.psm1 hosted with ❤ by GitHub

Editing SSRS Shared Data Sources with PowerShell 3.0

I have recently had a need to edit the server in the connection string of data sources for several dozen SSRS 2008 R2 reports. Easy I know but this was to be a part of a failover scenario. That means I might not be the one doing it.

So the requirements I had, were:

1.       Automate as much as possible to prevent human error.

2.       Make it easy enough for a trained monkey.

Thankfully there were only a few data sources but due to the requirements just documenting the manual process with pretty pictures wasn’t an option.

I want this scripted in PowerShell.

I want to touch as little as possible with the script. (Should it bugger something up.)

I want it to be simple.

I don’t want to have to answer questions for the trained monkeys.

Originally I was going to run a query against the SSRS servers ReportServer DB with invoke-sqlcmd, edit the output and update the line back in the same way. I ran the query in SSMS to make sure I was getting the correct fields and results.

The results were not what I expected and needed to be converted before it was in plain English. Unfortunately I am not knowledgeable enough in SQL to convert it back, even after some lengthy Google searches. Due to this limitation of my knowledge I started looking at using the SSRS Reportserver service.

When connecting to an SSRS Service through a URL it MUST have “.asmx?wsdl” at the end. Also you should know if your SSRS server is running in “native”, “integrated” or “SharePoint” mode. This tells you which of the following service classes the address needs to include.

ReportService2010

http://msdn.microsoft.com/en-us/library/reportservice2010%28v=sql.105%29.aspx

ReportService2006

http://msdn.microsoft.com/en-us/library/reportservice2006%28v=sql.105%29.aspx

ReportExecution2005

http://msdn.microsoft.com/en-us/library/reportexecution2005%28v=sql.105%29.aspx

ReportService2005

http://msdn.microsoft.com/en-us/library/reportservice2005%28v=sql.105%29.aspx

ReportServiceAuthentication

http://msdn.microsoft.com/en-us/library/reportserviceauthentication%28v=sql.105%29.aspx

So the following code will connect to your MSSQL SSRS 2008 R2 server, look for all shared data sources in the specified “folder”. If the connection string matches the $OldSource variable value it is replaced with the $NewSource variable value and updated. I did many on-line searches so I wouldn’t have to reinvent the wheel but all of them were long, complex and difficult to follow. I think the method I came up with is much easier.

	$OldSource = 'MySQLServer-01'
	$NewSource = 'MySQLServer-02'
	$URL = 'http://ReportServerName/ReportServer/ReportService2010.asmx?wsdl'
	$URI = New-Object System.Uri($URL)
	$SSRS = New-WebServiceProxy -Uri $URI -UseDefaultCredential
	$DataSources = ($SSRS.ListChildren("/data sources", $true)).path
	ForEach($Source in $DataSources){
	$DS = $SSRS.GetDataSourceContents($Source)
	IF($DS.ConnectString -like "*$OldSource*"){
	$String = $DS
	$String.ConnectString = $String.ConnectString.Replace($OldSource,$NewSource)
	$SSRS.SetDataSourceContents($Source,$string)
	}
	}

view raw Update-SSRS_Datasources hosted with ❤ by GitHub

Getting Powershell commands, functions, modules, etc without installing anything.

Dave:    Hey Buddy, you should look at this cool script that I found.

Buddy:  Really, what does it do?

Dave:    It’s so cool. It will connect to any SQL server and report back information about each database that is hosted on it.

Buddy:  That’s cool. Did you tell the SQL admin about it?

Dave:    Yup, he said it was easier than the expensive product we just spent 50k on. Plus he didn’t need to do anything extra.

Buddy:  I just tried it but it keeps giving me errors.

Dave:    Well do you have SQL installed on your workstation? Greg and I do.

Buddy:  No, where can I install it from? Do I need a license? How much space does it need?

Dave:    Here try running Import-RemoteCommands.ps1 –Computername someSQLservername instead then try it again.

Buddy:  Awesome it works now, what did that do?

Dave:   The Import-RemoteCommands.ps1 script temporarily imports all commands, functions, modules, etc. from a remote computer to your local session so you can run almost any PowerShell script without having to install things locally to a system.

Buddy:  But what if I want to run something like that on a server? Will I need to submit a change so the boss doesn’t have an aneurism?

Dave:    You shouldn’t because as soon as you close the PowerShell window all those commands go away! You can even use it for a list of computers, so if you want SQL plugins, the AD module and the SCOM module, just list all those server names separated by a coma.

Buddy:  I just opened a new console window but Import-RemoteCommands is giving me an error now.

Dave:    For it to work you need two things, 1. PowerShell remoting needs to be enabled on the computers you list. 2. You need local admin or PowerShell remoting access on those computers.

 OK, so this didn't happen quite the way I portrayed it but it's pretty close.

	#* FileName: Import-RemoteCommands.ps1
	#*=============================================
	#* Script Name: Import-RemoteCommands
	#* Created: 07/10/2014-published
	#* Author: David S. Elias
	#* Email: daveselias@gmail.com
	#* Requirements: PowerShell v2.0+
	#* Keywords: Function, module, command, cmdlet, import, remoting
	#*=============================================
	#*=============================================
	#* REVISION HISTORY
	#*=============================================
	#* Date: 07/15/2014
	#* Time: 1:50PM
	#* Issue: Get-PSSnapin, Import-Module and Import-PSSession
	#* always cause warnings or errors
	#* Solution: Changed ErrorActions from Stop to
	#* SilentlyContinue
	#*=============================================
	<#
	.Synopsis
	Imports all Commands, functions, modules and snapins not already found in the active console session
	.DESCRIPTION
	Imports all Commands, functions, modules and snapins not already found in the active console session
	from the specified computer(s).
	.EXAMPLE
	Import-RemoteCommands.ps1 -ComputerName DC1
	Attempting Remote session connection to DC1 using New-PSSession
	Attempting Get-Pssnapin on DC1
	Attempting Import-Module on DC1
	Attempting Import-PSSession from DC1
	###################################################
	Congratulations, you have imported 907 new commands
	###################################################
	.EXAMPLE
	Import-RemoteCommands.ps1 -ComputerName DC1, DC2
	Attempting Remote session connection to DC1 using New-PSSession
	Attempting Get-Pssnapin on DC1
	Attempting Import-Module on DC1
	Attempting Import-PSSession from DC1
	Attempting Remote session connection to DC2 using New-PSSession
	Attempting Get-Pssnapin on DC2
	Attempting Import-Module on DC2
	Attempting Import-PSSession from DC2
	###################################################
	Congratulations, you have imported 1207 new commands
	###################################################
	#>
	Param
	(
	# Server Name(s) to import modules, functions and cmdlets from
	[Parameter(Mandatory=$true,
	ValueFromPipelineByPropertyName=$false,
	Position=0)]
	[array]
	$ComputerName
	)
	Begin{
	[array]$ALL = $ComputerName
	IF($ALL.count -lt 1){
	Write-Error -Message "No Server Names Were Provided" -ErrorAction Stop
	EXIT 1
	}
	$List=@()
	ForEach($item in $ALL){
	Try{
	$Alive = Test-Connection -ComputerName $item -Count 1 -Quiet -ErrorAction Stop
	IF($Alive -eq $true){
	$List += $item
	}
	}Catch{
	Write-Warning -Message "Unable to find $item"
	}
	}
	$PSV = $PSVersionTable.PSVersion.major
	IF($PSV -eq 2){
	$LocalCommands = Get-Command
	}ELSEIF($PSV -ge 3){
	$LocalCommands = Get-Command -All
	}
	}
	Process{
	ForEach($Server in $List){
	Try{
	Write-Host "Attempting Remote session connection to $Server using New-PSSession" -ForegroundColor Green
	$Active = New-PSSession -ComputerName $Server -ErrorAction Stop
	Try{
	Write-Host "Attempting Get-Pssnapin on $Server" -ForegroundColor Green
	Invoke-Command -Session $Active { Get-PSSnapin -Registered | ForEach-Object { Add-PSSnapin -Name $_.name } } -ErrorAction SilentlyContinue
	Try{
	Write-Host "Attempting Import-Module on $Server" -ForegroundColor Green
	Invoke-Command -Session $Active { Get-Module -ListAvailable | ForEach-Object { Import-Module -Name $_.Name } } -ErrorAction SilentlyContinue
	Try{
	Write-Host "Attempting Import-PSSession from $Server" -ForegroundColor Green
	Import-PSSession -Session $Active -ErrorAction SilentlyContinue
	}Catch{
	Write-Warning -Message "Unable to execute Import-PSSession on $Server"
	}
	}Catch{
	Write-Warning -Message "Unable to execute Import-Module on $Server"
	}
	}Catch{
	Write-Warning -Message "Unable to execute Get-Pssnapin on $Server"
	}
	}Catch{
	Write-Error -Message "Unable to connect to remote session on $Server and import requested functions, cmdlets, modules" -ErrorAction Stop
	}
	}
	}
	End{
	########################### Compare commands to verify if any new commands were imported ###########################
	IF($PSV -eq 2){
	$AfterCommands = Get-Command
	$NewCommands = Compare-Object -ReferenceObject $LocalCommands -DifferenceObject $AfterCommands
	}ELSEIF($PSV -ge 3){
	$AfterCommands = Get-Command -All
	$NewCommands = Compare-Object -ReferenceObject $LocalCommands.Name -DifferenceObject $AfterCommands.Name
	}
	IF($NewCommands.count -lt 1){
	Write-Error -Message "No New functions, cmdlets or modules were imported"
	}ELSEIF($NewCommands.count -gt 1){
	Clear-Host
	Write-Host "###################################################" -ForegroundColor Cyan
	Write-Host " " -ForegroundColor Cyan
	Write-Host "Congratulations, you have imported $($NewCommands.count) new commands" -ForegroundColor Green
	Write-Host " " -ForegroundColor Cyan
	Write-Host "###################################################" -ForegroundColor Cyan
	}
	}

view raw Import-RemoteCommands.ps1 hosted with ❤ by GitHub

Rampant RDP sessions

I've been seeing more and more instances of developers and administrators just disconnecting from RDP sessions on our servers instead of logging off. One I just find this irritating and two It leaves possible applications open on the servers and using resources. So to combat this issue I finally put the following scripts together. 

The first looks for all RDP sessions that have been idle for more than 59 minutes on a filtered list of servers. It then compiles a list of all sessions with the same userID. It looks up the e-mail address of each userID in Active Directory and puts together a report of that list and e-mails it to the individual. Once all individual reports have been sent it sends a master list to a specified address (me). The function that gathers the RDP session info was written by Jaap Brasser http://www.jaapbrasser.com, I was half way through writing my own when I stumbled upon it but why would I want to reinvent the wheel?

	Function Get-LoggedOnUser {
	<#
	.Synopsis
	Queries a computer to check for interactive sessions
	.DESCRIPTION
	This script takes the output from the quser program and parses this to PowerShell objects
	.NOTES
	Name: Get-LoggedOnUser
	Author: Jaap Brasser
	Version: 1.1
	DateUpdated: 2013-06-26
	.LINK
	http://www.jaapbrasser.com
	.PARAMETER ComputerName
	The string or array of string for which a query will be executed
	.EXAMPLE
	.\Get-LoggedOnUser.ps1 -ComputerName server01,server02
	Description:
	Will display the session information on server01 and server02
	.EXAMPLE
	'server01','server02' | .\Get-LoggedOnUser.ps1
	Description:
	Will display the session information on server01 and server02
	#>
	param(
	[CmdletBinding()]
	[Parameter(ValueFromPipeline=$true,
	ValueFromPipelineByPropertyName=$true)]
	[string[]]$ComputerName = 'localhost'
	)
	process {
	foreach ($Computer in $ComputerName) {
	quser /server:$Computer | Select-Object -Skip 1 | ForEach-Object {
	$CurrentLine = $_.Trim() -Replace '\s+',' ' -Split '\s'
	$HashProps = @{
	UserName = $CurrentLine[0]
	ComputerName = $Computer
	}
	# If session is disconnected different fields will be selected
	if ($CurrentLine[2] -eq 'Disc') {
	$HashProps.SessionName = $null
	$HashProps.Id = $CurrentLine[1]
	$HashProps.State = $CurrentLine[2]
	$HashProps.IdleTime = $CurrentLine[3]
	$HashProps.LogonTime = $CurrentLine[4..6] -join ' '
	} else {
	$HashProps.SessionName = $CurrentLine[1]
	$HashProps.Id = $CurrentLine[2]
	$HashProps.State = $CurrentLine[3]
	$HashProps.IdleTime = $CurrentLine[4]
	$HashProps.LogonTime = $CurrentLine[5..7] -join ' '
	}
	New-Object -TypeName PSCustomObject -Property $HashProps |
	Select-Object -Property UserName,ComputerName,SessionName,Id,State,IdleTime,LogonTime
	}
	}
	}
	}
	#get list of all servers to check sessions on from Active Directory
	$AllServers = (Get-ADComputer -filter {name -like "*servernameconvention*"}).name
	#Check all sessions on Servers one by one
	$AllSessions=$List=$MasterList=@()
	ForEach($ComputerName in $AllServers){
	$AllSessions += (Get-LoggedOnUser -ComputerName $ComputerName -ErrorAction SilentlyContinue)
	}
	#Filter results down to Sessions older than 1 Hour
	$AllSessions = $AllSessions | Where-Object {($_.IdleTime -like "*:*") -and ($_.IdleTime -gt "00:59")}
	#Find User information from Active Directory
	$Users = ($AllSessions | select Username -Unique)
	ForEach($U in ($Users).username){
	$List += (Get-ADUser $U -Properties * | select Samaccountname, Name, mail, company)
	}
	#If username designated as a Domain Admin account Drop the "-DA" and locate the e-mail address of the account owner
	ForEach($u in $list){
	IF(($u.mail -eq $null) -and ($u.samaccountname -like "*-DA")){
	$SAM = $u.Samaccountname.substring(0,6)
	$U.mail = ((Get-ADUser $SAM -Properties mail).mail)
	}
	$MasterList += $U
	}
	#Add e-mail addresses from contacts (typically vendor accounts i.e. DELL, HP, etc.)
	$Contacts = Get-ADObject -Filter {Objectclass -like "contact"} -Properties *
	ForEach($U in $MasterList){
	IF($U.mail -eq $null){
	$Name = $U.name.split(',')[0]
	$Mail = $Contacts | Where-Object {($_.name -like "*$Name*") -and ($_.mail -like "*$($u.company)*")}
	$U.mail = $Mail.mail
	}
	}
	# Setup email parameters
	$today = Get-Date
	$priority = "Normal"
	$smtpServer = "mail.EmailServer.com"
	$emailFrom = "Notifications@EmailServer.com"
	#Send Individual Reports
	ForEach($u in $MasterList){
	$subject = "Remote Server Sessions Report for $($u.samaccountname) - " + $today
	$Body = '<font face="Arial">'
	$Body += "<H4> Your UserID $($u.samaccountname), was found to have sessions exceeding 1 hour of idle time on the following servers. Please connect back in and log off properly.</H4><p>`n`n"
	$Body += "`n"
	$Body += '<font color="FF0000">'
	$Body += "<H5>This is an automated e-mail, please do not reply</H5><p>"
	$Body += "</font>"
	$Body += "`n"
	$Body += ($AllSessions | Where-Object {($_.username -like ($u).samaccountname)} | ConvertTo-html)
	$Body += "`n"
	IF($u.mail.count -gt 0){
	$Body += "</font>"
	Write-Host "$($U.name), $($u.mail)"
	$emailTo = "$($u.mail)"
	Send-MailMessage -To $emailTo -Subject $subject -Body $Body -BodyAsHtml -SmtpServer $smtpServer -From $emailFrom -Priority $priority
	}ELSE{
	$emailTo = "Admin@EmailServer.com"
	$Body += "`n `n"
	$Body += $U | Select Samaccountname, Name, Company | ConvertTo-Html
	$Body += "</font>"
	Send-MailMessage -To $emailTo -Subject $subject -Body $Body -BodyAsHtml -SmtpServer $smtpServer -From $emailFrom -Priority $priority
	}
	}
	#Master Report
	$emailTo = "Admin@EmailServer.com"
	$Subject = "Remote Server Sessions Report for - " + $today
	$Body = '<font face="Arial">'
	$Body += "Remote Server Sessions Report for - " + $today
	$Body += "`n"
	$Body += "`n"
	$Body += "The following is a list of sessions that have exceeded 1 hour"
	$Body += "`n"
	$Body += $AllSessions | Sort-Object Username | ConvertTo-Html
	$Body += "</font>"
	Send-MailMessage -To $emailTo -Subject $subject -Body $Body -BodyAsHtml -SmtpServer $smtpServer -From $emailFrom -Priority $priority

view raw Send-RDPReport.ps1 hosted with ❤ by GitHub

The Second one I use to kill RDP sessions remotely quickly and easily. It uses Jaap's "Get-LoggedonUser" function as well but I've omitted it from the script box to save space.

	Function Kill-Sessions{
	Param(
	[Array]$Computer
	)
	$AllSessions=@()
	[int]$Number = 0
	ForEach($ComputerName in $Computer){
	$SystemSessions = (Get-LoggedOnUser -ComputerName $ComputerName -ErrorAction SilentlyContinue)
	ForEach($Session in $SystemSessions){
	$Number=($Number+1)
	$Session | Add-Member -MemberType NoteProperty -Name "Number" -Value $Number
	$AllSessions += $Session
	}
	}
	$AllSessions = $AllSessions | Where-Object {($_.IdleTime -like "*:*") -and ($_.IdleTime -gt "00:59")}
	$AllSessions | Select Number, `|, ComputerName, UserName, State, IdleTime, SessionName | ft -AutoSize
	$Terminate=@()
	$Terminate = Read-Host "Would you like to end one of the above sessions?"
	IF($Terminate -like "Y*"){
	$End = Read-Host "Enter the Number of the session you would like to end"
	$KillSession = ($Allsessions | ? {$_.number -eq $End})
	$Serv = ($Killsession).ServerName.ToString()
	$ID = ($KillSession).SessionID.tostring()
	Invoke-Expression "& `"LOGOFF`" /SERVER:$Serv $ID /V"
	}
	}
	$AllServers = (Get-ADComputer -filter {name -like "*Servername*"}).name
	Kill-Sessions -Computer $AllServers

view raw Kill-Sessions.ps1 hosted with ❤ by GitHub

My office is located in EST and we have a data center onsite but we also have another data center in CST. I don't know why but every so often we get some developers that don't understand that. So I have to sometimes find out why their code is an hour wrong or show them that they need to make their code time insensitive. I use this to do it.

	Function Get-TimeZone {
	Param(
	[Parameter(Mandatory=$True,Position=1)]
	[Array]$ComputerName
	)
	$AllTime=@()
	ForEach ($Computer in $ComputerName ) {
	IF((gwmi -class Win32_SystemTimeZone -ComputerName $Computer -ErrorAction SilentlyContinue) -gt $null){
	$Time = (Get-Time $Computer)
	$Zone1 = (gwmi -class Win32_SystemTimeZone -ComputerName $Computer).setting
	$Zone = $Zone1.split('=')[-1]
	$obj=New-Object PSObject
	$obj | Add-Member -MemberType NoteProperty -Name "ServerName" -Value $Time.ServerName
	$obj | Add-Member -MemberType NoteProperty -Name "DateTime" -Value $Time.DateTime
	$obj | Add-Member -MemberType NoteProperty -Name "TimeZone" -Value $Zone
	$AllTime += $obj
	}ELSE{
	Write-Output "Your current Credentials do not have access to this system ($Computer)"
	}
	}
	IF($AllTime.count -gt 0){
	Write-Output $AllTime
	}
	}

view raw Get-TimeZones.ps1 hosted with ❤ by GitHub

I decided to give up on the code highlighter and throw some business the way of https://gist.github.com

Sorry for the sloppy  script window here, still trying to figure out how to get it to show all neat and tidy on Blogger.

This Function I have found supper useful since at work to save drive letters many of our servers use mount points but we still haven't found a friendly way to check on the capacity and storage usage of them. This in combination of some other functions have become a staple of mine when checking system health and planning for additional storage needs.

 

 

 

1. Function get-mountpoints {  
2. Param(  
3.     [Parameter(Mandatory=$True,Position=1)]  
4.     [Array]$PC  
5. )  
6.     $volumes=@()  
7.     $TotalGB = @{Name="Capacity(GB)";expression={[math]::round(($_.Capacity/ 1073741824),2)}}  
8.     $FreeGB = @{Name="FreeSpace(GB)";expression={[math]::round(($_.FreeSpace / 1073741824),2)}}  
9.     $FreePerc = @{Name="Free(%)";expression={[math]::round(((($_.FreeSpace / 1073741824)/($_.Capacity / 1073741824)) * 100),0)}}  
10.     $volumes = Get-WmiObject -computer $PC win32_volume | Where-object {$_.name -notlike '\\?\*'}  
11.     $volumes | Select SystemName, Label, Name, $TotalGB, $FreeGB, $FreePerc | Sort-Object name | Write-Output  
12. }