My ULS Query

Anyone who has worked with SharePoint *should* be familiar with the ULS log where SharePoint can log nearly every operation. Most recent versions allow for a portion of those log files to be cataloged into a database called ULSTraceLog that makes searching easier.

SharePoint logs the records based on UTC time.

However, sometimes, you need to find the exact log file on the SharePoint server based on an event in the database. Depending on how your log files roll over, this might be a challenge.

Below is the work in progress query to convert the UTC time to local time and then further convert to 12 hour time. It is written as a CTE so I could get more familiar with that and it seems to work fine but I don’t know the performance around two CTE’s:

,CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, [ULSTraceLog].LogTime), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as LogTimeLocal
FROM [DEV_SP_CFG_UsageAndHealth].[dbo].[ULSTraceLog]
,substring(convert(varchar(max), LogTimeLocal,20),0,12) + substring(convert(varchar(20), [LogTimeLocal], 9), 13, 5) + ‘ ‘ + substring(convert(varchar(30), [LogTimeLocal], 9), 25, 2) as LogTimeLocal12Hour from CTE_UlsLogFile
select * from CTE_ULSLogFile_InLocal


Comments or thoughts please!


System.Data.SqlClient.SqlException: User does not have permission to perform this action.

A quick post around granting administrators  to the search service application in SharePoint 2013. I used AutoSPInstaller to install and configure a set of SharePoint Lab,Dev,Test and Prod SharePoint environments.

Continue reading

Report Builder 3.0–Application with the same identity already installed

Just a quick note, is if you are trying to use report builder, deployed via ClickOnce, in multiple environments (dev,test,prod) you will have to clean the ClickOnce cache by running, from a command line,:

rundll32 dfshim CleanOnlineAppCache


Thanks to:


While building out yet another SharePoint 2013 development environment, I decided to change a number of service accounts and delete the old ones from Active Directory. Everything seems fine until I ended up trying to go the http://centralAdmin:5555/admin/FarmCredentialManagement.aspx page to add some other ones. Instead of the page, I got an error message the logged the error the ULS:

Application error when access /_admin/FarmCredentialManagement.aspx, Error=Some or all identity references could not be translated.

I used


To show me all the managed accounts and it became obvious to show the accounts that had been deleted in AD but not cleaned up from the farm.

UserName             PasswordExpiration    Automatic ChangeSchedule
——–             ——————    ——— ————–
LABS\svcSP.FarmAdmin 7/4/2013 5:49:43 AM   False
LABS\svcSP.MySite…                       False
LABS\svcSP.Search… 7/4/2013 5:49:44 AM   False
LABS\svcSP.Services  7/4/2013 5:49:45 AM   False
LABS\svcSP.Portal…                       False
LABS\svcSP.MySite_AP 7/17/2013 3:08:12 AM  False
LABS\svcSP.Portal_AP 7/17/2013 3:08:19 AM  False
LABS\svcSP.Search_AP 7/17/2013 3:16:07 AM  False

A quick check documented the broken ones: (All on one line)

Get-SPManagedAccount | ? {$_.PasswordExpiration -eq $ null}

UserName             PasswordExpiration    Automatic ChangeSchedule
——–             ——————    ——— ————–
LABS\svcSP.MySite…                       False
LABS\svcSP.Portal…                       False


And a further one deleted it: (All on one line)

Get-SPManagedAccount | ? {$_.PasswordExpiration -eq $null}

| Remove-SPManagedAccount

Hope that helps anyone…