Powershell function to look up an Active Directory user with SamAccountName

This is a short function to create a function called "Show-User".

#
# Show a single user
#
function Show-User
{
    param (
        [Parameter(Mandatory)] $Username
        )

    try{
        $test = Get-ADUser -Identity $Username -Prop CN,description,LastLogon,whenCreated,LastLogontimestamp,LastLogonDate,SamAccountName, userPrincipalName,SmartcardLogonRequired,PasswordNeverExpires, targetAddress, EmailAddress,homeDirectory, CanonicalName, adminDescription, msDS-cloudExtensionAttribute1 -ErrorAction Stop | 
            Select CN, SamAccountName, description, userPrincipalName, SmartcardLogonRequired, PasswordNeverExpires, whenCreated, adminDescription, LastLogonDate, Employee-ID, @{n="lastLogontimestamp";e={[datetime]::FromFileTime($_.lastLogontimestamp)}},  @{n="lastLogon";e={[datetime]::FromFileTime($_.LastLogon)}},AccountExpirationDate, targetAddress, EmailAddress,homeDirectory, CanonicalName
        if($test) {
            Write-Output $test
        }
    } catch {
        Write-Output "User object $Username does not exist in AD"
    }
}

Execute the powershell script to create the command, then you should be able to use it in powershell like this:

C:\windows\system32> Show-User -Username john.doe

Hope you find that useful.

Powershell to copy files from one file server share to another file server share

Instead of using Windows File Explorer to copy large files/folders from one \\fileserver\share to another, it is problematic when you continually get prompted to overwrite files or other errors cause the process to stop until user responds to the prompt.  I found that using powershell works better.

Copy-Item -Path "\\Fileserver01\share$\john.doe" -Destination "\\Fileserver02\share$\" -Recurse -Force -WhatIf

# Remove -WhatIf flag to actually do it.
# -Recurse copies files and subfolders.
# -Force copies/overwrites files even if destination is readonly.

Hope you find that useful.

Moving AD accounts to the different OUs based on security group membership

We've been using powershell to check for "stale" accounts, ie. haven't been logged on for 30+ days, and disabling them.  If the user requests to be enabled and becomes "active" again, another PS moves them back to the OU they are supposed to be in.  However, I recently found out that some users should be placed in different OUs based on work location or status.  Anyway, below is what I created to fix that.

    foreach($usr in $usrlist){
        write-host $usr.'Logon Name'
        Write-Host $usr.'Canonical Name'
        if ( Get-ADGroupMember -Identity "Secgroup1" | Where-Object {$_.SamAccountName -eq $usr.'logon name'} ) 
        {
            $TargetOU = "OU=Secgroup1,OU=ABC,DC=acme,DC=com"
            Get-ADGroupMember -Identity "Secgroup1" | Where-Object {$_.SamAccountName -eq $usr.'logon name'} | Move-ADObject -TargetPath $TargetOU -Verbose
        }
        elseif ( Get-ADGroupMember -Identity "Secgroup2" | Where-Object {$_.SamAccountName -eq $usr.'logon name'} ) 
        {
            $TargetOU = "OU=Secgroup2,OU=ABC,DC=acme,DC=com"
            Get-ADGroupMember -Identity "Secgroup2" | Where-Object {$_.SamAccountName -eq $usr.'logon name'} | Move-ADObject -TargetPath $TargetOU -Verbose
        } else {
            $TargetOU = "OU=Default,OU=ABC,DC=acme,DC=com"
            Get-ADUser $usr.'Logon Name' | Move-ADObject -TargetPath $TargetOU -Verbose
        }
    }

Hope you find that useful.

Powershell Function: Test connectivity to SQL server

This powershell creates a helper function that tests connection to SQL database.  In my situation, found this in order to check if port and protocols needed for SQL connections were being allowed from different vlans.

Usage:
    Test-SQLDatabase -Server SQLServer -Database SomeDB -Username SQLUser -Password password

    Credit to: https://stackoverflow.com/a/38784435 (Rob Holme)

