Find SharePoint Mail-enabled Lists

Imagine the following: A user mail-enabled a SharePoint list, or two, or two hundred, and then disappeared. Maybe they found another job; maybe the left for Mexico; maybe they irritated Joey the Hippo and are keeping company with the fishes. Who knows? Frankly, it doesn't matter, beyond giving me the opportunity to offer literary abuse to you, my dear reader.

You are tasked with finding out where all these emails are going. You don't know, so, you turn to Bing, or Google (you don't use any other search engines, I hope), and find me here. I had the same problem. I also had a lot of mail getting stuck in the SharePoint drop folder, and I wanted to get a handle on why it was there.

Behold!

I call this "Get-SPMailEnabledLists"

############################################################################################################
# The following secion iterates through an entire farm. Two closing loops at the end finish out the        #
# For-Each login here                                                                                      #
############################################################################################################
$Farm = Get-SPWebApplication
Foreach ($WebApplicationname in $Farm)
    {
    $WebApplication = Get-SPWebApplication -identity $WebApplicationname
    $WebAppSites = $WebApplication.Sites
    Foreach ($SiteCollectionUrl in $webappsites)
        {
############################################################################################################
# End farm interative initiation                                                                           #
############################################################################################################
               
        $site = Get-SPSite -identity $SiteCollectionUrl
        foreach($web in $site.allWebs)
            {
            $lists = $web.lists
            ForEach($list in $lists)
                {
                if( ($list.CanReceiveEmail) -and ($list.EmailAlias) )
                    {
                    write-host "Current URL:" $web.Url     
                    write-host "Title of list: " $list.Title
                    write-host "Email of list: " $list.EmailAlias
                    }
                }  
            }
        }
    }

You can wrap the snippet above in a emailer or transcripter-er. Format as you like.

Enjoy.

Online Meeting: Make sure that you are signed in to Lync.

So I got a call. A user got a wonderfully friendly, useful, explanatory error message when she tried to schedule a Lync Online Meeting. Since I still can't seem to find any hits online for why this is happening, I'll paste the entire super-awesome message here for search engines to index and you to find:

The request failed. Please try again. Make sure that you are signed in to Lync.

Over the last few months, I've found that users who experience this error message are usually sending an Online Meeting out to lots of recipients. Why does this matter? Well, it's because the problem is due to one (or more) of your meeting recipients, not with the actual user.

This obviously presents a troubleshooting challenge. My standard steps are:

• Ask the user to send you a list of all of the recipients.
• Normalize this list from user-ese to csv or txt.
• Run a handy little script of your choosing to get user properties
$csv = import-csv c:\scripts\userlist.csv
foreach ($record in $csv) {
     $user = $record.Users
     get-csuser -identity $user | out-file users.log -Append       
} 
 
• Now, inspect your log for any oddities associated with one of the recipients. Usually, one will have a funky policy associated with them.
• If this doesn't help (sometimes the case), run the same script with a get-csaduser and inspect your log for users whose SIP address in Lync doesn't match the corresponding SIP address Exchange thinks they should have.
• If the above step seems too cumbersome, and you have a good user, you can always start carving off chunks of the recipients until you narrow down the one causing the issue.

Now that you've identified the problem recipient, have the meeting organizer remove that person and send their meeting request.

Fix the problem recipient. They either need a policy changed, or some massaging done to make Lync and Exchange agree.

For me, this usually happens when a user gets married, and their name got changed by somebody who decided that the steps you gave them for modifying a SIP address were too cumbersome. Now they have a SIP in Exchange that doesn't match Lync.

I typically will coordinate with the user to change their SIP address (ALWAYS CHANGE THIS IN THE LYNC POWERSHELL CONSOLE) to something temporary, then change it back to what it is supposed to be.

First:

$user = Read-Host -Prompt "Enter user name"
$usertomovesip = "sip:" + $user + "@dummydomain"
Set-CSUser -Identity $user -SipAddress $usertomovesip

