SQL Queries to IIS Logs
Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows® operating system such as the Event Log, the Registry, the file system, and Active Directory®.
Overview
There is always a case that you want to integrated IIS Logs into your project. You can relay on third party services/ interface. However, the best approach is to have one application where you can integrate the logic of IIS log reading.
Since 2009, Microsoft has introduced a tool called Log Parser that provides sql like queries against IIS Log files.
You can also download the same through choco command.
Get Started
Go to the path C:\Program Files (x86)\Log Parser 2.2\
Log parser by default provide you a COM DLL that you can import in Native C/C++ projects, or you can also import the same in .NET project that uses Interop facility.
To use COM DLL into .NET project you can also use tlbimp.exe command as well.
Open command prompt and run this simple select statement:
“C:\Program Files (x86)\Log Parser 2.2\LogParser.exe” “select * from c:\inetpub\logs\logfiles\w3svc1\u_ex150101.log”
You can also view output in a GUI through the -o:DataGrid switch value.
”C:\Program Files (x86)\Log Parser 2.2\LogParser.exe” “select * from c:\inetpub\logs\logfiles\w3svc1\u_ex150101.log” -I:w3c -o:datagrid
Command Reference:
- logparserplus.com
- http://mlichtenberg.wordpress.com/2011/02/03/log-parser-rocks-more-than-50-examples/
- LogParser Studio
- LogParser.chm file is also a great source of truth 😉
C# Integration
First thing you got to do is Add reference into your .NET project using Visual Studio IDE.
IWebLogService.cs:
public interface IWebLogService
{
List<IISLogCount> GetLogs(string fileName = null, string api = null);
List<IISLog> GetLogDetails(string uri, string fileName = null);
}
public class WebLogService : IWebLogService
{
public List<IISLogCount> GetLogs(string fileName = null, string api = null)
{
if (string.IsNullOrWhiteSpace(fileName))
{
fileName = "{0}\\*.log".FormatMessage(ConfigurationManager.AppSettings["IISLOGPATH"]);
}
if (string.IsNullOrWhiteSpace(fileName))
{
throw new ArgumentNullException(fileName);
}
string query = string.Empty;
if (string.IsNullOrWhiteSpace(api))
{
query = @"
SELECT date, cs-uri-stem, cs-method, count(cs-uri-stem) as requestcount from {0}
WHERE STRLEN (cs-username ) > 0
GROUP BY date, cs-method, cs-uri-stem
ORDER BY date, cs-uri-stem, cs-method, count(cs-uri-stem) desc".FormatMessage(fileName);
}
else
{
query = @"
SELECT date, cs-uri-stem, cs-method, count(cs-uri-stem) as requestcount from {0}
WHERE cs-uri-stem LIKE {1} and STRLEN (cs-username ) > 0
GROUP BY date, cs-method, cs-uri-stem
ORDER BY date, cs-uri-stem, cs-method, count(cs-uri-stem) desc".FormatMessage(fileName, " '%/api/{0}%' ".FormatMessage(api));
}
var recordSet = this.ExecuteQuery(query);
var records = new List<IISLogCount>();
int hit = 0;
for (; !recordSet.atEnd(); recordSet.moveNext())
{
var record = recordSet.getRecord().toNativeString(",").Split(new[] { ',' });
if (int.TryParse(record[3], out hit))
{ }
else
{
hit = 0;
}
records.Add(new IISLogCount { Hit = hit, Log = new IISLog { EntryTime = Convert.ToDateTime(record[0]), UriQuery = record[1], Method = record[2] } });
}
return records;
}
public List<IISLog> GetLogDetails(string uri, string fileName = null)
{
if (string.IsNullOrWhiteSpace(fileName))
{
fileName = "{0}\\*.log".FormatMessage(ConfigurationManager.AppSettings["IISLOGPATH"]);
}
if (string.IsNullOrWhiteSpace(fileName))
{
throw new ArgumentNullException(fileName);
}
string query = string.Empty;
query = @"SELECT"
+ " TO_TIMESTAMP(date, time) AS EntryTime"
+ ", s-ip AS ServerIpAddress"
+ ", cs-method AS Method"
+ ", cs-uri-stem AS UriStem"
+ ", cs-uri-query AS UriQuery"
+ ", s-port AS Port"
+ ", cs-username AS Username"
+ ", c-ip AS ClientIpAddress"
+ ", cs(User-Agent) AS UserAgent"
+ ", cs(Referer) AS Referrer"
+ ", sc-status AS HttpStatus"
+ ", sc-substatus AS HttpSubstatus"
+ ", sc-win32-status AS Win32Status"
+ ", time-taken AS TimeTaken"
+ " from {0} WHERE cs-uri-stem = '{1}' and STRLEN (cs-username ) > 0 ORDER BY EntryTime".FormatMessage(fileName, uri);
var resultSet = this.ExecuteQuery(query);
var records = new List<IISLog>();
for (; !resultSet.atEnd(); resultSet.moveNext())
{
var record = resultSet.getRecord().toNativeString(",").Split(new[] { ',' });
records.Add(new IISLog { EntryTime = Convert.ToDateTime(record[0]), UriQuery = record[1], Method = record[2], UriStem = record[3], UserAgent = record[6] });
}
return records;
}
internal ILogRecordset ExecuteQuery(string query)
{
LogQueryClass logQuery = new LogQueryClass();
MSUtil.COMW3CInputContextClass iisLog = new MSUtil.COMW3CInputContextClass();
return logQuery.Execute(query, iisLog);
}
}
This is how my POC looks like:
public class IISLog
{
public string LogFilename { get; set; }
public int RowNumber { get; set; }
public DateTime EntryTime { get; set; }
public string SiteName { get; set; }
public string ServerName { get; set; }
public string ServerIPAddress { get; set; }
public string Method { get; set; }
public string UriStem { get; set; }
public string UriQuery { get; set; }
public int Port { get; set; }
public string Username { get; set; }
public string ClientIpAddress { get; set; }
public string HttpVersion { get; set; }
public string UserAgent { get; set; }
public string Cookie { get; set; }
public string Referrer { get; set; }
public string Hostname { get; set; }
public int HttpStatus { get; set; }
public int HttpSubstatus { get; set; }
public int Win32Status { get; set; }
public int BytesFromServerToClient { get; set; }
public int BytesFromClientToServer { get; set; }
public int TimeTaken { get; set; }
}
public class IISLogCount
{
public IISLog Log
{
get;
set;
}
public int Hit { get; set; }
}
Once the Service class has been defined, you can create your proxy whichever you want it.
Here I am using ApiController driven class as a proxy. This class will be passing any Http request to this service and returning back the response in Http protocol.
[HttpGet]
[Route("applications/iislog")]
public IHttpActionResult GenerateIISLog(string fileName = null, string api = null)
{
return Ok(_weblogService.GetLogs(fileName, api));
}
[HttpGet]
[Route("applications/iislogdetails")]
public IHttpActionResult GenerateIISLogDetails(string uri, string fileName = null)
{
return Ok(_weblogService.GetLogDetails(uri, fileName));
}
I’m using NInject IoC/ DI framework for injecting service to the controller:
kernel.Bind(typeof(IWebLogService)).To(typeof(WebLogService)).InRequestScope();
Points of Interest
As you can see in my above sample that I could managed to read a text file using SQL commands. LogParser is fantastic tool for retrieving usage stats/ bandwidth , slow pages and many more details.
One thing I like the most with LogParser is it can even communicate with Directory Service Logs, Mail Logs, Event View Logs and et al; that too using SQL commands.