SharePoint - Working with IIS Logs

  • Article

Hi Search Folks

As with ULS Logs (see my previous entry https://blogs.msdn.microsoft.com/nicolasu/2016/12/20/sharepoint-working-with-uls/) , IIS is also a good source of data for troubleshooting SharePoint issues whether you need to analyze some user behavior or look at the response times of your pages or search queries.

Assuming you are now familiar with Log Parser Studio, you might have seen that the IIS log format aka IISW3CLOG is built-in which makes our life much easy.

So how do I proceed ?

  • Open LogParser Studio
  • Create a new query.
  • The below query is adding extract columns for analysis enrichment. It also excludes 401 requests by default.
 select EXTRACT_FILENAME(LogFilename) as filename,LogRow,
date,time,s-ip,cs-method,cs-uri-stem,cs-uri-query,s-port,TO_UPPERCASE(cs-username) as cs-username,c-ip,cs(User-Agent) as UserAgent,cs(Referer) as Referer,sc-status,sc-substatus,sc-win32-status,time-taken,
to_int(to_string(to_timestamp(date,time),'yyyy')) as TYear,
to_int(to_string(to_timestamp(date,time),'MM')) as TMonth,
to_int(to_string(to_timestamp(date,time),'dd')) as TDay,
to_int(to_string(to_timestamp(date,time),'hh')) as THour,
to_int(to_string(to_timestamp(date,time),'mm')) as TMinutes,
to_int(to_string(to_timestamp(date,time),'ss')) as TSeconds,
to_lowercase(EXTRACT_FILENAME(cs-uri-stem)) as fname,
to_lowercase(EXTRACT_EXTENSION(cs-uri-stem)) as fext,
case div(time-taken, 1000) when 0 then '00-01' else case div(time-taken,3000) when 0 then '01-03' else case div(time-taken,5000) when 0 then '03-05' else case div(time-taken,10000) when 0 then '05-10' else case div(time-taken,20000) when 0 then '10-20' else case div(time-taken,30000) when 0 then '20-30' else '30+' end end end end end end as PerfBucket
FROM '[LOGFILEPATH]'
where sc-status<>401
  • Select the input type as IISW3CLOG

2017-01-24_1050

  • Now it is time to indicate to LogParser Studio where your IIS log files are located.

One very good thing of LogParser is its ability to query against multiple files as input and merge them all into one single output.

  • Click on the Log File Manager and add all IIS logs you want to process.

2017-01-24_1056

  • You're all set to execute your first IIS query!
  • You would notice two extra columns : Filename and RowNumber. Those are LogParser specific, automatically generated upon processing. They will always appear when executing SELECT * queries. Filename can be used to maintain the origin of your IIS or differentiate IIS logs set.

Note that the query doesn't specify any output file hence the resulting data would only be shown in LogParser Studio pane, not very ideal for analysis.

  • To output in a CSV or TSV, add the INTO clause BEFORE THE FROM clause
 INTO '[OUTFILEPATH]\Output.TSV' 
FROM '[LOGFILEPATH]'
where sc-status<>401
  • Output into a TSV or CSV will open Excel to visualize the output generated from your query.

2017-01-24_1115

 

Et voila !You are now all set to quickly process IIS logs. Don't forget to save your query !

Resources

  1. Analyze SharePoint Products and Technologies Usage
  2. Other blogs related to LogParser and IIS.

 

Stay in Search !