Why are my SharePoint Audit Logs missing the MachineName and MachineIP data?

Audit Log Reporting seems to be one of those topics within SharePoint that is still a little misunderstood.  I have blogged on it before here, and suggest that if you are not familiar with Audit Logs that you view that post quickly before reading on.  There is also an overview which can be read here on the office.microsoft.com site.

I received a question about Audit Log Reporting from one of the readers of my blog a few months ago, and since then it hasn't been the first time I've heard it :

I read your blog about SharePoint Audit Log Report. I'm trying to get a report but I cannot get the "Machine Name" and "Machine IP" data. Can you help me? Is there any settings that I'm missing?

My answer was that there were no settings missing, and unfortunately the data missing is by design. 

Here is a row taken straight from the Audit table :

Site Id Item Id Item Type User Id Machine Name Machine IP Document Location Location Type Occurred (GMT) Event Custom Event Name Event Source Source Name Event Data
ed91340f-e335-45d2-82f3-c6521eb23fc0 59af845e-b604-436e-9c08-0a948a27d996 Document NT AUTHORITYlocal service _catalogs/masterpage/Editing Menu/CustomSiteAction.xml

As you can see the MachineName and MachineIP values are mysteriously null, in fact querying the table for a row where they were not null returns zero results, they are never used. Unfortunately the reason in this case is to view KB939246, which has the following as a cause : The values in the MachineIP column and in the MachineName column appear as NULL because of privacy concerns. By design, Windows SharePoint Services 3.0 works in this manner.

This feature is not going to be reporting MachineName or MachineIP of actions in the audit log any time soon, it is a bit of a shame because I can imagine some occasions where this could be useful.  Keep in mind that your IIS logs will still contain all the IP information, and if you have a timestamp you should have no problem tracing that request back to a particular IP.  Unless you have your site set to be edited by anonymous users, the UserID field is going to be far more valuable when utilizing these reports, however you may need to perform a join to the userinfo before you will get anything particularly relevant out of it, i.e.

SELECT     UserInfo.tp_Login AS UserLogin, UserInfo.tp_Title AS UserTitle, AuditData.SiteId, AuditData.ItemId, AuditData.ItemType, AuditData.DocLocation,
                      AuditData.Occurred, AuditData.Event, AuditData.EventName, AuditData.EventSource, AuditData.SourceName, AuditData.EventData
FROM         AuditData
INNER JOIN UserInfo ON AuditData.UserId = UserInfo.tp_ID

I would not be surprised if the functionality to turn back on the MachineIP and MachineName reporting could be enabled by flicking a switch in the registry or database, but as yet I have had no luck finding it.  Maybe someone else out there will, good luck!

December Cumulative Updates for SharePoint re-released.

If you have tried to install the recently released December Cumulative Update for SharePoint you may have noticed that the original release actually fails during the post-installation configuration.  Jeremy Thake came across the same issue at and has more detail on it here http://wss.made4the.net/archive/2008/12/20/sharepoint-december-cumulative-update-error-and-resolution.aspx . I had difficulty installing this myself, but was able to get around the errors that occur by taking similar steps to Jeremy.  

 Fortunately the SharePoint Team noticed this fairly quickly and have released an updated version of December Cumulative Updates that no longer causes this issue.The revised version can be downloaded from the updated links on the original SharePoint team blog post : http://blogs.msdn.com/sharepoint/archive/2008/12/17/announcing-december-cumulative-update-for-office-sharepoint-server-2007-and-windows-sharepoint-services-3-0.aspx, and now there is a small comment on the page reflecting the investigation and update to the installer. 

A comment from myself on this particular update, finally it is actually cumulative!  This update can be applied from SharePoint SP1 upwards, however, as always, just because hotfixes are released does not mean they need to be installed!  Gauge the need to install them carefully against the problems they resolve.  For most installations SP1 + Infrastructure Updates will be more than sufficient, with the exception of any security updates that have or may be released in the future, i.e.  MS08-077.

Revisited – Content Deployment error? How to find those checked out items within Sharepoint

A month or so ago I put up a post about how to find checked out items within a Sharepoint site.  This particular error tends to bring up problems with Content Deployment, as CD cannot occur if there are any items checked out.

Unfortunately there is no inbuilt method to find these items for an Administrator, the only way within the standard Sharepoint interface if that on the Site Actions menu, each user can check if there are any items still checked out and quickly check them back in.  Training users to do this can be problematic however, and sometimes it is just easier to point them at the items that are still checked out.

I had a query to do this, but one of the readers has suggested a much better query than my own for finding these items :