Then

$user = Read-Host -Prompt "Enter user name"

$usertomove = Get-CSAdUser -Identity $user
$usertomovesmtp = $usertomove.windowsemailaddress
$usertomovesip = "sip:" + $usertomovesmtp
Set-CSUser -Identity $user -SipAddress $usertomovesip

Now, wait for Lync and Exchange to agree. Depending on your AD topology, this might take a few minutes. You can check the progress through "get-csaduser" and monitoring the Exchange properties.

If the users use cached Exchange mode, it may take a day or two for everything to replicate out, but your work should now be done.

Lync Group Chat: OCSChat Account? Really?!

You stood up Group Chat way back in the OCS 2007 days. Originally a proof of concept you threw out there on a day you weren't too horribly busy, it's become a critical business tool. You upgraded it to 2007 R2, but that wasn't too painful. Organically growing through your organization, the highest profile cells of users (Call Center and Help Desk) use it as their main forum for communications. Now you've moved everyone to Lync, and the only remnant of OCS 2007 R2 is the Group Chat server, a service account with a mysterious SIP URI of OCSChat@yourdomain.tld, and licensing issues you want to avoid.

Welcome to my world.

I stood up the Lync group chat environment. I made all of the appropriate DNS changes. Automatic sign-in, however, is horribly, inextricably tied to the default account of OCSChat@yourdomain.tld. Why? Wouldn't it have made life a lot easier if Microsoft had somehow allowed you to set the default search account to something you picked, rather than something related to an earlier version of the platform? Well, yeah. But, I don't make the world, I just try to manipulate it to make it work for my users and my boss.

My plan is to cut everyone over from OCS chat to Lync chat in one quick, band-aid(r) ripping, soul-crushing move. I do not intend to move historical chat data from OCS to Lync, so I get to run the environments concurrently, and have ensured all the appropriate rooms exist in the new environment with the appropriate settings. How do I cut this?

I could migrate the OCSChat account from OCS to Lync, update the Lync Group Chat environment to use the new lookup URI, restart (or wait for an hour or so) all your front-ends to get their tiny little minds wrapped around the new home for OCSChat, and then move on. There isn't much fallback in that, though. Alternately, I could change the SIP URI of the service account with OCSChat@yourdomain.tld to something like Legacy_OCSChat@yourdomain.tld and set up a new service account to use the default SIP URI in Lync. This way I haven't migrated users and I have a little bit of fallback.

There's one little problem with the above scenarios, and, like most relationships, I can say that "It's not you, it's me." I don't LIKE the default SIP URI. I'm trying REALLY HARD to get rid of OCS, and having an account out there saying "Hey, I'm OCS!" is going to annoy me for years to come.

Instead, let's figure out how to set a new SIP URI to be the default lookup account for Lync Group chat. If you look here: %AppData%\Roaming\Microsoft\Group Chat\Common\Accounts you'll see the Automatic Configuration XML file for group chat. It probably look something like this:

<?xml version="1.0" encoding="utf-8"?>

<AccountConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<name>Automatic Configuration</name>

<serverType>OCSGCF</serverType>

<sipServerType>OCS</sipServerType>

<autoDetectLookupServer>true</autoDetectLookupServer>

<lookupServerUserName></lookupServerUserName>

<useSso>true</useSso>

<useTcp>false</useTcp>

<lastUsedLoginName />

<lastUsedLoginUri></lastUsedLoginUri>

<domainName>yourdomain.tld</domainName>

<hasLoggedIn>true</hasLoggedIn>

<lcs>

<host />

<port>5060</port>

<securePort>0</securePort>

<useSecureComms>true</useSecureComms>

</lcs>

<globalCatalog>

<findGlobalCatalog>true</findGlobalCatalog>

<host />

<allowNonSSL>false</allowNonSSL>

<useCredentials>false</useCredentials>

<account />

