How to find Concurrent Users for SharePoint and ASP.NET using IIS Logs


This post is about finding the number of concurrent users. The concurrent users helps to do the capacity planning for SharePoint. Below are the main attributes required to do the capacity planning for SharePoint 2010/2013.

Average daily RPS

Average RPS at peak time

Total number of unique users per day

Average daily concurrent users

Peak concurrent users at peak time

Total number of requests per day

 

One of the main attribute for the planning is the Concurrent users. Below are the steps required to find the concurrent users

  1. Enable the IIS Logs in the server
  2. Collect the logs for particular period
  3. Install Log Parser Tool
  4. Analyse the Log file using the Log Parser.
  5. Finally can plot a graph or use the data in any form

You can download the load.txt and the bat file in the skydrive

Step 1:

Before collecting the IIS log files, make sure the below attributes are enabled in the IIS. These attributes are the key to do analysis. The below fields can be enabled in IIS.

Field

Column name

Date

date

Time

time

Client IP Address

c-ip

User Name

cs-username

Method

cs-method

URI Stem

cs-uri-stem

Protocol Status

sc-status

Protocol SubStatus

sc-substatus

Bytes Sent

sc-bytes

Bytes Received

cs-bytes

Time Taken

time-taken

User Agent

cs-user agent

IIS Log

Step 2:

After enabling the necessary attributes, allow the application to be used for few days. After few days collect the IIS log from the c:\inetpub\logs\logfiles\. The log files will be scattered with multiple directories. Usually it is better to set s particular directory to the IIS site for easy gathering of log files. The log files will be *.log extension. Usually the analysis is done out of the server. So collect all the files and copy it to the local desktop for analysis.

Step 3:

Install the log parser tool in the local laptop or the desktop where the log files are collected. The log parser can be downloaded from the below location

http://www.microsoft.com/en-us/download/details.aspx?id=24659

After installing the logparser better to add the logparser exe path to the environment Path folder. It will be easy to execute the logparser from any directory from command prompt if added the path to environment variable PATH.

Step 4:

To get the concurrent users follow the below steps

Copy the below text into the Load.txt file

select EXTRACT_FILENAME(LogFilename),LogRow,

date, time, cs-method, cs-uri-stem, cs-username, c-ip, cs(User-Agent), cs-host, sc-status, sc-substatus, sc-bytes, cs-bytes, time-taken,

add(
   add(
      mul(3600,to_int(to_string(to_localtime(to_timestamp(date,time)),’hh’))),
      mul(60,to_int(to_string(to_localtime(to_timestamp(date,time)),’mm’)))
   ),
   to_int(to_string(to_localtime(to_timestamp(date,time)),’ss’))
) as secs,

to_int(to_string(to_localtime(to_timestamp(date,time)),’yy’)) as yy,
to_int(to_string(to_localtime(to_timestamp(date,time)),’MM’)) as mo,
to_int(to_string(to_localtime(to_timestamp(date,time)),’dd’)) as dd,

to_int(to_string(to_localtime(to_timestamp(date,time)),’hh’)) as hh,
to_int(to_string(to_localtime(to_timestamp(date,time)),’mm’)) as mi,
to_int(to_string(to_localtime(to_timestamp(date,time)),’ss’)) as ss,

to_lowercase(EXTRACT_PATH(cs-uri-stem)) as fpath, 
to_lowercase(EXTRACT_FILENAME(cs-uri-stem)) as fname, 
to_lowercase(EXTRACT_EXTENSION(cs-uri-stem)) as fext

from *.log

where sc-status<>401

After copying the above text into load.txt run the log parser. Assume that all the IIS files are in the c:\iislogs\. Run the command prompt in admin mode. use the commnd to navigate to the log folder say cd c:\iislogs.

In my case the log files are in d:\log files\april 2013

image

image

Run the below command in the command prompt to create a bigo.csv in the log folder which later can be used for analysis.

logparser -i:IISW3C file:load.txt -o:csv -q >bigo.csv

