logparser for ULS reference post

  • Article

 

Most of the time I try to write posts of issues that I’ve encountered or things that might help customers with their implementations of enterprise software. Currently I’m working on a project where we are trying to make some proactive changes for my customer and as part of this endeavor we are reviewing SharePoint ULS logs and hoping to remediate any issues that we find.

We used the powershell commandlet Merge-SPLogFile to gather a 6 hour window of logs from every server in the farm and I used log parser to import these into SQL. I had quite a struggle with Log Parser as it's not my every day tool. However I finally got it to import using the following query:

 

logparser.exe -i:tsv -o:sql –server:<sql server>-database:<dbname> -maxStrFieldLen:8000 -createTable:on -clearTable:on "select 0 as Testcolumn, Timestamp,TO_TIMESTAMP(STRCAT(STRCAT(STRCAT(YearText,MonthText),DayText),TimeText), 'yyyy-MM-dd hh:mm:ss') as ActualDate, TO_INT(TRIM(SUBSTR(Timestamp,6,4))) AS Year, TO_INT(TRIM(SUBSTR(Timestamp,0,2))) AS Month, TO_INT(TRIM(SUBSTR(Timestamp,3,2))) AS Day, TRIM(SUBSTR(Timestamp,11,8)) AS Time, TRIM(SUBSTR(process,ADD(INDEX_OF(process,'('),1),SUB(INDEX_OF(process,':'),ADD(INDEX_OF(process,'('),1)))) AS Server, TRIM(SUBSTR(Process,0,INDEX_OF(Process,'('))) AS Process, TID, Area, Category, EventID, Level, Message, Correlation USING STRCAT(SUBSTR(Timestamp,6,4), '-') as YearText, STRCAT(SUBSTR(Timestamp,0,2), '-') as MonthText, STRCAT(SUBSTR(Timestamp,3,2), ' ') As DayText, SUBSTR(Timestamp,11,8) as TimeText, SUBSTR(Timestamp,0,22) as MyDate2 from Test.log to ULS"

Columns created in SQL by “select”:

Clause from SELECT

Description

0 as Testcolumn

this column was to address an issue with log parser and the fact that the ULS logs are not 100% true TSV (tab delimited)

Timestamp

this is the actual timestamp column from the ULS log

TO_TIMESTAMP(STRCAT(STRCAT(STRCAT(YearText,MonthText),DayText),TimeText), 'yyyy-MM-dd hh:mm:ss') as ActualDate

parses a date from the Timestamp column using predefined variables from the USING clause

TO_INT(TRIM(SUBSTR(Timestamp,6,4))) AS Year

TO_INT(TRIM(SUBSTR(Timestamp,0,2))) AS Month

TO_INT(TRIM(SUBSTR(Timestamp,3,2))) AS Day

TRIM(SUBSTR(Timestamp,11,8)) AS Time

columns to separate Year, Month, Day, and Time into queryable columns

TRIM(SUBSTR(process,ADD(INDEX_OF(process,'('),1),SUB(INDEX_OF(process,':'),ADD(INDEX_OF(process,'('),1)))) AS Server

column parses the server name out of the Process column of the ULS log

TRIM(SUBSTR(Process,0,INDEX_OF(Process,'('))) AS Process

column for the name of the process that logged the entry into the ULS log

TID

thread ID

Area

Major area for ULS category

Category

sub-category for major area

EventId

ULS tag ID

Level

Severity of the entry

Message

Text of the ULS entry

Correlation

Correlation ID (if present) to associate with an end-user error

   

 

 

After this one can use any number of tools to get data from the SQL database: PowerPivot, SQL direct query, etc. As I said earlier, *most* of the time I post with the intention of helping others… this one is for me. Smile  As a reference so that I can re-use the LogParser query later.

 

*NOTE: to give credit where credit is due the above query was a collaborative effort between myself and a couple of peers.