<maxSearchResultSize>200</maxSearchResultSize>

<ADSyncFrequency>10</ADSyncFrequency>

</globalCatalog>

</AccountConfiguration>

We only care about the fields I made red... So how can we realistically set those for users? The answer is in the GroupChatConsole.adm file that comes with your Group Chat installation. Get your friendly domain administrator to apply (in a limited TEST first) this policy to your Group Chat users with the value of your lookup URI. Basically: everything after the "sip:" here:

 

 

The only value in the policy is for "Speficy lookup server URI" to be (in my case) RTCService@yourdomain.tld. Applying this policy will place a file called _gpo_ in the same folder as your Automatic Configuration XML file. It will look like this:

<?xml version="1.0" encoding="utf-8"?>
<AccountConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <name>Group Policy Configuration</name>
  <serverType>OCSGCF</serverType>
  <sipServerType>OCS</sipServerType>
  <autoDetectLookupServer>false</autoDetectLookupServer>
  <lookupServerUserName>RTCService</lookupServerUserName>  <useSso>true</useSso>
  <useTcp>false</useTcp>
  <lastUsedLoginName />
  <lastUsedLoginUri>YOUREMAIL@yourdomain.tld</lastUsedLoginUri>
  <domainName>yourdomain.tld</domainName>
  <hasLoggedIn>true</hasLoggedIn>
  <lcs>
    <port>0</port>
    <securePort>0</securePort>
    <useSecureComms>false</useSecureComms>
  </lcs>
  <globalCatalog>
    <findGlobalCatalog>true</findGlobalCatalog>
    <allowNonSSL>false</allowNonSSL>
    <useCredentials>false</useCredentials>
    <maxSearchResultSize>200</maxSearchResultSize>
    <ADSyncFrequency>10</ADSyncFrequency>
  </globalCatalog>
</AccountConfiguration>

Now, as your users apply that policy and log out and back in to Group Chat, they will be presented with a new configuration option for logging in. They will have to type their SIP URI, and rejoin their chat rooms.

Now, next time you upgrade Group Chat, you can just not worry about the "OCSChat" SIP URI, and can just stand up your new environment with a lookup URI that makes you happy. Then change the policy when you cut, and move on.

Large SharePoint Lists and Second-stage Recycle Bin

What's that? I'm starting my Lync blog with a SharePoint entry? Well, in a word: yes. Since I happen to manage and wrestle with both environment, I run into daily issues with both. For your reading enjoyment, you will be subjected to my own twisting of space and time, multiple revisions where I am always the hero, and literary abuse to be envied by the most verbose of Woot's(r) writing staff.

A month ago, I started a day off with a call from a SQL DBA, asking me WHY one of my SharePoint databases was going nuts, consuming 20% CPU on the box, and just generally crapping all over the environment. Due to my own amazing powers of deduction (and a general idea about where people tend to do things perhaps not completely planned out or properly executed), I determined that there was a mail-enabled discussion board taking in ~4000 emails/hour.

First things first: Shut off email intake for the discussion board and tell the guy managing the devices sending the emails to see a proctologist and get his head examined.

Second things, well, second: Clean out the discussion board. Woah... Not so fast there, Turbo. You said it has 1.7 MILLION list items? Nobody will ever read those, so retaining them is just a waste. Deleting them at 1500 rows and 2 minute wait per delete would be... 37.77777 hours, if I don't take a break, go to the restroom, etc. While some people might be happy to log the bulk of an entire work week to mind-numbingly refreshing and deleting out of SharePoint, them people don't get employed as SharePoint administrators at my company.

As a loyal Microsoft Office Server Stack implementer(er), off to http://www.bing.com I go. Finding a good script to do what I want to do is hard, but you learn to hack things together. Eventually, I realize there isn't anything out there close to what I need. I corner my friendly PowerShell guru, and eventually we come up with this:

SCRIPT BLOCK:

