Getting Database counts per Exchange Server via PowerShell

Was recently asked to generate a report of the total count of items per Exchange database per server. This one-liner (broken up for readability), takes care of it.

Get-MailboxServer | Get-MailboxStatistics | `
Sort-Object DatabaseName | `
Select DatabaseName, ItemCount | `
Group-Object -Property DatabaseName | `
Foreach {            
    $items = ($_.Group | Measure-Object -Property ItemCount -sum).Sum            
    "{0}`t{1:N0}" -f $_.Name,$items             
}

This will generate something like the following:

Exchange01-DB01	1,372,127
Exchange01-DB02	1,522,356
Exchange01-DB03	1,406,486
Exchange01-DB04	1,345,962
Exchange01-DB05	1,330,690
Exchange01-DB06	1,392,853
Exchange01-DB07	1,318,130
.....	.....

Enjoy!

Managing Proxy Settings with PowerShell

I find myself changing proxy settings often on my laptop between various environments (Home, Production and Test). Generally this is no big deal. But today, I found myself switching multiple times as I was testing ISA and realized that PowerShell can easily take care of this.

At some point, I will wrap this into a GUI but for now, here is the script.

Note: I am using Jeff Hick's Test-RegistryItem.Enjoy!

function Set-Proxy {          
  [cmdletbinding()]          
  Param (          
      [Parameter(Position=0,Mandatory=$True,          
       HelpMessage="Enter either `"Home`", `"Production`" or `"Test`".")]          
      [ValidateNotNullOrEmpty()]          
      [ValidateSet("Home", "Production", "Test")]          
      [String]$Location          
  )           
            
  $path = "HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings"          
  $url  = "http://autoconf.FatBeard.com/proxy.pac"          
            
  switch ($location)          
  {          
      "Home" {           
          Set-ItemProperty -Path $path -Name ProxyEnable -Value 0          
          if (Test-RegistryItem -Path $path -Property ProxyServer) {          
              Remove-ItemProperty -Path $path -Name ProxyServer          
          }          
          if (Test-RegistryItem -Path $path -Property AutoConfigURL) {          
              Remove-ItemProperty -Path $path -Name AutoConfigURL          
          }          
      }          
      "Production" {          
          Set-ItemProperty -Path $path -Name ProxyEnable -Value 0          
          Set-ItemProperty -Path $path -Name AutoConfigURL -Value $url          
      }          
      "Test" {          
          Set-ItemProperty -Path $path -Name ProxyEnable -Value 1          
          Set-ItemProperty -Path $path -Name ProxyServer -Value "TestProxy-vt01:8080"          
          if (Test-RegistryItem -Path $path -Property AutoConfigURL) {          
              Remove-ItemProperty -Path $path -Name AutoConfigURL          
          }          
      }           
  }          
}

Enjoy!

PowerShell & User Certificates

I was recently asked if I could generate a report of user certificates. I recalled doing this with a .NET class a while back, here is that function:

