Using Log Parser on IIS Log files to get data on bandwidth usage.
I came across the need recently to gather information on traffic on each site hosted on a Windows IIS 7 server. to get the breakdown across each site, I needed to set up the log files to log all of the bytes-in and bytes-out for each request. Once we had that done and set up at the end of the month we were able to analyse the content of file to group together usage and find bandwidth for each site.
You will need a Windows Server, IIS, and the Log Parser tool. You can also run this over Apache Log files.
Getting the data
First, and simplest we grab the bytes in and out for each site, note the group-by in the statement, this pulls all requests together for a host name, in a SQL style manner:
logparser “select cs-host, sum(cs-bytes) as inbytes, sum(sc-bytes) as outbytes from u_ex1101.log group by cs-host”
We could get data used by type, shown here we get all for a jpg file:
logparser “select cs-host, sum(cs-bytes) as inbytes, sum(sc-bytes) as outbytes from u_ex1101.log where cs-uri-stem like ‘%.jpg’ group by cs-host”
Making the data useful with a database
So thats all very well, but it would be useful to push this straight into a database. This is the first data query we saw, but it then inserts into ta table in a SQL database:
logparser “SELECT cs-host, sum(cs-bytes) as inbytes, sum(sc-bytes) as outbytes, SYSTEM_DATE() INTO WebTraffic from u_ex1101.log group by cs-host” -o:SQL -server:localhost -database:Intranet -driver:”{SQL Native Client}” -username:sa -password:***** -createTable:Off
Here is an example using a system DSN on a windows box to insert directly:
logparser -i:IISW3C “SELECT 208, cs-host, sum(cs-bytes) as inbytes, sum(sc-bytes) as outbytes, TO_INT(TO_STRING(TO_DATE(Date), ‘yyyy’)) AS Year,TO_INT(TO_STRING(TO_DATE(Date), ‘MM’)) AS Month, SYSTEM_TIMESTAMP() INTO WebTraffic from C:\inetpub\logs\LogFiles\W3SVC1\*.log WHERE cs-host = ‘www.commseatingsys.com.au’ group by cs-host, TO_INT(TO_STRING(TO_DATE(Date), ‘yyyy’)), TO_INT(TO_STRING(TO_DATE(Date), ‘MM’)) ORDER BY cs-host” -o:SQL -server:113.192.20.246 -database:Intranet -username:***** -password:***** -createTable:Off
Apache NCSA files
Apache files are in NCSA format, and we put them into a different table, because we have to deal with them a little differently too. We enter the domain, and the date the log file is rolled over.
logparser -i:NCSA “select RemoteLogName, SUM(BytesSent),2011,3,15, SYSTEM_DATE() INTO WebTrafficMs FROM C:\Share\logs\access_log-20110315 GROUP BY RemoteLogName” -o:SQL -server:localhost -database:Intranet -driver:”{SQL Server Native Client 10.0}” -username:***** -password:***** -createTable:Off
See also:
http://www.carehart.org/logparser/
http://groups.google.com/group/fusionreactor/web/log-parser-commands-for-fr-3?pli=1
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en
http://www.iis.net/community/default.aspx?tabid=34&g=6&i=1976