SELECT AllUserData.tp_DirName, 'http://SiteName/' + AllUserData.tp_DirName, AllUserData.tp_LeafName,
case when AllUserData.tp_ContentType = 'Item'
then 'http://SiteName/' + AllUserData.tp_DirName + '/DispForm.aspx?ID=' + AllUserData.tp_LeafName
else 'http://SiteName/' + AllUserData.tp_DirName + '/' + AllUserData.tp_LeafName
end as Link,AllUserData.tp_ContentType, AllUserData.nvarchar1, AllUserData.nvarchar2, AllUserData.tp_ModerationStatus, AllUserData.tp_DeleteTransactionId, AllUserData.tp_IsCurrent
FROM AllUserData AllUserData
WHERE (AllUserData.tp_ModerationStatus=2)
AND (AllUserData.tp_DeleteTransactionId=0x0)
AND (AllUserData.tp_IsCurrent=1)
ORDER BY AllUserData.tp_DirName, AllUserData.tp_LeafName

Just replace the http://SiteName/ with the url to your Site, and use this on the database of your Web Application and you are good to go. Thank you very much to rwoods@city.pg.bc.ca for supplying this great query.

Content Deployment error? How to find those checked out items within Sharepoint

If you have content deployment running you have probably seen this error before :

10/30/2008 1:01 AM You cannot perform this action on a checked out document. at Microsoft.SharePoint.SPListItem.SetRequiredInfoForUpdateItem(Boolean bDocLib, Boolean bAdd, Boolean bMigrate) at Microsoft.SharePoint.SPListItem.PrepareItemForUpdate(Guid newGuidOnAdd, Boolean bMigration, Boolean& bAdd, Boolean& bPublish, Object& objAttachmentNames, Object& objAttachmentContents, Int32& parentFolderId) at Microsoft.SharePoint.Deployment.ListItemSerializer.AddOrUpdateDoclibItemVersion(SerializationInfoHelper infoHelper, SPListItem& listItem, SPWeb web, Guid newId, String& listItemServerRelativeUrl, Boolean bIsPublish, Boolean exists, String version, Boolean isFirstVersion, Boolean isLastVersion, StreamingContext context, ISurrogateSelector selector, ImportObjectManager objectManager) at Microsoft.SharePoint.Deployment.ListItemVersionSerializer.AddListItemVersion(SPWeb web, SPListItem listItem, Guid newId, Boolean editHistory, Boolean existsInDb, Boolean isFirst, Boolean isLast, Boolean isDocLib, StreamingContext context, XmlElement listItemData, SPImportSettings settings, ImportObjectManager objectManager, SerializationInfoHelper listItemInfoHelper, String& listItemServerRelativeUrl, ISurrogateSelector selector) at Microsoft.SharePoint.Deployment.ListItemSerializer.UpdateListItemVersionData(SerializationInfoHelper infoHelper, SPWeb web, SPListItem& listItem, Guid newId, Boolean existsInDb, Boolean isDocLib, String& listItemServerRelativeUrl, StreamingContext context, SPImportSettings settings, ISurrogateSelector selector) at Microsoft.SharePoint.Deployment.ListItemSerializer.SetObjectData(Object obj, SerializationInfo info, StreamingContext context, ISurrogateSelector selector) at Microsoft.SharePoint.Deployment.XmlFormatter.ParseObject(Type objectType, Boolean isChildObject) at Microsoft.SharePoint.Deployment.XmlFormatter.DeserializeObject(Type objectType, Boolean isChildObject, DeploymentObject envelope) at Microsoft.SharePoint.Deployment.XmlFormatter.Deserialize(Stream serializationStream) at Microsoft.SharePoint.Deployment.ObjectSerializer.Deserialize(Stream serializationStream) at Microsoft.SharePoint.Deployment.ImportObjectManager.ProcessObject(XmlReader xmlReader) at Microsoft.SharePoint.Deployment.SPImport.DeserializeObjects() at Microsoft.SharePoint.Deployment.SPImport.Run()
10/30/2008 1:01 AM Content deployment job 'Remote import job for job with sourceID = bc232239-4545-4050-a8fe-85a5e4b8b112' failed.The exception thrown was 'Microsoft.SharePoint.SPException' : 'You cannot perform this action on a checked out document.'

 Pretty obviously this error is caused by a checked out document, which can be a real pain to find if you have a big site with multiple authors.  The following query when run on your content database (run this on your authoring database, not your production one!) will find all checked out items and tell you roughly where to look, which filename, and the user responsible.

