SCCM Queries for Software Collections - Copy and Paste These

Queries for collections of machines with different versions of Adobe Acrobat, Symantec Anti-Virus or Symantec Endpoint Protection, or Microsoft Office.  

As of today (6/30/2010), Adobe doesn't support or update the full version of Acrobat or Reader less then version 8.

Adobe Acrobat 6 or less:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Adobe Acrobat%" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version < "7%"

Adobe Acrobat 7:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Adobe Acrobat%" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version >= "7%" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version < "8%"

Adobe Acrobat 8:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Adobe Acrobat%" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version >= "8%" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version < "9%"

Adobe Acrobat 9 (10 doesn't exist yet, so we can't put a <"10%":

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Adobe Acrobat%" and SMS_G_System_ADD_REMOVE_PROGRAMS.Version >= "9%"

Symantec Anti-Virus or Symantec Endpoint Protection:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "symantec antivirus" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "symantec endpoint protection"

Microsoft Office 2003:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Professional Edition 2003" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Basic Edition 2003" or SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Standard Edition 2003"

Microsoft Office 2007:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft Office Enterprise 2007"

Microsoft Office 2010 (I had problems getting it to take all of this as 1 rule, so there are 2.  1 for x86 and 1 for x64)

Rule 1: Office 2010 x86

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Microsoft Office Professional Plus 2010"

Rule 2: Office 2010 x64

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "Microsoft Office Professional Plus 2010"

My Top Exchange 2007 Links

These are pages that I have found very useful and I believe that every Exchange admin should have bookmarked.

• Microsoft Technet: http://technet.microsoft.com/en-us/library/aa996058(EXCHG.80).aspx
• For certificates, I highly recommend DigiCert and their UC certs: https://www.digicert.com/easy-csr/exchange2007.htm, and http://www.digicert.com/ssl-certificate.htm
• The Microsoft Exchange Team Blog: http://msexchangeteam.com/
• MSExchange.org: http://blogs.msexchange.org/
• Computer Performance Exchange 2007: http://www.computerperformance.co.uk/exchange2007
• Exchange Non-Delivery Reports: http://www.computerperformance.co.uk/exchange2007/exchange2007_ndr.htm
• Performance Troubleshooting using the PAL tool: http://blogs.technet.com/b/mikelag/archive/2008/08/20/performance-troubleshooting-using-the-pal-tool.aspx
• Replacing the Exchange 2007 Self-Signed Certificate: http://www.exchangeinbox.com/article.aspx?i=127

Exchange 2007 Management Shell Commands

I'll try to add to these over time as I remember and as I learn more.  These are some of the Exchange 2007 Management Shell commands that I use and why.  The blue words are the commands and the red words need to be replaced with your information.

When you see a Get-Mailbox, you can leave it alone and the command will get all mailboxes or you can add in switches such as -server, -database, or -identity to select smaller groups.

1. Do a user who needs full access to all mailboxes, maybe for legal or audit reasons.  Replace "useraccount" with the account of the person who needs full access.  The second line removes the full access from all mailboxes for the specified user.
Get-Mailbox | Add-MailboxPermissions -AccessRights FullAccess -User useraccount
Get-Mailbox | Remove-MailboxPermissions -AccessRights FullAccess -User useraccount

2. Need to set set the MaxSendSize and MaxReceiveSize for all mailboxes at once.  You can write the number out such as 20480 will be the same as 20MB.  Or you can use KB, MB, GB, or TB at the end to shorten it.

Get-Mailbox | Set-Mailbox -MaxSendSize 20MB

Get-Mailbox | Set-Mailbox -MaxReceiveSize 20MB

3. Run an Exchange CCR Cluster, type Get-Sto and hit tab twice to get to Get-StorageGroupCopyStatus and press enter to get a list of all of your storage groups and their copy status, copy and replay queue lengths, and last inspected log time.  This is faster then opening the EMC and going to Server Configuration/Mailbox and give you a little more information.

4. Need mailbox statistics.

This will give you details stats on a single mailbox.

Get-MailboxStastics user |fl

This will give you the mailbox name, item count, storage limit status (if you have one), and last logon time for every mailbox in the specified database.

Get-MailboxStastics -database yourdatabase

Exchange 2007 Script - 30 day old Deleted Items with email report

My current Exchange environment is a decentralized one where I maintain one configuration and others maintain their own.  We do not have mailbox policies configured for the entire organization.  So I wanted to remove any items in my users Deleted Items folders that are more than 30 days old and then be able to see how many items were removed and how much space this freed up.  This use to be easy in Exchange 2003 with Mailbox Maintenance Policies.

After several days of learning Powershell scripting and trying so many different syntaxes, I finally have a working script.  I can't say that I know much about Powershell but I definitely know a lot more than I did a week ago.  This script will need to be modified to fit your environment but I added documentation on where to do that.  You can copy the text below into your own text file and save it with the .ps1 extension.  If you copy be sure to watch for line wrapping.  Once you have made the few modifications for your environment you can set this to run on a schedule with Windows Task Scheduler.




#This Powershell script will go through every mailbox on your Exchange 2007 mailbox server and will delete
#all items in the Deleted Items folder that are more than 30 days old and then emails a report to you.
#You can set this with Windows Task Manager to run on a set schedule.
#Written by Brad Goodman

#Sets the $date variable to 30 days before today.
$date = Get-Date
$date = $date.AddDays(-30)
$date = $date.ToShortDateString()

#Sets the output file name.
$filename = "DeletedItems{0:MMddyyyy}" -f (Get-Date)

#Defines the output table.
$mbxcomb = "" | Select "Display Name", "Items Deleted", "Space Saved(KB)"

#Begins a transcipt and saves it in the Documents folder of the user running the script.
Start-transcript -Path $home\documents\$filename.txt

#Calls the mailboxes.  You can set a -database or -server to be more specific after the Get-Mailbox.
#I suggest that you set the -server to your mailbox server if you don't manage all in your Exchange org.
Get-Mailbox -server "your mailbox server" | foreach {

$mbx = $_.DisplayName
$strmbxUser = $NULL

#Determine the initial size of the mailbox in KB.
$intSizeBefore = (Get-MailboxStatistics $mbx).TotalItemSize.Value.ToKB()

#Removes any items in the Deleted Items folder that are older than 30 days.
$strMbxUser = Export-Mailbox $mbx –includefolders “\Deleted Items” –StartDate “1/1/1995” –EndDate $date –DeleteContent -Confirm:$False

#Save the number of deleted items to the variable $intTotalDeleted.
$intTotalDeleted = $strmbxUser.StandardMessagesDeleted

#Gets the new size of the mailbox after deleting items and then finds the amount of space saved in KB.
$intSizeAfter = (Get-MailboxStatistics $mbx).TotalItemSize.Value.ToKB()
$intSpaceSaved = $intSizeBefore - $intSizeAfter

#Stores the specifics about each mailbox in the table.
$mbxcomb."Display Name" = $mbx
$mbxcomb."Items Deleted" = $intTotalDeleted
$mbxcomb."Space Saved(KB)" = $intSpaceSaved

#Creates totals of mailboxes processed, items deleted, and space saved through each iteration of the
#get-mailbox.
$intmbxTotal = $intmbxTotal + 1
$intmbxDeleted = $intmbxDeleted + $intTotalDeleted
$intmbxSaved = $intmbxSaved + $intSpaceSaved

#Outputs the table to the screen so that the transcript can capture it.
Write-output $mbxcomb

}  #End of the Foreach loop.

#Outputs the totals for all mailboxes to the screen so the transcript can capture it.
Write-Host ("`n=======================================`n")
Write-Host ("Mailboxes Processed = $intmbxTotal")
Write-Host ("Total Items Deleted = $intmbxDeleted")
Write-Host ("Total Space Saved(KB) = $intmbxSaved")
Write-Host ("`n=======================================`n")

Stop-Transcript
$transcript = (Get-Content $home\documents\$filename.txt | Out-String)

#Emails transcript file.  Replace smtphost, domain, user, password, sender, recipient with your own.
$SmtpClient = new-object system.net.mail.smtpClient
$smtpclient.Host = 'smtphost'
$Credentials = new-object System.Net.networkCredential
$Credentials.domain = "domain"
$Credentials.UserName = "user"
$Credentials.Password = "password"
$SMTPClient.Credentials = $Credentials
$smtpclient.Send('sender', 'recipient', "Exchange Deleted Items Report - {0:MM/dd/yyyy}" -f (Get-Date), $transcript)