The adventures continue

There has been a lull in posts lately since I’d finished up the scripts that run my inventory system and my work had shifted into a direction of application testing and the ongoing integration work from our company buyout. But then, about three months ago I got one of those emails that you just stare at for a while trying to digest… the CIO emailing you and the VP of Enterprise Architecture simply saying “Can you two discuss the new dev ops role.” … I’m gunna leave out all the sappy stuff about how much this means to me and skip to the important stuff. Adventures in Desktop Engineering is now rebranded as Adventures in DevOps and I’m going to have lots to write about again!

Edit:  Ok, one sappy note.  That email from the CIO?  It showed up in my inbox just a few days before my wife and I welcomed our third child into the world.  I think Jesus was trying to show off for me that week.

Posted in Announcements | 2 Comments

PowerShell Function Run-CommandLine

So in this server build script that I’m working on there are several times when external executables are called, such as when I use the operator to call ICACLS for setting share permissions (& ICACLS.exe $folderName /GRANT $ADObject`:(OI)(CI)$ICACLSpermission). Sometimes calls can get messy with all the special characters involved, so I wrote a function that will parse a command line string and run it with Invoke-Expression.

function Run-Commandline {
    <#
    .SYNOPSIS
        Runs a commandline executable with unlimited arguments.
 
    .DESCRIPTION
        Uses space delimination to break the commandline up, then rejoin it back into an Invoke-Expression.  Special chatacters must be escaped! `
 
    .PARAMETER Commandline
        The command as you would run it from a command prompt.
 
    .EXAMPLE
        Run-Commandline -Commandline "C:\Windows\System32\netsh.exe advfirewall firewall add rule name=`"JON Monitoring`" protocol=TCP dir=in localport=16163,7080 action=allow"
    #>

    PARAM([string]$CommandLine)    
    $cmd,$args = $CommandLine -split ' '
    $args = $args | foreach {"'$_'"}
    $args = $args -join ' '
    $cmdLine = $cmd + ' ' + $args
    Invoke-Expression $cmdLine
}
Posted in Programming | Tagged , , | Leave a comment

PowerShell Function Set-PageFile

Here is a function for working with the Windows page file. You can use it to change the path (it will delete the old one and make a new one for you) or modify initial and maximum size.

function Set-PageFile {
    <#
    .SYNOPSIS
        Sets Page File to custom size
 
    .DESCRIPTION
        Disables automatic management of the pagefile, then applies the given values for path and page file size.
        Defaults to C:\pagefile.sys with a 4 gig pagefile.
 
    .PARAMETER Path
        The page file's fully qualified file name (such as C:\pagefile.sys)
 
    .PARAMETER InitialSize
        The page file's initial size [MB]
 
    .PARAMETER MaximumSize
        The page file's maximum size [MB]
 
    .EXAMPLE
        C:\PS> Set-PageFile "C:\pagefile.sys" 4096 6144
    #>
    
    PARAM(
        [string]$Path = "C:\pagefile.sys",
        [int]$InitialSize = 4096,
        [int]$MaximumSize = 4096
    )
     
    $ComputerSystem = $null
    $CurrentPageFile = $null
    $modify = $false
 
    # Disables automatically managed page file setting first
    $ComputerSystem = Get-WmiObject -Class Win32_ComputerSystem -EnableAllPrivileges
    if ($ComputerSystem.AutomaticManagedPagefile) {
        $ComputerSystem.AutomaticManagedPagefile = $false
        $ComputerSystem.Put()
    }
 
    $CurrentPageFile = Get-WmiObject -Class Win32_PageFileSetting
    if ($CurrentPageFile.Name -eq $Path) {
        # Keeps the existing page file
        if ($CurrentPageFile.InitialSize -ne $InitialSize) {
            $CurrentPageFile.InitialSize = $InitialSize
            $modify = $true
        }
        if ($CurrentPageFile.MaximumSize -ne $MaximumSize) {
            $CurrentPageFile.MaximumSize = $MaximumSize
            $modify = $true
        }
        if ($modify) { $CurrentPageFile.Put() }
    }
    else {
        # Creates a new page file
        $CurrentPageFile.Delete()
        Set-WmiInstance -Class Win32_PageFileSetting -Arguments @{Name=$Path; InitialSize = $InitialSize; MaximumSize = $MaximumSize}
    }
}
Posted in Enterprise Management, Programming | Tagged , , , , | Leave a comment

PowerShell command line menu

I’ve been working on a server build script to assist our server team in automating their server build outs. The objective was to select which environment the server is being built in (Alpha, Dev, etc.) then the type of server to build (Database, Web, etc.) and from those two choices go about setting proper permissions and server functionality. Normally when I build tools for other teams, I’ll use Sapien’s PowerShell Studio to build WinForm apps, but this time the server team wanted to be able to crack open the file and modify it when needed, so I couldn’t hand over a compiled exe file this time. So, since I couldn’t do a GUI, I had to come up with a way of doing a “drop down” menu at the command line and this is what I came up with.

function menu1 {
Clear-Host
Write-Host "=============================================================
1) Alpha
2) AlphaX
3) Dev
4) DevInt
============================================================="
}

function menu2 {
Clear-Host
Write-Host "=============================================================
1) Web
2) Database
3) SSIS
4) ServiceMart
5) Microstrategy
============================================================="
}


$environment = ""
$serverType = ""

$stoploop = $false
$retryCount = 0
menu1
#do loop to validate menu input
do {
    $input1 = Read-Host "Please select a environment type"
    if (($input1 -ge 1) -and ($input1 -le 4)) {$stoploop = $true}
    else {
        Write-Host "ERROR! Please select 1-4"
        $retryCount = $retryCount + 1
        if ($retryCount -ge 3) {
            Write-Host "Seriously, how hard is it to press 1-4?"
            $stoploop = $true
        }
    }
} while ($stoploop -eq $false)

switch ($input1) 
{
    1 { $script:environment = "Alpha" }
    2 { $script:environment = "AlphaX" }
    3 { $script:environment = "Dev" }
    4 { $script:environment = "DevInt" }
}

$stoploop = $false
$retryCount = 0
menu2
#do loop to validate menu input
do {
    $input2 = Read-Host "Please select a server type"
    if (($input2 -ge 1) -and ($input2 -le 5)) {$stoploop = $true}
    else {
        Write-Host "ERROR! Please select 1-5"
        $retryCount = $retryCount + 1
        if ($retryCount -ge 3) {
            Write-Host "Seriously, how hard is it to press 1-5?"
            $stoploop = $true
        }
    }
} while ($stoploop -eq $false)

switch ($input2) 
{
    1 { $script:serverType = "Web" }
    2 { $script:serverType = "Database" }
    3 { $script:serverType = "SSIS" }
    4 { $script:serverType = "ServiceMart" }
    5 { $script:serverType = "Microstrategy" }
}

#if either menu items fail, exit
if ($environment -eq "" -or $serverType -eq "") {
    Write-Host "You suck at this."
    Read-Host
    exit
    }
else {Write-Host "You selected $environment and $serverType"}
Posted in Programming | Tagged , , , | Leave a comment

Updating Active Directory using PowerShell and SQL

There are a ton of examples out there on how to use CSV files to import user accounts into AD, but personally I prefer SQL to power the data that I’m pulling into my scripts when I can. Case in point, we just finished the migration to our new hosted mail server and in the process about a third of our users had their default email address changed (all the old users were forced to use an updated naming scheme). As you can image that caused all kinds of issues, but because I already had an inventory of Active Directory and the Exchange Global Address List in SQL, I was able to quickly compare the two, find the discrepancies and put this script together to sync everything back up.

Add-PSSnapin Quest.ActiveRoles.ADManagement
Import-Module SQLPS -DisableNameChecking

$SQLServer = "SERVER"
$SQLDatabase = "Inventory"
# This query displays a list of users whos AD mail attribute do not match their GAL address
# by looking for instances where the first and last names match, but email address does not
$SQLQuery = "SELECT ADUserAccounts.UserID, MailUserObjects.PrimarySMTPAddress
FROM ADUserAccounts INNER JOIN MailUserObjects ON (ADUserAccounts.First_Name = MailUserObjects.FirstName) 
    AND (ADUserAccounts.Last_Name = MailUserObjects.LastName) 
    AND (ADUserAccounts.Mail <> MailUserObjects.PrimarySMTPAddress)
WHERE (ADUserAccounts.First_Name <> '') AND (ADUserAccounts.Last_Name <> '')"

$list = Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $SQLServer -Database $SQLDatabase
ForEach ($row in $list) {
    [string]$UserID = $row.UserID
    [string]$PrimarySMTPAddress = $row.PrimarySMTPAddress
    Set-QADUser $UserID -email $PrimarySMTPAddress
}

As another example, everyone also just had their job title changed, so here is another version of this same logic, powered by a different SQL query to get the needed data and then set it in AD.

Add-PSSnapin Quest.ActiveRoles.ADManagement
Import-Module SQLPS -DisableNameChecking

$SQLServer = "SERVER"
$SQLDatabase = "Inventory"
# This query displays a list of users whos AD job title attribute do not match HR (with some filters)
$SQLQuery = "SELECT ADUserAccounts.UserID, StaffReport.[Job Title] as newTitle, ADUserAccounts.Title as oldTitle
    FROM StaffReport INNER JOIN ADUserAccounts ON StaffReport.[File Number] = ADUserAccounts.Employee_ID
    WHERE (ADUserAccounts.Title <> StaffReport.[Job Title]) AND (StaffReport.[Employee Status Type] <> 'Terminated') 
        AND (ADUserAccounts.Account_Status <> 'Disabled') AND ((ADUserAccounts.Employee_Type = 'Employee') OR 
        (ADUserAccounts.Employee_Type = 'Consultant') OR (ADUserAccounts.Employee_Type = 'Contractor'))"

$list = Invoke-Sqlcmd -Query $SQLQuery -ServerInstance $SQLServer -Database $SQLDatabase
ForEach ($row in $list) {
    [string]$UserID = $row.UserID
    [string]$newTitle = $row.newTitle
    Set-QADUser $UserID -Title $newTitle
}
Posted in Enterprise Management, Programming | Tagged , , , | Leave a comment

UPDATED: Inventory Outlook GAL

Recently my company was acquired and the first thing to be migrated was our email infrastructure. Firstly, this makes all the work I did inventorying the Rackspace system both very useful up front and very useless once we’ve transitioned. The usefulness of course was that we had a full inventory of every mailbox, contact, and distribution list ready to send over for them to use in setting up all the new Exchange objects on their end. But, as with any big transition, mistakes get made, so in order to track them all down I had to pull out the Outlook GAL Inventory script that I used to use before I figured out the Rackspace API. Using this script I was able to get an inventory of the GAL for our new Exchange host and then compare the results in SQL to what I showed from Rackspace.  Its been well over a year since I wrote the original and it needed some updating, so here it is:

#========================================================================
# Created with: SAPIEN Technologies, Inc., PowerShell Studio 2012 v3.0.8
# Created on:   10/11/2012 6:30 PM
# Created by:   richard.hopkins
# Filename:     GAL2SQL
#========================================================================

#region Script Initializers
$Outlook = New-Object -Comobject Outlook.Application
$Mapi = $Outlook.GetNamespace("MAPI")
$Gal = $Mapi.GetGlobalAddressList()

$usercollection=@()
$groupcollection=@()
$groupmembercollection=@()
$contactcollection=@()

$Debug = $true
$SQLServer = "SERVER"
If ($Debug) {$SQLDatabase = "Inventory_Dev"}
Else {$SQLDatabase = "Inventory"}

#Create persistant connection to SQL to keep Temp Tables alive
$conn = New-Object Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=$SQLServer;Database=$SQLDatabase;Trusted_Connection=Yes;Integrated Security=SSPI;"
$conn.Open()
$SQLQuery = ""
$SQLCmd = New-Object System.Data.SqlClient.SqlCommand $SQLQuery, $conn
#endregion

#region Create Temporary Tables on SQL Server
$SQLQuery = "CREATE TABLE [#MailGroupMembership](
	[GroupName] [nvarchar](255) NULL,
	[GroupSMTP] [nvarchar](255) NULL,
	[MembersSMTP] [nvarchar](255) NULL)"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null

$SQLQuery = "CREATE TABLE [#MailGroupObjects](
	[Name] [nvarchar](255) NULL,
	[Alias] [nvarchar](255) NULL,
	[PrimarySmtpAddress] [nvarchar](255) NULL,
	[Comments] [nvarchar](255) NULL)"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null

$SQLQuery = "CREATE TABLE [#MailUserObjects](
	[Name] [nvarchar](255) NULL,
	[Alias] [nvarchar](255) NULL,
	[FirstName] [nvarchar](255) NULL,
	[LastName] [nvarchar](255) NULL,
	[PrimarySMTPAddress] [nvarchar](255) NULL,
	[Address] [nvarchar](255) NULL,
	[Manager] [nvarchar](255) NULL,
	[AssistantName] [nvarchar](255) NULL,
	[BusinessTelephoneNumber] [nvarchar](255) NULL,
	[City] [nvarchar](255) NULL,
	[Comments] [nvarchar](255) NULL,
	[CompanyName] [nvarchar](255) NULL,
	[Department] [nvarchar](255) NULL,
	[JobTitle] [nvarchar](255) NULL,
	[MobileTelephoneNumber] [nvarchar](255) NULL,
	[OfficeLocation] [nvarchar](255) NULL,
	[PostalCode] [nvarchar](255) NULL,
	[StateOrProvince] [nvarchar](255) NULL,
	[StreetAddress] [nvarchar](255) NULL,
	[ExtraSMTPAddresses] [nvarchar](max) NULL)"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
#endregion

#region Main Script Body
#setup progress bar
$totalTicks = 0
ForEach ($Entry In $Gal.AddressEntries) {$script:totalTicks++}
$eachTick = 100/$totalTicks
$currentTick = 0

ForEach ($Entry In $Gal.AddressEntries) {
	Write-Progress -Activity "Inventory Global Adress List" -Status "Gathering Information on object $($entry.name)" -PercentComplete $currentTick
	
	#UserObjects
	If ($entry.AddressEntryUserType -eq 0) {
		$userobject = $entry.getExchangeUser() | select Name,Alias,FirstName,LastName,`
			PrimarySMTPAddress, Address,Manager,AssistantName, BusinessTelephoneNumber, City, `
			Comments,CompanyName,Department,Jobtitle, MobileTelephoneNumber,OfficeLocation, `
			Postalcode, StateorProvince, StreetAddress, @{Name="ExtraSMTPAddresses"; Expression = {`
			$entry.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x800F101E") `
			| Where-Object {$_.Contains("@")}}}

        $SQLQuery = "INSERT INTO [#MailUserObjects] VALUES ('$($userobject.Name -replace "'","''")','" + `
            "$($userobject.Alias -replace "'","''")','$($userobject.FirstName -replace "'","''")','" + `
			"$($userobject.LastName -replace "'","''")','$($userobject.PrimarySMTPAddress -replace "'","''")','" + `
			"$($userobject.Address -replace "'","''")','$($userobject.Manager -replace "'","''")','" + `
            "$($userobject.AssistantName -replace "'","''")','$($userobject.BusinessTelephoneNumber)','" + `
			"$($userobject.City -replace "'","''")','$($userobject.Comments -replace "'","''")','" + `
			"$($userobject.CompanyName -replace "'","''")','$($userobject.Department -replace "'","''")','" + `
            "$($userobject.JobTitle -replace "'","''")','$($userobject.MobileTelephoneNumber)','" + `
			"$($userobject.OfficeLocation -replace "'","''")','$($userobject.PostalCode)','" + `
			"$($userobject.StateOrProvince -replace "'","''")','$($userobject.StreetAddress -replace "'","''")','" + `
            "$($userobject.ExtraSMTPAddresses -replace "'","''")')"

		Write-Output $SQLQuery
		$SQLCmd.CommandText = $SQLQuery
		$SQLCmd.ExecuteNonQuery() | Out-Null
	}
     
	#GroupObjects
	ElseIf($entry.AddressEntryUserType -eq 1) {
		$groupobject = $entry.GetExchangeDistributionList() | select Name,Alias,PrimarySmtpAddress,Comments
        $SQLQuery = "INSERT INTO [#MailGroupObjects] VALUES ('$($groupobject.Name -replace "'","''")','" + `
            "$($groupobject.Alias -replace "'","''")','$($groupobject.PrimarySmtpAddress -replace "'","''")','" + `
			"$($groupobject.Comments -replace "'","''")')"
		Write-Output $SQLQuery
		$SQLCmd.CommandText = $SQLQuery
		$SQLCmd.ExecuteNonQuery() | Out-Null

		$groupobject = $entry.GetExchangeDistributionList()
		$members = $groupobject.GetExchangeDistributionListMembers()
	
		$membersobj= '' | Select GroupSMTP,MembersSMTP
		$smtpmembers = ''
	
        ForEach ($member in $members) {
            If ($member.AddressEntryUserType -eq 0) {
                $membersmtp = $member.getExchangeUser() | Select PrimarySmtpAddress
                $SQLQuery = "INSERT INTO [#MailGroupMembership] VALUES ('$($groupobject.Name -replace "'","''")','" + `
                    "$($groupobject.PrimarySMTPAddress -replace "'","''")','$($membersmtp.PrimarySMTPAddress -replace "'","''")')"
                Write-Output $SQLQuery
				$SQLCmd.CommandText = $SQLQuery
				$SQLCmd.ExecuteNonQuery() | Out-Null
            }
            ElseIf ($member.AddressEntryUserType -eq 1) {
                $membersmtp = $member.GetExchangeDistributionList() | select PrimarySmtpAddress
                $SQLQuery = "INSERT INTO [#MailGroupMembership] VALUES ('$($groupobject.Name -replace "'","''")','" + `
                    "$($groupobject.PrimarySMTPAddress -replace "'","''")','$($membersmtp.PrimarySMTPAddress -replace "'","''")')"
                Write-Output $SQLQuery
				$SQLCmd.CommandText = $SQLQuery
				$SQLCmd.ExecuteNonQuery() | Out-Null
            }
            ElseIf ($member.AddressEntryUserType -eq 5)    {
                    $membersmtp = $member.GetExchangeUser() | select PrimarySmtpAddress
                $SQLQuery = "INSERT INTO [#MailGroupMembership] VALUES ('$($groupobject.Name -replace "'","''")','" + `
                    "$($groupobject.PrimarySMTPAddress -replace "'","''")','$($membersmtp.PrimarySMTPAddress -replace "'","''")')"
                Write-Output $SQLQuery
				$SQLCmd.CommandText = $SQLQuery
				$SQLCmd.ExecuteNonQuery() | Out-Null
            }
        }	
	} 
	$currentTick += $eachTick
}
#endregion

