Home     RSSRSS

Archives: Scripting

Loading Microsoft IIS Logs into SQL Server 2012 with Log Parser

February 1, 2014 by kiranbadi1991 | Comments Off on Loading Microsoft IIS Logs into SQL Server 2012 with Log Parser | Filed in Database, Development, Performance Engineering, Performance Test Tools, Scripting, Web Server

If you ever thing of building the customized tool for viewing and analyzing the IIS logs, the first thing, you will probably do is to think of some way of loading the IIS logs to some database specially MS SQL Express or MY SQL.

Parsing the IIS logs and loading it into the database has its own challenges, we can always write the customized code which will read each line of log file and then load it to the database table. However it will  require a specialized programming skills since parsing logs file will require you to first read the log file, remove the headers or unnecessary data in the log file and then proceed to load the required data into the Database table. So your program should be aware of all the format and complete structure of your log file so that it can handle all unexpected characters of the log file.Its quite a tedious and time consuming  task.I have loaded the IIS logs using Powershell and Log Parser into the MS SQL Express database.

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®.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.

So in this post, I will show you as how to Load the IIS Logs into the MSSQL Express database.First thing you need to do , is to download the IIS Log Parser and then Install it in your local machine or Server. Once you have installed the log parser tool, then you need know the location of your IIS logs files and you need to access that you have all the rights to access and read the IIS Logs.You will also need to ensure that you have SQL Server installed and you have complete rights on the database.Probably you need to have rights to create the table and have full rights to database.If your database is located in other machine, then you will need to ensure that you are able to connect the machine where log parser is installed and it is able to connect to the database and has access the location of IIS logs files.

Once you have installed log parser , have access to SQL Server and IIS logs location, then you can use the below example query to load iis logs to sql server database.(Below example I have loaded multiple logs into the Database)

C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130327.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10

Statistics:
-----------
Elements processed: 75
Elements output:    75
Execution time:     0.07 seconds


C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130318.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10
Task completed with parse errors.
Parse errors:
Cannot find '#Fields' directive in header of file "C:\Users\Kiran\Desktop\IIS
  Logs\u_ex130318.txt". Lines 1 to 285 have been ignored

Statistics:
-----------
Elements processed: 12968
Elements output:    12968
Execution time:     20.93 seconds


C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130319.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10

Statistics:
-----------
Elements processed: 25219
Elements output:    25219
Execution time:     54.60 seconds


C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130320.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10

Statistics:
-----------
Elements processed: 25922
Elements output:    25922
Execution time:     53.50 seconds


C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130321.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10

Statistics:
-----------
Elements processed: 27395
Elements output:    27395
Execution time:     59.74 seconds


C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130322.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10

Statistics:
-----------
Elements processed: 22772
Elements output:    22772
Execution time:     60.12 seconds (00:01:0.12)


C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130323.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10

Statistics:
-----------
Elements processed: 17497
Elements output:    17497
Execution time:     42.62 seconds


C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130325.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10

Statistics:
-----------
Elements processed: 670
Elements output:    670
Execution time:     1.35 seconds


C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130326.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10

Statistics:
-----------
Elements processed: 1135
Elements output:    1135
Execution time:     1.60 seconds


C:\Program Files (x86)\Log Parser 2.2>
Let me explain the below query which inserts the logs into the sql server database,
C:\Program Files (x86)\Log Parser 2.2>LogParser.exe "SELECT * INTO TBIIS76 FROM
C:\Users\Kiran\Desktop\IISLogs\u_ex130326.txt" -o:SQL -oConnString:"Driver=SQL S
erver;Server=KIRAN\SQLExpress; Database=IISLOGS;Trusted_Connection=yes" -createT
able:OFF -e:10

If you look closely at the above query, you can see that I have connection string to the local server KIRAN\SQLExpress and I am using the driver SQL Server and I have a database IIS Logs. Since I have windows authentication on my local machine, I am using Trusted_Connection = Yes. However if you are using network based logs, then you need to replace,

 
Trusted_Connection=yes

with

-username:yourusername -password:yourpassword 

I have given the create table flag as Off since I have table TBIIS76 already created in the database IISLogs. In case if you need the log parser to create the table by itself, then you need to set create table flag as ON.

e flag is very important flag and is more of extended logging for log parser.I will give you information as what went wrong in case if few records are not loaded into the database for any reason.