param([string]$Url, [string]$List, [switch]$help)
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
function GetHelp()
{
$HelpText = @"
DESCRIPTION:
NAME: Empty-LargeSPList
Deletes all items in a large SharePoint List.
PARAMETERS:
-url        Url to SharePoint Site
-list       List name
SYNTAX:
Empty-LargeSPList -url http://moss -list BigItemsList
Deletes all items in a large list.
Empty-LargeSPList -help
Displays the help topic for the script
"@
$HelpText
}
function Empty-LargeSPList([string]$url, [string]$list)
{
$site = New-Object Microsoft.SharePoint.SPSite($url)
$web = $site.openweb()
$buildlisturl = $url + "/lists/" + $list
$buildlist = $web.getlist($buildlisturl)

$count = $buildlist.itemcount
Write-Host "Items in list: " $count
$x = [int]($count/20)
foreach($iteration in $x..0)
{
$listquery = New-Object Microsoft.sharepoint.spquery
$listquery.rowlimit = 20
$removinglistquery = $buildlist.GetItems($listquery)
Write-Host "Deleting iteration:" $iteration
 foreach($i in 19..0)
    {
        $removinglistquery.delete($i)
    }
}
}
if($help) { GetHelp; Continue }
if($url) { Empty-LargeSPList -url $url -list $list}

Wait. NO. Seriously? Crap. Yes, Virginia, all that stuff I deleted DID go into the recycle bin. Well, actually, it didn't, and that's a GOOD THING(tm). However, I find that the recycle bin is full because somebody's been cleaning up that list manually for a while. Now that it's all aging off, my timer jobs to clean up the recycle bin are falling all over themselves? Why yes. Yes they are.

Back to Bing, where I find a nice script. Problem was, it gives no feedback and only nukes 50 rows at a shot. That's OK, for I know how to " | Get-Member".

Still: Thanks to Kirk Evans for doing to leg work on the script:
http://blogs.msdn.com/b/kaevans/archive/2010/10/29/emptying-the-second-stage-recycle-bin-in-sharepoint-2007.aspx

param([string]$Url, [switch]$help)

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

function GetHelp()
{
$HelpText = @"

DESCRIPTION:
NAME: Remove-SPSiteSecondStageRecycleBin
Empties the second-stage recycle bin for a Microsoft.SharePoint.SPSite Collection

PARAMETERS:
-url Url to SharePoint Site Collection

SYNTAX:

Remove-SPSiteSecondStageRecycleBin -url http://moss

Empties the second stage recycle bin for the SiteCollection.

Remove-SPSiteSecondStageRecycleBin -help

Displays the help topic for the script

"@
$HelpText
}

function Remove-SPSiteSecondStageRecycleBin([string]$url)
{
$siteCollection = New-Object Microsoft.SharePoint.SPSite($url);

$recycleQuery = New-Object Microsoft.SharePoint.SPRecycleBinQuery;
$recycleQuery.ItemState = [Microsoft.SharePoint.SPRecycleBinItemState]::SecondStageRecycleBin;
$recycleQuery.OrderBy = [Microsoft.SharePoint.SPRecycleBinOrderBy]::Default;
$recycleQuery.rowlimit = 500000 $recycledItems = $siteCollection.GetRecycleBinItems($recycleQuery);

$count = $recycledItems.Count;
write-host "Items in recycle bin: " + $count

for($i = 0; $i -lt $count; $i++)
{
$g = New-Object System.Guid($recycledItems[$i].ID);
write-host "Deleting: " + $g
$recycledItems.Delete($g);
}

$siteCollection.Dispose()
}

if($help) { GetHelp; Continue }
if($url) { Remove-SPSiteSecondStageRecycleBin -url $url }

So, now my DBA is happy. My users notice a performance improvement in SharePoint, so they are happy. I didn't spend 37.77777 hours deleting things from a discussion board, and another 37.77777 hours deleting them from the recycle bin, so my boss sure ought to be happy.