SELECT tp_dirname, tp_leafName, userinfo.tp_title, userinfo.tp_email
FROM AllUserData
NNER JOIN AllLists on AllUserData.tp_listid = AllLists.tp_id
INNER JOIN userinfo on alluserdata.tp_checkoutuserid = userinfo.tp_id
WHERE tp_checkoutUserId != ''
order by tp_dirname, tp_leafname

Increasing the maximum upload limit on a Sharepoint Site

A new colleague here asked me today how to configure WSS v3 for files larger than the default limit of 50mb today.  I asked him to quickly tap it into google as I figured there would be hundreds of posts out there on how to do it, but sadly almost all of them seem to be for WSS v2.

Fortunately most of the principles are still the same, except there are far less steps to do :

1.  Increase the Web Applications Upload limit.
2.  Increase the HTTP Timeout.
3.  Increase the Execution Timeout and MaxRequestLength in web.config

Typically step 1 should only be necessary on an Intranet environment, but steps 2 and 3 should only be necessary if you are on a slow connection, or getting timeout errors when attempting to upload large files.

The instructions for each step are below :

1.  Open Central Administration. Go to "Application Management", then "Web Application General Settings".  Ensure you are on the correct web application and modify the "Maximum Upload Size" field.

2.  Open IIS, find the appropriate website and open its properties.  The timeout setting is on the Website tab and in the connections setting under "Http Connection Timeout".  I'd probably consider adding 120 seconds per extra 50 mb, more or less depending on your connection.

3.  Edit C:Program FilesCommon FilesMicrosoft SharedWeb server extensions12TEMPLATELAYOUTSweb.config

From : 

  <location path="upload.aspx">
      <httpRuntime maxRequestLength="2097151" />

To : 

  <location path="upload.aspx">
      <httpRuntime maxRequestLength="2097151" executionTimeout="999999"/>

Edit the web.config in the home directory of the IIS Site of your web application.

From :

<httpRuntime maxRequestLength="51200" />

To :

<httpRuntime maxRequestLength="51200" executionTimeout="999999" />

Slipstreaming your Sharepoint installation, easy!

If you have been regularly installing Sharepoint farms you might notice that in the last 12 months there has been a large amount of time consuming hotfixes to deploy with each server installation of both WSS and MOSS.  With MOSS you've got all the WSS ones as well as the copious amount of MOSS hotfixes.

Here is the current patch path as recommended by the Microsoft Sharepoint Team :

1. Windows SharePoint Services 3.0 Service Pack 1
2. The 2007 Microsoft Office Servers Service Pack 1
3. The Windows SharePoint Services Infrastructure Update x86 x64
4. The Microsoft Office Servers Infrastructure Update x86 x64
5. KB 953397: Excel Server Security Update x86 x64
6. KB 955586: Document Lifecycle Workflow Update
7. August Cumulative Update for Windows SharePoint Services 3.0 (Global)
8. August Cumulative Update for Windows SharePoint Services 3.0 (Local)
9. August Cumulative Update for Microsoft Office Servers

Wow thats a few patches needed!  I started to get fairly jack of installing these before I was even installing 3 – 9, so heres a really brief and easy way to slipstream the install.

1. Download all of the above patches
2. Take a copy of the MOSS dvd.  Keep note of the path you copied this to.
3. One by one use the following syntax to pull out all of the information from the hotfixes :

hotfixname.exe /extract:<path to moss folder>x86updates /passive

This will extract all the files in the hotfix to your copy of the MOSS DVD x86update folder. 

Now when you run setup, you wont need to spend half an hour patching afterwards.  For WSS the same principle applies, except use the WSS patches only, and probably start with the WSS 3.0 SP1 installer.

Force Deletion of an SSP

There is a lot of pain involved with removing an SSP thats having errors due to the database no longer existing.  Of course trying to remove the database via Central Administration and even STSADM, I got alot of errors such as "Cannot open database." and "Failed to delete SSP".

Fortunately there is an undocumented switch for the "STSADM -o DeleteSSP" command that will help in this situation. 

"Stsadm -o deletessp -title SSPNAME -force".

 The -force will delete the entry even if there are errors occuring. 

There are less aggressive methods, such as using stsadm -o deleteconfigurationobject, but if you are trying to connect to a non-existant database, that one probably wont work.  To do this one, query the objects table in the config database for your server, looking for the SSP name in the "name" column, Then take the ID of that object and use it against the stsadm -o deleteconfigurationobject command.

.NET Loading extremely slowly