function Get-Certificate {      
[cmdletBinding()]      
param([Parameter(Position=0,      
Mandatory=$True,      
ValueFromPipeLine=$True)]      
[String]$user)      
    
Begin {       
  Add-PSSnapin `
         -Name Quest.ActiveRoles.ADManagement `
         -ErrorAction SilentlyContinue }      
    
Process {      
  Get-QADUser -Identity $user | `
        Foreach { $_.DirectoryEntry.userCertificate } | `
        Foreach {[System.Security.Cryptography.X509Certificates.X509Certificate2]$_} | `
        Select Issuer, Subject, NotBefore,NotAfter      
}      
}

However, assuming you have the most recent version of the Quest Active Directory cmdlets (1.4.0), this becomes a trivial task.

Get-QADUser -Identity FatBeard | Get-QADCertificate

IssuedBy                 IssuedTo   ValidFrom  ValidTo
--------                 --------   ---------  -------
OSUMC EnterpriseRoot CA  Fat Beard  6/3/2010   6/3/2011
OSUMC EnterpriseRoot CA  Fat Beard  11/9/2009  11/9/2010

Enjoy!

Where's my attribute?

I am often asked why certain Active Directory attributes do not show up when using Get-QADUser (ActiveRoles Management Shell for Active Directory). An example:

(Get-QADUser -Identity FatBeard).employeeID

One might expect this to return the employeeID for Mr. FatBeard.  It doesn't and this is a desired behavior.  In order to maximize performance, the Quest cmdlets return a default list of attributes.  To see non-default attributes you have a few options:

- Use the -IncludeAllProperties parameter.  This will store ALL the attributes in the memory cache.

- Use the -IncludedProperties parameter.  For ad-hoc reporting, when all that is needed are a few attributes, this is the recommended way.
Get-QADUser -Identity FatBeard -IncludedProperties employeeID,logonCount | Select employeeID, logoncount

- Lastly, you can modify the default attributes that are returned.
$attributes = Get-QADPSSnapinSettings -DefaultOutputPropertiesForUserObject

$attributes += 'employeeID'

Set-QADPSSnapinSettings -DefaultOutputPropertiesForUserObject $attributes

(Get-QADUser -Identity FatBeard).employeeID #Success!

Modifying the Snapin settings is only good for the current session. If you want the settings to persist, you will need to add the modification to your profile.

Enjoy!

Project Euler and PowerShell - Problem 42

Here is a PowerShell solution to Project Euler - Problem 42.

# Download the text file and create the array            
$web = New-Object System.Net.WebClient           
$url = "http://projecteuler.net/project/words.txt"           
$words = $web.DownloadString($url).replace("""","").split(",")           
          
# Generate a list of Triangular numbers           
[int[]]$triNums = @()           
for($r=1;$r -lt 500; $r++) {           
   $triNums += $r*($r+1)/2           
}           
          
# Create a hashtable for numeric lookup           
$lookup = @{}           
$i=1           
65..90 | Foreach {$lookup.add([Char]$_,$i++)}           
          
# Get the numeric value of a word           
function Get-WordValue($word) {           
$letters = $word.ToCharArray()           
$sum = 0           
foreach ($letter in $letters) {           
 $sum += $lookup[[char]$letter]           
}           
return $sum           
}           
          
# Count the Triangular words           
$Count = 0           
foreach ($word in $words) {           
if($triNums -contains (Get-WordValue $word)) {           
 #"{0}`t{1}" -f (Get-WordValue $word), $word           
       $Count++           
}            
}           
$count

Finding instances of BitLocker

Was responding to a forum question today about finding what workstations are running BitLocker. Following is a script that does just that:

Found this posting very helpful for the PacketPrivacy.

In this example I am specifically looking for computers running Windows 7.

$w = [wmi]''          
$w.PSBase.Scope.Options.Authentication = 'PacketPrivacy'          
$w.PSBase.Scope.Options.Authentication          
        
Get-QADComputer -OSName 'Windows 7*' | Select-Object Name | `
Foreach {          
  Get-WmiObject -ErrorAction SilentlyContinue `
      -Namespace 'root\cimv2\Security\MicrosoftVolumeEncryption' `
      -Impersonation 'impersonate' `
      -Authentication $w.PSBase.Scope.Options.Authentication `
      -Class win32_EncryptableVolume `
      -ComputerName $_.Name | Select __Server, DriveLetter          
}

Enjoy!

Get-LatticePoints

I am so close to reaching the second level in Project Euler (need 50, I am at 48). Most of the problems that I have solved have been via Python, however, I am occasionally able to use PowerShell. A few of the problems ask to find lattice points within a circle.

What is a lattice point? - Think of a lattice point as an intersection on a grid. So if you had a circle with radius 1, there would be 5 lattice points: (-1,0), (0,1), (0,-1), (1,0) and (0,0).
Following is a PowerShell function that will get the number of lattice points for a given radius:

function Get-LatticePoints ([int]$radius)       
{       
    $count = 0       
    $x = -$radius       
    while ($x -le $radius)       
    {       
        $y = -$radius       
        While ($y -le $radius)        
        {       
            if($x*$x + $y*$y -le $radius*$radius)       
            {       
                $count++       
            }       
            $y++       
        }       
        $x++       
    }       
    return $count       
}       
       
for($i=1;$i -le 100;$i+=1) {       
   "Index: {0}`tLatticePoints: {1}" -f $i, (Get-LatticePoints $i)       
}

I forsee using this function in a couple of the Euler problems.

Enjoy!

Query AD & ND concurrently (Yep, you heard me right)