#region Drop current tables and replace with temp data
$SQLQuery = "DROP TABLE [MailUserObjects]"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
$SQLQuery = "SELECT * INTO [MailUserObjects] FROM [#MailUserObjects]"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
$SQLQuery = "DROP TABLE [MailGroupObjects]"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
$SQLQuery = "SELECT * INTO [MailGroupObjects] FROM [#MailGroupObjects]"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
$SQLQuery = "DROP TABLE [MailGroupMembership]"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
$SQLQuery = "SELECT * INTO [MailGroupMembership] FROM [#MailGroupMembership]"
$SQLCmd.CommandText = $SQLQuery
$SQLCmd.ExecuteNonQuery() | Out-Null
#endregion

#region Script Cleanup
$SQLCmd.Dispose()
$conn.Close()
$conn.Dispose()
#endregion 

edit: for some reason vs2html bugged out coloring the code, but it’s good.

Posted in Programming | Tagged , , , , , | 2 Comments

File Organizer: My first C# project

FileOrgangizer

So I’ve finished up my first real C# WinForm project. It takes two paths, From and To, then either moves or copies all files from one to the other while organizing all the files into the new path by year and date.

The inspiration for the project came from my desire to take all the pictures that my wife and I have taken over the years and sort them all out by date into a nice folder structure that makes it easier to browse through. And yeah, there are better apps out there for stuff like this, but I’m learning here! 😛