Below is example of parse errors which was given for one of the log files. Without this flag, you will spend hours trying to figure out as what is wrong in case if there are any errors. If you look at below error, you will understand as why it’s painful to write custom code to parse log files, if there is any missing status fields, then probably your program will die without giving you any indication.

Task completed with parse errors.
Parse errors:
Cannot find '#Fields' directive in header of file "C:\Users\Kiran\Desktop\IIS
  Logs\u_ex130318.txt". Lines 1 to 285 have been ignored

The table in case if it does not exists and you want log parser to create for you, then it will create one for you. The table structure along with column data format looks something like below,

image

Once the data is loaded into the SQL Server, it will open the new world for you to query the results and you can built the customized tools on top of it or use SQL Management studio to write customized query and do the kind of analysis you want to do. This is especially very helpful in analyzing Production metrics and doing capacity planning activity.

Also please note that you can upload any event logs/registry logs/http err logs etc to the database using the log parser tool.I feel using the above approach, you should be able to do all kinds of performance analysis on logs.

In the next post I will show you as how to load the IIS logs into the SQL Server at runtime using system ODBC driver.

Tags: , , , , , ,

Unit Testing – 10 Good Reasons to Avoid It

July 20, 2013 by kiranbadi1991 | Comments Off on Unit Testing – 10 Good Reasons to Avoid It | Filed in Development, Scripting

I have been participating in the unit testing discussion for quite some time and does know that Unit testing is considered as one of tools which can increase the quality of the code which in turn improves the quality of the product, but still lot many people shy away from writing the unit tests. Couple of year’s back I had asked couple of experienced developers as why they don’t always write unit tests, and I got some of below interesting answers,

  1. Clients do not pay for Unit tests since unit tests are never part of agreed deliverable.

When client hires a developer to write some functionality, rates are agreed and paid for the functionality developed by the developer. Clients in most of the cases do not really bother as how developers write their code, what type of art they use to write and develop the functionality , all clients requires is working functionality. I would say it’s fair enough expectation from client. However when IT is your long term business or your business depends heavily on IT than probably you don’t want to skip unit tests. I suggest keep an on this blog for more my thoughts in coming posts or probably you can ask some veteran who is in business of coding or in business of developing products from scratch. IT is all about code and nothing else.

2. Unit tests do not assure the quality of your code completely, we still need QA to test the code or functionality.

This is another good reason for getting rid of unit tests. There are certain types of tests which most unit tester don’t cover for various reasons. Functional Testing, System testing etc. etc. are some of the types which are not covered by current existing practices of unit testing. However technically speaking, I believe unit tests can be extended to do Functional Testing, System Testing or Performance Testing. However if you are writing unit tests, you should probably be able to cut down the QA cycle by at least 30 to 40% , if you are not able to achieve even close to 20% , then probably your unit tests are not real unit tests.

3. There isn’t enough time to write unit tests.

This is another very good reason for not writing unit tests. Probably there isn’t time to write tests because time is just not accounted for in Project Management Plan. Most Project Manager I have spoken or seen, never included or tracked the activities of Unit tests. So even if you want to write it, probably you will not have time to write it since it’s not there in project schedule.

4. Its pain to write tests when requirements are changing.

This is fairly true reason. Requirements keeps on changing and with change, it adds over heads to all teams. It’s the way life works in IT. The thumb rule of IT is whenever requirements stops, growth stops. Changing requirements is the only way to see that company is making an attempt to grow at least for those whose core business is IT.

5. It’s much appreciated to develop the functionality and toss the code to QA.

I personally believe in having rock star developers and superstar testers in a team. So I would appreciate if developers can focus on developing functionality and testers focus on unit testing that code base. However I also believe that developers needs to toss the bare minimum working code to QA that does what it’s supposed to do. Things that code is not supposed to do , things that makes code to do what it’s not supposed to do, things what code does when it’s not supposed to do etc. etc. can be taken care by testers at the code development phase itself. However most unit test guru’s out their will not favor this approach. They expect developers to test their code for its accuracy by exercising both depth and breadth of code. They believe if I have written the code, only I knows its intent best, so only I can test it in best way. Probably I believe this thought process is the one which killed unit testing in the first place.

6. We do not have robust libraries out there still which can test every bit and pieces of the code.

One of things which I always wondered what if I am using the JDK for developing my applications, do oracle provide me some help as how to unit test their classes or probably if I am using .net framework, is there any guidance coming from Microsoft as how to unit test their .net framework classes ?. This may sound like naïve thoughts, but as we get into the lower level details, it’s just more than calling the methods or checking their return values. In short there is not enough material available to get best out of unit tests.

