I needed to see which services in a wcf project were taking an unfair share of time. The most accessible method is to analyze the iis logs. However, I didn’t want to spend all day figuring out how to use some of the analytical features in Excel so I opted to use SQL instead.
Converting the File
The logs are in a space delimited text format by default so you’ll need to convert it to an Excel file instead.
Open excel, and then chose File.Open and browse to your file. You’ll need to change the filter to “all files”
In this case the IIS Logs are space delimited…
For the most part “general” data type is fine, but you may need to return here and change this if you run into a data type error in your query.
Save the file. Select No when prompted to save in the current format and select xlsx instead.
Querying the Excel File
Create a blank workbook. On the data tab, select From Other Sources. From Microsoft Query.
Choose Excel Files as the data source and then select the Excel file you just created.
You’ll be prompted to choose columns. You can use the picker or just click cancel to go right to the designer (say yes at the prompt to continue editing).
Add the table(s) (sheets) from your Excel file that you’d like to query.
From there you’ll be in the designer. You may want to just double click on the ‘*’ in the column selector and click the “SQL” button to get you started. After that you can execute whatever SQL you like. For example, group by request, the top running pages:
SELECT t.`cs-uri-stem`, Count(*), Sum(t.`time-taken`) AS 'totaltime', Min(t.`time-taken`) AS
'mintime', Max(t.`time-taken`) AS 'maxtime', Avg(t.`time-taken`) AS 'avgtime'
FROM `C:\Temp\u_ex130513.xlsx`.`u_ex130513$` t
GROUP BY t.`cs-uri-stem`
ORDER BY Sum(t.`time-taken`) DESC
Click ok in your query and then close query editor. You’ll then pop back into excel and will be prompted for a starting cell to place your query data.
Done. If you know SQL better than you know the functions in Excel, this is a much easier way to analyze a spreadsheet.