using System;
using System.IO;
using System.Linq;
using System.Windows.Forms;

namespace File_Organizer
{
    public partial class Form1 : Form
    {
        int numFiles;
        string[] files;
        string fromPath = "";
        string toPath = "";

        public Form1()
        {
            InitializeComponent();
        }

        private void buttonFrom_Click(object sender, EventArgs e)
        {
            DialogResult result = folderBrowserDialog1.ShowDialog();
            if (result == DialogResult.OK)
            {
                fromPath = folderBrowserDialog1.SelectedPath.ToString();
                textBoxFrom.Text = fromPath;
                GetNumberOfFiles();
            }
        }

        private void buttonTo_Click(object sender, EventArgs e)
        {
            DialogResult result = folderBrowserDialog1.ShowDialog();
            if (result == DialogResult.OK)
            {
                toPath = folderBrowserDialog1.SelectedPath.ToString();
                textBoxTo.Text = toPath;
            }
        }

        private void textBoxFrom_TextChanged(object sender, EventArgs e)
        {
            fromPath = textBoxFrom.Text;
        }
        
        private void textBoxTo_TextChanged(object sender, EventArgs e)
        {
            toPath = textBoxTo.Text;
        }

        private void textBoxFrom_Leave(object sender, EventArgs e)
        {
            GetNumberOfFiles();
        }