7. They do not test the end to end integration of systems.

Unit testing was born to test smallest bit of code. Integration testing or system testing involves lot of dependency, and also lot of other work which extends beyond the developer’s desk at times. As I said earlier, unit tests can be extended to do integration tests, system tests etc. etc. I believe in extending unit tests as it will save your lot of downstream QA effort. However it needs to planned and executed properly.Probably its time for unit system tests and unit integration tests.

8. In few cases technical complexity of the code restricts the testability of the feature at the code level.

Well this statement is also true, however with growth of agile practices, lot of people believe that if your code cannot be tested, then probably it’s a bad code and should not get into production. There might be few exceptions to this like using private methods for security purposes etc.

9. There aren’t enough people to who has knowledge or skills of doing unit testing.

Skilled resources are always hard to find. However I believe that existing skillsets of QA can be leveraged to do Unit testing. Anyone who prepares the script should be able to write Unit tests. It might take time to learn the trick of the trade, but they should be able to do it.

10. Unit testing is for Young and Rookie Developers.

I had seen lot many young people (1 years to 5 years development experiences) doing unit testing and experienced ones writing the code base. I don’t know as why only young developers are made to write unit tests and experienced one shy away from it. Probably they don’t want to get associated with anything which has word “test” associated with it.

I am sure there could be many more reasons in addition to above which I might not be thought about now. But I still feel Unit testing is one core skills which can improve the quality of the product with minimal investment. It’s just that there is not enough guidance and awareness among the masses and probably that’s reason we come up with intelligent ways to avoid it.

Tags: ,

Compression,Decompression,Mobile Performance and LoadRunner

February 4, 2013 by kiranbadi1991 | 2 Comments | Filed in Decompression, Development, LoadRunner, Performance Center, Performance Engineering, Scripting, SilkPerformer

Recently I inherited some of the  LR scripts from one of my colleagues,it was all about building the json calls for stressing the backend spring security framework which was first layer of entry into the mobile infrastructure.Those scripts were simple scripts  built using the custom request with json string as a body part.One of the things that really surprised me as part of this effort was that web custom request in itself was taking close to 100ms to 300ms to do decompression of the server response during load testing.

Okay first let me give you some background,servers were configured to send the response compressed in gzip format with content encoding header as gzip.The functionality under scope had SLA of 1 sec max and quite a few functionality in scope also had SLA that was less than 500ms.Quite a challenging SLA’s I would say.But again these functionality were supposed to be accessed over the mobile device,so probably less the response time better it is for users.

Most of the response coming from the server for all functionality was served as chunked bytes,so what it means is that server sends initially some bytes as response in compressed gzip format,LR decompresses  those bytes in 5 to 10ms and then again server sends next range of bytes as chunked gzip response and then again LR will spend close to 5 to 10ms to decompress those bytes and like wise the process continues till we have final set of bytes.All these process happens in the single connection and connection never closes with the server.In case if you do have some server response validation in place, then expect that it will add another 10ms to do that validation.

Now I have measured all these times in the single iteration of vugen,these times increase exponentially when we are running the Load Test in controller or PC and this overhead of decoding the gzip content becomes a quite an issue when response time SLA are in ms.

Here is how it looks when you see the behavior in LR Vugen with decompression on in the script.You can see that it takes 5ms to decode the 154 bytes of response.Now imagine the normal webpage will have size of 2mb of data gzipped,so you can see the impact of this decoding  when size of page increase specially when response is coming as chunked bytes with no fixed content length from the server.

pic1

 

I think HP LR team might also be aware of this behavior and probably that the reason as why they might have come up with function to disable this.Use Web set option with decode content flag turned off if you are running the scripts which do not require validation and has response time SLA’s in ms.The drawback of disabling this feature is that all your correlation and other checks for server response will fail since server response will show up as binary content like below.

pic3

 

I would suggest you to disable this feature if you can and do the response validation by using the other techniques like verifying server logs etc.By disabling this you will gain close to 15 to 20% reduction in response time reported by LR.

Is this expected behavior of LoadRunner ?, I think they have to do this,unless they decode the response, none of the other function like web reg save param or web reg find will work and these functions are core functions of LoadRunner.Probably the right way is that LR should not add these decompression timing in their transaction markers.These timing really pollute the results specially for web applications or probably they can increase the speed of this decompression library what they are using in LoadRunner.

Tags: , , , ,