Listing Disabled Active Directory Users

I generate this report once a month, just to make sure we keep AD cleaned up. With people coming and going, and with every case treated differently, it's sometimes hard to remember when to delete what. This is just a monthly report to make sure we aren't leaving anything out there.

#---------------BEGIN SCRIPT---------------------

#Import the Active Directory module. Unless you're running this on a domain controller, you'll need RSAT installed.
import-module activedirectory

#Find disabled AD accounts. Here, I've excluded some builtin accounts, and an OU.
#Return only the name
$output = (Search-ADAccount -AccountDisabled -UsersOnly | where {
($_.name -cnotlike "System*") -and `
($_.name -ne "Guest") -and `
($_.name -ne "krbtgt") -and `
($_.name -notlike "FederatedEmail*") -and `
($_.name -notlike "DiscoverySearchMailbox*") -and `
($_.DistinguishedName -notlike "*ObjectsToDelete*")
} | select Name)

#Build the body of the email by taking the output above and converting it to a string
$body = ($output | Out-String)

#Send me the email
Send-MailMessage -To itreporting@contoso.com -Subject "PS Report - Disabled Active Directory Users" -Body $body -From "helpdesk@contoso.com" -SmtpServer "mailserver.contoso.com"

#---------------END SCRIPT---------------------

Remotely Find What User is Logged In - Put This in Your Profile!

It was occurring quite often that I would attempt to shut down or reboot a system only to find that "other users" were logged in. So I found the function below on the internet and adapted it a bit, I guess, because I removed the attribution info. So if this is yours, I'm sorry for not crediting you.

If you find it useful, I would recommend adding it to your profile so it loads the function every time you open Powershell. Do this by pasting it into C:\Users\username\Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1.

Syntax is Get-LoggedOnUser <ComputerName>

#---------------------BEGIN SCRIPT---------------------------

#This loads the function into your session so you can use Get-LoggedOnUser "Servername"

function global:Get-LoggedOnUser {
#Requires -Version 2.0            
[CmdletBinding()]            
 Param             
   (                       
    [Parameter(Mandatory=$true,
               Position=0,                          
               ValueFromPipeline=$true,            
               ValueFromPipelineByPropertyName=$true)]            
    [String[]]$ComputerName
   )#End Param

Begin            
{            
 Write-Host "`n Checking Users . . . "
 $i = 0            
}#Begin          
Process            
{
    $ComputerName | Foreach-object {
    $Computer = $_
    try
        {
            $processinfo = @(Get-WmiObject -class win32_process -ComputerName $Computer -EA "Stop")
                if ($processinfo)
                {    
                    $processinfo | Foreach-Object {$_.GetOwner().User} | 
                    Where-Object {$_ -ne "NETWORK SERVICE" -and $_ -ne "LOCAL SERVICE" -and $_ -ne "SYSTEM"} |
                    Sort-Object -Unique |
                    ForEach-Object { New-Object psobject -Property @{Computer=$Computer;LoggedOn=$_} } | 
                    Select-Object Computer,LoggedOn
                }#If
        }
    catch
        {
            "Cannot find any processes running on $computer" | Out-Host
        }
     }#Forech-object(ComputerName)       
            
}#Process
End
{

}#End

}#Get-LoggedOnUser

#--------------------------END SCRIPT------------------------

Password Expiration Reminder Emails with Powershell

We used to pay for something that did this, but querying active directory and emailing people based on the results seemed so easy to script!

We've been using this for around two months without any issues.

A prerequisite for this is to download and install Quest's Active Directory Powershell Commands Module, which you can get here.

Please see my comments in the code for further information; here's the script:

#--------------------BEGIN SCRIPT----------------------

#Load the Quest snapin
Add-PSSnapin Quest.ActiveRoles.ADManagement

#Main Variables
$Today = Get-Date
$Outfile = "C:\Temp\Outfile.txt"

#Grab User Accounts. You'll want to specify a maximum because the snapin defaults to 1000.
#Also, you can omit OUs like I did for our "Recycle Bin" OU
$Users = get-qaduser -Sizelimit 2000 | where {$_.DN -notlike "*OU=ObjectsToDelete*"}

#Alter the user list so I can work with it. Basically, I'm taking the list of usernames, getting rid of any blanks, and then converting them to strings for later
$Selection = ($Users | select UserPrincipalName)
$Names = ($Selection | where {($_.UserPrincipalName)} | % {(($_.UserPrincipalName).tostring())})