The above command will create a bigo.csv in the folder. I have created a bat file based on the blog mentioned here http://blogs.msdn.com/b/markarend/archive/2012/02/24/measuring-concurrent-site-users.aspx

Create a Batch file named ConCurrentUser.bat and add the below script into it

SET inputCSV=%1
if ‘%inputCSV%’==” GOTO USAGE

REM outputCSV has no extension, it’s added later
SET outputCSV=UserConcurrency

SET concurrencyPeriod=%2
if ‘%concurrencyPeriod%’==” GOTO USAGE

SET concurrencyField=%3
if ‘%concurrencyField%’==” SET concurrencyField=c-ip

REM Set a filter to match requests that should be excluded
REM %%Service%% matches our service accounts (like search), exclude them
REM …if you don’t want a filter, use SET filter=0 IS NULL
SET filter=(cs-username LIKE ‘%%Service%%’)

echo.
echo Counting Concurrent Users
echo inputCSV         : %inputCSV%
echo outputCSV        : %outputCSV%.csv
echo concurrencyField : %concurrencyField%
echo concurrencyPeriod: %concurrencyPeriod% seconds

echo.
echo First stage, quantizing to %concurrencyPeriod% seconds…
logparser -i:CSV -o:CSV “SELECT DISTINCT %concurrencyField%, date, QUANTIZE(TO_TIMESTAMP(time,’hx:mx:sx’), %concurrencyPeriod%) AS Period, COUNT(*) as Requests INTO temp1-%outputCSV%.csv FROM %inputCSV% WHERE %concurrencyField% IS NOT NULL AND NOT %filter% Group By  Date, Period, %concurrencyField%”

echo.
echo Second stage, grouping…
logparser -i:CSV -o:CSV “SELECT date, to_string(to_timestamp(Period,’hx:mx:sx’),’hh’) as Hour, Period, count(%concurrencyField%) as UserCount, sum(Requests) as RequestCount INTO temp2-%outputCSV%.csv From temp1-%outputCSV%.csv Group by date, Period”
logparser -i:CSV -o:CSV “SELECT Hour, avg(UserCount) as Concurrent-Users(q%concurrencyPeriod%), sum(RequestCount) as Total-Requests(q%concurrencyPeriod%) INTO %outputCSV%-%concurrencyPeriod%.csv From temp2-%outputCSV%.csv GROUP BY Hour ORDER BY Hour”

GOTO DONE

:USAGE

echo.
echo # Usage:
echo #
echo # ConcurrentUsers inputCSV seconds [fieldname]
echo #
echo # inputCSV : csv file (or *.csv) of log entries with fields: date, time, c-ip or [fieldname], other
echo # seconds  : concurrency quantization level.  Typical values 300 and 600 seconds
echo # fieldname: field to evaluate for concurrency.  Typical values c-ip (default) and cs-username
echo #
echo # Example  : ConcurrentUsers BigO.csv 300
echo.

:DONE

Now run the below command in the command prompt to get the concurrent users for multiple periods

ConcurrentUser BigO.csv 300

ConcurrentUser BigO.csv 1200

ConcurrentUser BigO.csv 3600

Using the result from the above command we can plot the graph in the excel.

Graph

Advertisements

8 thoughts on “How to find Concurrent Users for SharePoint and ASP.NET using IIS Logs

  1. Hi,
    log parser give me the following back:

    Error: SELECT clause: Syntax Error: unknown field ”hh”

    To see valid fields for the IISW3C input format type:
    LogParser -h -i:IISW3C

    Any idea?

    1. I don’t know if you allready solved your issue but I had the same and the solution was to change the quotes (‘) around al tha variables from ‘ to `

  2. Hi Senthamil.. this is a great post.. I was able to follow your blog, however in the last step when I try to run ConcurrentUser BigO.csv 3600 or 1200 (any value) I always see the Concurrent Number if Users as ‘1’ in the excel o/p file. For sure my log file has more than 200unique users at any given time.. any help is appreciated.. Thanks.. Syed

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s