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:

WITH CTE_UlsLogFile
AS
(
SELECT TOP 100
[LogTime]
,CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, [ULSTraceLog].LogTime), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as LogTimeLocal
,[MachineName]
,[ProcessName]
,[ThreadID]
,[Area]
,[Category]
,[Level]
,[EventID]
,[Message]
,[CorrelationId]
,[RowCreatedTime]
FROM [DEV_SP_CFG_UsageAndHealth].[dbo].[ULSTraceLog]
ORDER by LogTime DESC
),
CTE_ULSLogFile_InLocal
AS
(
Select
*
,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!