#Go through the user list and Email everyone that has a password expiring in the next 15 days, or has had one
Foreach ($UserPrincipalName in $Names){
$QueryPiece1 = (Get-QADUser $UserPrincipalName)
$EmailAddress = ($QueryPiece1.Email)
$Date1 = ($QueryPiece1.PasswordExpires)
$Difference = (($Date1.subtract($Today)).days)

#This section sends emails IF the password expires in less than 14 days
If (($Difference -le 14) -and ($Difference -gt 0)){
#Writes to report file
$Output = ($EmailAddress + "," + $Date1 + "," + $Difference)
$Output | Add-Content $outfile

#Builds body of the email that is sent to a user
#Put together email body
$Body1 = "Your Windows password is expiring in $Difference days.`r`n"
$Body2 = "The next time you are logged in to a computer, press Ctrl-Alt-Delete and change your password.`r`n"
$Body3 = "PLEASE REPLY TO THIS EMAIL WITH YOUR EXTENSION IF YOU NEED HELP, OR CALLTHE HELPDESK"
$BodyCombined = ($body1 + $body2 + "`r`n" + $body3)

#Actually Sending the emails to users
Send-Mailmessage -from helpdesk@contoso.com -to $EmailAddress -subject "From the Contoso IT Department - Please Read" -smtpserver mailserver.contoso.com -body $BodyCombined
} #End If
} #End Foreach

#Send IT a report on users that were emailed, then delete the temp file we attached
Send-Mailmessage -from helpdesk@contoso.com -to itreporting@comtoso.com -subject "PS Report - Password Reset Notifications" -smtpserver mailserver.contoso.com -body "Emails Sent to (See Attached)" -Attachments $outfile
Remove-Item $OutFile -Force

#--------------------END SCRIPT----------------------

That wasn't that difficult to create, and saved my department some money. I know the text format's a little wonky there because of my nested IF statements, but if you paste the code into a text editor it should wrap properly and be more readable. I wanted to sit and play with it, but I have a family and a job and stuff.

Pulling Information from SQL Server with Powershell, for Documentation Purposes

I will preface this by saying that I'm an accidental DBA, and probably not a very good one. I managed to get transaction log backups working properly, which I posted about last week, but I'm all the time discovering things that I didn't know that I didn't know.

I have a script that I run once a week just to keep current documentation on my SQL servers. To work with a SQL server in Powershell (and my SQL Servers are SQL 2008R2 running on top of Windows 2008R2), you first need to have SQL Server Management Studio installed. This makes the Powershell modules available to you. After that, open a Powershell prompt and load the modules like so:

add-pssnapin SqlServerCmdletSnapin100
add-pssnapin SqlServerProviderSnapin100

Next, set up the database connection:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$SQLConnection = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "MySQLServerName"

Now, you can explore the TONS of information there is by doing this:
$SQLConnection | get-member

There's a lot to probe in this list. Every item with a "Property" membertype yields information. Usually, I like to capture lists like this so I can go through the properties one at a time to find useful information. I would do this like so:
$SQLConnection | Get-Member | where-object {$_.Membertype -like "Property"} | select-object Name | out-file c:\temp\SQL_Properties.txt

Let's break this down a bit:

• $SQLConnection ***Query the information you go from the SQL Server.
• Get-Member ***List all properties and methods available on $SQLConnection.
• where-object {$_.Membertype -like "Property"} ***Only give me results for items that have "Property" as their membertype.
• select-object Name, Membertype ***I only want the name returned. 
• out-file c:\temp\SQL_Properties.txt ***Send the output to this file.

Now, let's look at just one of those properties: Databases

Running the following command will give you a lot of information regarding each database on the SQL Server:

$SQLConnection.databases

There's a lot to digest, especially if you have more than one database on there. Using the technique above, I'll break that down to include the pieces I want, like so:

$SQLConnection.databases | Get-Member | where-object {$_.Membertype -like "Property"} | select-object Name | out-file c:\temp\SQL_Database_Properties.txt

Let's just get a couple of interesting properties:

• Name
• Owner
• RecoveryModel
• Size
• LastBackupDate
• LastLogBackupDate
• SpaceAvailable
• CompatibilityLevel

$SQLConnection.databases | select Name, Owner, RecoveryModel, Size, LastBackupDate, LastLogBackupDate, SpaceAvailable, CompatibilityLevel

Now that you've got the hang of this, you can pick what you need to know and have it mailed to you via the send-mailmessage command or saved to a file with out-file/export-csv. Automate it, and you'll always have up-to-date documentation!