#
# Usage:
#     Test-SQLDatabase -Server SQLServer -Database SomeDB -Username SQLUser -Password password
#
# Credit to: https://stackoverflow.com/a/38784435 (Rob Holme)
#
function Test-SQLDatabase 
{
    param( 
    [Parameter(Position=0, Mandatory=$True, ValueFromPipeline=$True)] [string] $Server,
    [Parameter(Position=1, Mandatory=$True)] [string] $Database,
    [Parameter(Position=2, Mandatory=$True, ParameterSetName="SQLAuth")] [string] $Username,
    [Parameter(Position=3, Mandatory=$True, ParameterSetName="SQLAuth")] [string] $Password,
    [Parameter(Position=2, Mandatory=$True, ParameterSetName="WindowsAuth")] [switch] $UseWindowsAuthentication
    )

    # connect to the database, then immediatly close the connection. If an exception occurrs it indicates the conneciton was not successful. 
    process { 
        $dbConnection = New-Object System.Data.SqlClient.SqlConnection
        if (!$UseWindowsAuthentication) {
            $dbConnection.ConnectionString = "Data Source=$Server; uid=$Username; pwd=$Password; Database=$Database;Integrated Security=False"
            $authentication = "SQL ($Username)"
        }
        else {
            $dbConnection.ConnectionString = "Data Source=$Server; Database=$Database;Integrated Security=True;"
            $authentication = "Windows ($env:USERNAME)"
        }
        try {
            $connectionTime = measure-command {$dbConnection.Open()}
            $Result = @{
                Connection = "Successful"
                ElapsedTime = $connectionTime.TotalSeconds
                Server = $Server
                Database = $Database
                User = $authentication}
        }
        # exceptions will be raised if the database connection failed.
        catch {
                $Result = @{
                Connection = "Failed"
                ElapsedTime = $connectionTime.TotalSeconds
                Server = $Server
                Database = $Database
                User = $authentication}
        }
        Finally{
            # close the database connection
            $dbConnection.Close()
            #return the results as an object
            $outputObject = New-Object -Property $Result -TypeName psobject
            write-output $outputObject 
        }
    }
}

Powershell Function: Show members of active directory group

This powershell creates a function called "Show-ADGroup" with two parameters to display the members of an AD group.

#
# Show-ADGroup - show members of an AD group, exports to out-grid or CSV on desktop.
#
# Parameters: 
#    Gname - name of AD group to show
#    ToFileYN - "Y" will show export list to CSV file; otherwise, display in Out-gridview
#
function Show-ADGroup
{
    param (
        [Parameter(Mandatory)] $Gname,
        [Parameter(Mandatory)] $TofileYN
        )

    # set $DCname to one of your local domain controllers
    $DCname = "your_DC_name"

    # counts number of members
    $cnt = (Get-ADGroup $Gname -Properties *).Member.Count
    Write-Host "# members: $cnt"

    # specify location of export to file; in this case the user's desktop
    $path = "C:\Users\$ENV:USERNAME\Desktop"
    $pathexist = Test-Path -Path $path

    If ($pathexist -eq $false)
        {New-Item -type directory -Path $path}

    $reportdate = Get-Date -Format ssddmmyyyy
    $csvreportfile = $path + "\ADGroupMembers_$reportdate.csv"

    if ($TofileYN -eq "Y") {
        Get-ADGroupMember -Identity "$Gname" -Server $DCname| 
        Select-Object @{Label = "Name";Expression = {$_.Name}}, 
                      @{Label = "SamAcctName";Expression = {$_.SamAccountName}},
                      @{Label = "distinguishedName";Expression = {$_.distinguishedName}} |
        Export-Csv -Path $csvreportfile -NoTypeInformation
    } else {
        Get-ADGroupMember -Identity "$Gname" -Server $DCname| 
        Select-Object @{Label = "Name";Expression = {$_.Name}}, 
                      @{Label = "SamAcctName";Expression = {$_.SamAccountName}},
                      @{Label = "distinguishedName";Expression = {$_.distinguishedName}} |
        Out-GridView
    }
}

MS Teams was working, but now doesn't allow logon

 Occasionally, MS Teams prompts me to logon again and sometimes gets into a loop where it says it logon didn't work and restart to try again, repeatedly...

1. What I do next is right-click MS Teams in systray, then select Quit.

 

2. Open file explorer and go to folder "%appdata%\Microsoft\Teams" and delete the contents.

3. Then start up MS Teams again.  

Otherwise, see more MS Teams troubleshooting info here:
https://docs.microsoft.com/en-us/microsoftteams/troubleshoot/teams-sign-in/resolve-sign-in-errors

SQL Server Agent (Agent XPs disabled)

How to enable Agent XPs when SQL Agent Service shows "(Agent XPs disabled)".

From SQL Server Management Studio, open query window and use sp_configure commands below:

SP_CONFIGURE 'SHOW ADVANCE',1

GO

RECONFIGURE WITH OVERRIDE

GO

SP_CONFIGURE 'AGENT XPs',1

GO

RECONFIGURE WITH OVERRIDE

GO

Start or restart SQL Agent in SSMS.

Reference:

https://blog.sqlauthority.com/2016/06/13/sql-server-fix-agent-xps-component-turned-off-part-security-configuration-server/

https://blog.sqlauthority.com/2016/02/29/sql-server-unable-to-start-sql-server-agent-failed-to-initialize-sql-agent-log/