        private void buttonStart_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;
            progressBar1.Value = 0;
            int i = 0;
            if (fromPath == "")
                MessageBox.Show("Please enter a From Path");
            else if (toPath == "")
                MessageBox.Show("Please enter a To Path");
            else if (numFiles == 0)
            {
                MessageBox.Show("No files to process");
                this.Cursor = Cursors.Default;
            }
            else
            {
                foreach (string file in files)
                {
                    FileInfo fileinfo = new FileInfo(file);
                    string newFolderPath = toPath + "\\" + fileinfo.LastWriteTime.Year + "\\" + fileinfo.LastWriteTime.Month;
                    string newFilePath = file.Replace(fromPath, newFolderPath);

                    if (Directory.Exists(newFolderPath) == false)
                    {
                        Directory.CreateDirectory(newFolderPath);
                    }

                    if (!copyCheck.Checked)
                    {
                        try
                        {
                            fileinfo.MoveTo(newFilePath);
                        }
                        catch (Exception)
                        {
                            //MessageBox.Show(ex.Message);
                        }
                    }

                    if (copyCheck.Checked)
                    {
                        try
                        {
                            fileinfo.CopyTo(newFilePath, true);
                        }
                        catch (Exception)
                        {
                            //MessageBox.Show(ex.Message);
                        }
                    }

                    i++;
                    progressBar1.Value = i;
                }

                this.Cursor = Cursors.Default;
            }
        }

        private void GetNumberOfFiles()
        {
            if (Directory.Exists(fromPath))
            {
                try
                {
                    files = Directory.GetFiles(fromPath, "*", SearchOption.AllDirectories);
                    numFiles = files.Count();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            
            if (numFiles == 1) { labelOutput.Text = numFiles + " file found to organize."; }
            else { labelOutput.Text = numFiles + " files found to organize."; }

            if (numFiles > 0) { progressBar1.Maximum = numFiles; }
        }
    }
}
Posted in Programming | Tagged , , , | Leave a comment