In our environment, we have Active Directory and Novell Directory Services. While I spend 95% of my time in AD, I do occasionally get asked to grab information from NDS. Instead of falling back on older tools, I thought I would look at querying NDS via PowerShell. After looking at a few .NET classes, I recalled that one of my handouts at a Central Ohio PowerShell Users Group meeting was NetCmdlets. These cmdlets greatly extend the features of Microsoft Windows PowerShell with a broad range of network management and messaging capabilities. They also happen to include Get-LDAP. Using this cmdlet, it is fairly straightforward to query NDS. Following is and example that queries AD and NDS in one line of script (broken up for readability.

Get-QADUser -Enabled -Department *92278*  Sort-Object samaccountname   `
ForEach-Object {  
    Get-LDAP -Server 'Novell-Server' -Search "cn=$($_.samaccountname)"  
}  Select-Object -Unique @{N="FullName";E={$_.FullName[0]}}, resultDN  `
Export-Csv -Path c:\temp\NDS.csv -NoTypeInformation

So here is what happens:

1. We query for enabled users in AD that are in Department 92278.
2. We sort these users by SAMAccountName
3. We iterate over each user calling Get-LDAP with an NDS server and the SAMAccountName as a parameter
4. We then select FullName and the resultDN (there is a bit of magic going on here as we need to assist PowerShell with the formatting: -Unique gets rid of blank lines (don't ask me why they are there). FullName actually returns an array (once again, not sure why), we can easily grab what we want by using by forcing the format @{N="FullName";E={$_.FullName[0]}}
5. Lastly, we kickout the results to a CSV ready for use in Excel

The results look like this...

FullName	resultDN
Alda, Alan	cn=Alda01,ou=IS,ou=OSU,ou=HOSP,ou=CAMPUS,o=OSU_MC
Burghoff, Gary	cn=Burg02,ou=IS,ou=OSU,ou=HOSP,ou=CAMPUS,o=OSU_MC
Farr, Jamie	cn=Farr01,ou=IS,ou=OSU,ou=HOSP,ou=CAMPUS,o=OSU_MC

At some point, I will look at using the System.DirectoryServices Namespace to accomplish this instead of relying on a 3rd party, but for now I can check a few immediate NDS related tasks off my list.

Enjoy!

WPK Snippets

Here is another WPK snippet for you. This one will demonstrate how to use New-Slider and New-Ellipse. Assuming that you the PowerPack Modules installed, you should be able to cut and paste the following:

Import-Module WPK           
New-Window -Name "WPK" -Title "WPK Snippets" -Width 250 -Height 175 -Show {           
   New-Grid -Rows 28,100 -Columns 200* {           
       New-TextBox -Name txtSliderValue -Column 0 -Row 1           
       $fill = [System.Windows.Media.Brushes]::Blue           
 $stroke = [System.Windows.Media.Brushes]::Black           
 New-Ellipse -Name oEllipse -Column 0 -Row 2 -Height 100 -Width 100 `
         -Fill $fill -Stroke $stroke           
       New-Slider  -Name Slider -Column 0 -Row 0 -Minimum 0 -Maximum 200 `
         -IsSnapToTickEnabled -On_ValueChanged {           
           $txtSliderValue = $window | Get-ChildControl txtSliderValue           
           $slider         = $window | Get-ChildControl Slider           
           $oEllipse       = $window | Get-ChildControl oEllipse           
           $oEllipse.width = $slider.value           
       }           
   }           
}

You should see something like the following:


Moving the slider triggers the -On_ValueChanged event.
Moving it around you should see the width of the ellipse change accordingly.


Download the script.

Enjoy!

WPK Snippets

I have been experimenting with the WPK module from the PowerShell PowerPack. This module enables you to easily write user interfaces within PowerShell. Think PrimalForms but with less code and staying within one IDE. The hard part of writing the UI in WPK is knowing all the methods and properties associated with a particular object. I find searching MSDN for the members very helpful. As I use the different objects (controls), I will share my findings. Up first is the New-FolderBrowserDialog. These examples are ready to be cut and pasted into PowerShell ISE.

Import-Module WPK
New-Window -Name "WPK" -Title "WPK Snippets" -Width 400 -Height 100 -Show {
 New-Grid -Rows 28,28 -Columns 75,300* {
     New-TextBlock -Text "Folder Path:" -Row 1 -Column 0 -Margin 3
     New-TextBox -Name txtUserName -Width 300 -Row 1 -Column 1 -Margin 3
     New-Button -Content "Folder" -Name btnFolder -Row 0 -Column 1 -Margin 3 -Width 300 -On_Click {
       $obj = New-FolderBrowserDialog -RootFolder "MyComputer"
       $obj.ShowDialog()       
       $txtUserName = $Window | Get-ChildControl txtUserName
       $txtUserName.Text = $obj.SelectedPath }
 } 
}

You should see something similar to the following:


Clicking on the button brings up the familiar "Browse For Folder" Dialog box.


Select a folder and the path is placed in the appropriate textbox.


Enjoy!

Dynamic Variable Values

Once again, my good friend from Texas asked me for a little PowerShell assistance. Being an old Cold Fusion junkie, he sent me his cryptic CF code:

Disclaimer (Blogger hates Angle Brackets)
cfset variable1="blah"
cfset var2="yada"

This statement would've then returned the value of "#blahyada#"
cfoutput #Evaluate("#variable1#")var2# cfoutput

It is basically a self-renaming variable based on the value of another variable - no idea if PowerShell can do that.

After my brief Cold Fusion viewing induced nausea subsided, I decided to help him. This is the sample I sent him:
$DallasAddress = "123 Cowboy Ave."
$ColumbusAddress = "456 Hayes St."
$ClevelandAddress = "910 Kosar Blvd."

$loc = "Dallas"
# Returns "123 Cowboy Ave."
(Get-Variable ($loc+"Address")).value

He is now happy and has learned a little bit more PowerShell. As for me, he has added yet another 6 bottles of my favorite beverage to the till.
Enjoy!

Query DNS with PowerShell

Following is a script that will enable you to query DNS for duplicate records. Be sure to change the location of the output file!

# Using WMI, retrieve all the duplicate DNS records
$DNS = Get-WmiObject -ComputerName 'DNS-Server' `
   -Namespace 'root\MicrosoftDNS' `
   -Class MicrosoftDNS_AType `
   -Filter "ContainerName='Your Container'" | `
Group-Object OwnerName | Where-Object {$_.Count -gt 1}

# Create our CSV file to hold the data
$file = 'c:\temp\DNS.csv'
New-Item -ItemType file -Path $file -Force 
Add-Content -Path $file -Value "Name,IPAddress"

# Iterate of the DNS items grabbing the name and IPAddress
foreach ($item in $DNS) {
    foreach ($IPAddresses in $item.Group) {
        $value = "{0},{1}" -f $item.name,$IPAddresses.IPAddress
        Add-Content -Path $file -Value $value
    }
}

Results should look something like:

Name	IPAddress
Server1	10.194.111.22
Server2	10.140.111.22
ServerA	10.333.19.121
ServerB	10.333.131.24

Enjoy!

Calling a Web Service with PowerShell

I often need to be notified of a certain condition when a scheduled script executes.
With PowerShell V2, I can take advantage of our internal paging web service by using the New-WebServiceProxy cmdlet to take care of this notification.

Check out the help on this cmdlet to see a full list of capabilities. Help New-WebServiceProxy -Full

Before we dive into the actual call, lets interrogate the web service to see what it can do.
# Create a proxy for the Paging web service
$page = New-WebServiceProxy -Uri 'http://InternalPagingService/pageservice.asmx'

#List the methods
$page Get-Member -MemberType Method
You should see something like this:

The method we are going to use is RequestSinglePage.

Enter the following to see the expected parameters:
($page Get-Member -Name RequestSinglePage).definition

Looking at the end of the definition we see 2 parameters:
- string PagerId
- string NumericOrAlphaMessage

We now have all we need to page from script!
$page.RequestSinglePage('3141','Testing Page Web Service from PowerShell')

If you need confirmation of the Web Service call, you can look at the FunctionStatus property of the executed Web Service.
($page.RequestSinglePage('3141','Test Page from PowerShell')).FunctionStatus

This returns - SUCCESS

Wrapping this web service call around some return code if fairly straight forward and is left as an exercise for the reader.

If you don't have an internal Web Service handy, try out http://www.webservicex.net/WeatherForecast.asmx

Enjoy!