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!

Advertisements

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
I
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">
    <system.web>
      <httpRuntime maxRequestLength="2097151" />
    </system.web>
  </location>

To : 

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

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" />

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 131.107.115.28.  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 [131.107.115.28]

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 127.0.0.1 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.