Log Parser – Querying IIS Logs

Recently ​I needed to get statistics about a web site running on the production server. I found the following Microsoft software easy to use and it gave me the results I needed really quickly.

Background

Log Parser 2.2 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, IIS logs and Active Directory. You tell Log Parser what information you need and how you want it processed. The results of your query can be custom-formatted in text based output, or they can be persisted to more specialty targets like SQL, SYSLOG, or a chart. Most software is designed to accomplish a limited number of specific tasks. Log Parser is different… the number of ways it can be used is limited only by the needs and imagination of the user. The world is your database with Log Parser.

How To Use It

I copied the date range of logs I needed from production to my laptop in order to run the query against them as I was querying 1 years worth of data. You can run the UI against the live logs as it doesn’t lock the file, but I wanted to do this in isolation from the production server.

I was able to run the following query against the logs for 2017 to give me stats around how many people had accessed the application on a daily basis for 2017.

  • Open log parser
  • Point it at the log files from the relevant environment
  • Type your sql syntax and press execute

Example SQL syntax

select cs-uri-stem, cs-username , count(cs-uri-stem), TO_DATE(TO_LOCALTIME(TO_TIMESTAMP(date, time))) AS [LocalDate] from '[LogFilePath]' where cs-uri-stem = '/CMS/MainFrameset.aspx' group by cs-uri-stem, cs-username, TO_DATE(TO_LOCALTIME(TO_TIMESTAMP(date, time))) order by TO_DATE(TO_LOCALTIME(TO_TIMESTAMP(date, time))), count(cs-uri-stem) desc

 

Download Log Parser 2.2 (command line)

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

Download Log Parser Studio (UI)

https://gallery.technet.microsoft.com/office/Log-Parser-Studio-cd458765

50 Examples to get you going

https://mlichtenberg.wordpress.com/2011/02/03/log-parser-rocks-more-than-50-examples/

SQL Server 2016 Temporal Tables

A temporal table is a new type of user table in SQL Server 2016, it’s designed to keep a full history of data changes and allow easy point in time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (i.e. database engine).

– Auditing all data changes and performing data forensics when necessary
– Reconstructing state of the data as of any time in the past
– Calculating trends over time
– Recovering from accidental data changes and application errors

getting-started-with-temporal

This post is when you already have tables in place in SQL Server. See the Microsoft article at the bottom of the article that explains how to create them from Scratch. If your like me you’ve been using SQL Server through the versions.

Create the history schema

CREATE SCHEMA history; 
GO

Converting an existing table

ALTER TABLE dbo.components
ADD ValidFrom datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_ValidFrom DEFAULT SYSUTCDATETIME(), 
 ValidTo datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_ValidTo DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'), 
 PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo); 
GO
ALTER TABLE dbo.components
 SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.components))

Querying the data

You now easily query the audit history of the components table by using FOR SYSTEM_TIME BETWEEN and then specify the dates.

SELECT * FROM dbo.components 
 FOR SYSTEM_TIME 
 BETWEEN '2017-05-19 00:00:00.0000000' AND '2017-05-19 00:00:00.0000000'

Converting back to a normal table

If you wish to remove system versioning apply the following:-

ALTER TABLE dbo.components SET (SYSTEM_VERSIONING = OFF)

Then turn off period definition – without turning this off you won’t be able to remove the ValidFrom and ValidTo columns/constraints.

ALTER TABLE dbo.components  DROP PERIOD FOR SYSTEM_TIME;

Then we can delete the DF_ValidFrom and DF_ValidTo constraints

ALTER TABLE dbo.components DROP CONSTRAINT DF_ValidFrom;
ALTER TABLE dbo.components DROP CONSTRAINT DF_ValidTo;

We can then remove the two ValidFrom and ValidTo columns

ALTER TABLE dbo.components DROP COLUMN ValidFrom
ALTER TABLE dbo.components DROP COLUMN ValidTo

You can then drop the history table from the history schema. Warning – this will remove all history – be careful!)

DROP TABLE history.components

Continue reading “SQL Server 2016 Temporal Tables”