One of my Sharepoint servers has been painfully slow after installation, even running stsadm would take about 10 seconds.  Whats really infuriating about it is that during the time frame where STSADM is typed into the command prompt, and its response, the server is doing next to nothing.

I've seen this behaviour before, however it was on an extremely overloaded ESX platform, so I initially thought it was due to that and didn't bother to look closely into it.  This time is on a glorious HyperV system that has resources out the wazoo, so should have absolutely no reason to run anything slow.  My guess from the outset was that something was timing out, and eventually erroring and after spending some time with FileMon trying to find something, I eventually turned to NetMon and ran a couple of captures.

The first time I found nothing interesting, but the second time I ran it I found a frame referring to an outbound HTTP connection attempt to  A few frames later I saw the same request again, but never saw a response.   I did a quick tracert to that IP address and immediately noticed this :

Tracing route to crl.microsoft.com []

CRL is short for "Certificate Revocation List", for more information click on the link.  It turns out for every single signed assembly being loaded, .Net is checking the CRL to ensure that the key used to sign the assembly is legitimate. 

To confirm that it was this problem contacting the CRL server that was the problem, I decided to try a quick fix and added an entry for CRL.microsoft.com into my hosts file, pointing to localhost.  After doing this the problems I'd had were almost instantly resolved.  This however is only useful as a diagnosis, stopping the server from accessing crl.microsoft.com has some long term security risks, and is not advisable. 

Methods to fix the error :

  • Fix the problem with the internet access

If you can't do the above solution :

  • Point CRL.microsoft.com to in a hosts file
  • Untick the "Check for Publishers Certificate Revocation" under security in the advanced Internet Explorer options.
  • Put <generatePublisherEvidence enabled="false" /> into your application's .config file.

I'd really recommend just going with the first one however.

Locking down the people picker to an OU

I thought I'd already covered this, but a quick look through my history shows I didn't.

Locking down a Web Application's people picker to a specific OU is very easy, but not well known.

stsadm -o setsiteuseraccountdirectorypath -url http://WEBAPPLICATIONURL -path "OU=OU,DC=DOMAIN,DC=COM"

Its really that simple, just point the path to the distinguished name of your OU, and you're set!


Accidental cross domain results in People Picker

For a while I have had an open ticket with a client in regards to odd behavior while trying to create a new site collection.  The odd behaviour follows : "In Central Administration – when trying to create a new site collection and using the people picker to select the Site Collection Administrator, users from other domains are appearing."  Obviously for a hosting provider this is pretty bad behavior to have happen. 

It turns out that there are a very large amount of posts out there for allowing a people picker to search cross forest or cross domain, and very few about how to lock them down or limit them, so here we are.

Like most organisations, we try to lock our active directory environments down as much as possible, and this means removing list view for all authenticated users, except to their own OU.  Having a client be able to view entire organisations that were in different domains or forests is fairly counter-productive and needs immediate resolution.

I had initially thought that this might be fixed with AD permissions, but thats a bridge I'd rather not cross right now, if possible I'd rather fix this up at the people picker level.

There are quite a few commands that might help out here :

  • We could limit the people picker to a specific OU.  That would fix the problem, unless the same OU exists on one of the other domains.
  • We could limit the people picker to only show people within the site collection.  That would work ordinarily, but when creating a new site collection, you may want users that dont exist in a site collection yet.  And besides, which site collection would this pick to find the users in?  Thats one for another time.
  • We could limit the people picker to only one domain.  Bingo.

The peoplepicker-searchadforests command is perfect for this.   So I tap in the following command :

stsadm -o setproperty -pn peoplepicker-searchadforests -pv "domain:domaintosearch.com,USERNAME,PASSWORD" -url http://centraladminurl

Sidenote : The first time I ran this I got an error

Cannot retrieve the information for application credential key.

This was as I had not set an apppassword.  A quick run of "stsadm -o setapppassword -password PASSWORD" fixed this very quickly.  Make sure you keep that password written down, just in case.

Figuring that this is happening at the Central Administration level, I use the Central Admin web application as my target.  This returns a successful result, and so I go off to Central Administration again to test this.  It didn't work, I could still see multiple other domains. 

After a bit of musing, I decided to this feature against the Web Application I was trying to create the site collection into.  Bingo this worked.   Obviously when creating a new site collection,  the Central Administration people picker must inherit the settings from the selected Web Application, this would probably explain that long wait when you select a different Web Application.

Additional Note :

Make sure if you run this, you add the username and password to the command.  If you don't you'll lock your people picker down so well you wont find anyone.