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)

How to completely remove SQL 2005 for DPM reinstall.

This is specifically for Windows Server 2008 Enterprise 64bit with Microsoft Data Protection Manager 2007 and SQL 2005 SP2 but I see no reason why it wouldn’t work on any version of Windows Server, with or without DPM and for probably any service pack level of SQL 2005.  This will remove all databases and config and put you back to ground zero.  Be sure to have backups if necessary first.

Ever try to uninstall Microsoft Data Protection Manager with SQL 2005 on the same machine and then reinstall it?  It doesn’t work very well mostly because SQL Reporting Services doesn’t completely uninstall.  The reinstallation of DPM won’t get past the prerequisite check.  Even though you may have uninstalled SQL from Add/Remove Programs, not everything is gone.  References to the MS$DPM2007$ database are still present, mostly in the registry.

After countless hours of research and trying all kinds of methods I finally found a way to do it.  I took pieces from several different forums and guides and came up with a solution.

I was trying to pull the old database back in but it was too corrupt and would crash the system each time.  So I got plenty of practice with this complete uninstall method and it worked every time.  The official instructions from Microsoft to uninstall SQL 2005 Manually (http://technet.microsoft.com/en-us/library/bb418915.aspx) is difficult to follow and the way I describe is easier and more complete.  This method worked for me because I wanted to go back to zero to start again.  If you are not trying to do the same, I suggest you find another way.  Follow these directions at your own risk.

Step 1. Backup what you want to keep first.  Then stop all SQL component and DPM services in services.msc.

Step 2. Uninstall from Add/Remove Programs.  DPM always uninstalled fairly well from Add/Remove programs.  SQL usually uninstalled but not close to completely.  If one of the SQL components won't uninstall, don't worry we will remove it all through the registry.  Restart after this step.

Step 3. Delete the DPM and SQL folders from wherever you installed them to.  Usually at:
C:\Program Files\Microsoft DPM
C:\Program Files\Microsoft SQL Server
C:\Program Files (x86)\Microsoft SQL Server

Step 4. Open IIS Manager and under the Default Web Site, remove both Reports and ReportServer directories.  Then go up to Application Pools and remove the ReportServer app pool.

Step 5. ****Warning: editing the registry is dangerous****  Some of this taken from Gilham Consulting wesite (http://www.gilham.org/Blog/Lists/Posts/Post.aspx?List=aab85845-88d2-4091-8088-a6bbce0a4304&ID=190).  
Time to open Regedit.exe and delete some keys.  Remove the following keys if they are still present:
HKLM\SYSTEM\CurrentControlSet\Services\MSSQLInstanceName$
HKLM\SYSTEM\CurrentControlSet\Services\MSRS 2005 Web Service
HKLM\SYSTEM\CurrentControlSet\Services\MSRS 2005 Windows Service
HKLM\SYSTEM\CurrentControlSet\Services\ReportServerInstanceName$
HKLM\SYSTEM\CurrentControlSet\Services\SQLAgent$InstanceName$
HKLM\SYSTEM\CurrentControlSet\Services\SQLBrowser
HKLM\SYSTEM\CurrentControlSet\Services\SQLWriter
HKLM\SOFTWARE\Microsoft\Microsoft SQL Native Client
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server 2005 Redist
HKLM\SOFTWARE\Microsoft\MSSQLServer
HKLM\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Native Client
HKLM\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server
HKLM\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer


Step 6. Do a search in regedit for the InstanceName$ of your SQL instance if you know what it is.  If this is a Microsoft DPM server, your instance name will be MS$DPM2007$.  If you find one and it is a value, not a key, press ctrl+tab to move to the key and then delete the whole key.  If they are under LEGACY keys you won't be able to delete them and they are fine.  There may be 4 or 5.  Close out of regedit.


Step 7. Download and install Little Registry Cleaner (http://sourceforge.net/projects/littlecleaner/).  Open this little app and let it scan the registry.  It will find several leftovers.  You can look through and select or de-select the ones you want.  These are broken links that it finds and it should be safe to remove them all.  Press the Fix button to have them removed.  Restart the server.


Step 8. Your server is now free of SQL and you can begin installing again.  DPM will make it past the pre-reqs for SQL now and as long as there aren't other issues not covered by this post, you should be able to reinstall DPM now.


Reference links:
http://www.gilham.org/Blog/Lists/Posts/Post.aspx?List=aab85845-88d2-4091-8088-a6bbce0a4304&ID=190
http://support.microsoft.com/kb/909967

Exchange 2007 - Administratively set mailbox send and receive size limits

These commands can be used in the Exchange Management Shell to set the MaxSendSize and MaxReceiveSize for all mailboxes at once.  If you use a number like 20480, that will set it as KB.  Or you can use MB at the end to shorten it.

get-mailbox | set-mailbox -MaxSendSize 20MB

get-mailbox | set-mailbox -MaxReceiveSize 20MB

One or more users cannot be added to the folder access list. Non-local users cannot be given rights on this server.

Exchange 2007
If you are trying to add a mail-enabled distribution or security group to a resource such as a shared calendar or task list and you may get the error "One or more users cannot be added to the folder access list.  Non-local users cannot be given rights on this server."

If you can't do it from within that shared resource mailbox you may be able to do it from a regular user mailbox that has full access to that shared resource mailbox.  Then in Outlook with that user mailbox go to Tools, Account Settings, Change, More Settings, Advanced tab, and add that resource mailbox.  Click Ok, Next and Close out.  Now you have access to that mailbox on the left column of Outlook.

Open the Folder view so you can see all the folder and then right click on Tasks or Calendar or the folder you want to add permissions to.  Go to Properties and the Permissions tab.  Now you should be able to add your mail-enabled distribution or security group to this resource.