LogParser, PowerShell and Quick and dirty parsing of IIS files

For a local enterprise IIS system you do not have to resort to Google analytics or other beasts that interprets IIs logs. After all, users are identified through ActiveDirectory, does not matter from what city do they come, and so on. But it will help to have some details of wjhat happened on the system this day( or the day before , if you want to send an email about the previous day totals)

So logparser to help  – he knows already to read IIS logs with  -i:IISW3C

So I have come up with the syntax  :

C:\LogParser -e:10 -i:IISW3C “SELECT cs-uri-stem as url, DIV(SUM(time-taken),1000) as Seconds, Count(time-taken) as Requests, DIV(Seconds ,Requests) as TimeExecuting   INTO C:\newfile FROM   C:\Windows\System32\LogFiles\W3SVC1\ex100909.log GROUP BY cs-uri-stem Having SUM(time-taken)>0 and Seconds>0 order by Seconds   desc” -o:TPL -tpl:%2\iistime.tpl

Basically , this will do this report about statuses of URL requested  :

Status Requests
200 1541
302 89
401 11
403 61

The problem is that C:\newfile and  C:\Windows\System32\LogFiles\W3SVC1\ex100909.log are hard-coded – we need to modify every time… So PowerShell to the rescue (Ok, I could do a C# Console program – but

1. it is more fun this way – fun meaning I want to learn something new

2. the script could be modified easily

)

So the same command is written this way with arguments , in order to can be executed each time :

%2\LogParser -e:10 -i:IISW3C "SELECT cs-uri-stem as url, DIV(SUM(time-taken),1000) as Seconds, Count(time-taken) as Requests, DIV(Seconds ,Requests) as TimeExecuting   INTO %2\%4 FROM   %5\*%1  GROUP BY cs-uri-stem Having SUM(time-taken)>0 and Seconds>0 order by Seconds   desc" -o:TPL -tpl:%2\iistime.tpl

But who will give arguments ( such as the system date ) ?Now powershell  to the rescue :

$namepc = (gc env:computername)
$a = get-date
$a = (get-date).AddDays(-1)
$allpath= Split-Path -Parent $MyInvocation.MyCommand.Path;
$logfolders = $env:WINDIR +"\system32\Logfiles\W3SVC*"
foreach($logfolder in  Get-ChildItem $logfolders)
{
$logfiles= $logfolder.FullName
Write-Host "parsing"  $logfiles
$log =  $a.ToString("yyMMdd") + ".log"
$process = [Diagnostics.Process]::Start($allpath + "\iis.bat" , $log + " "+ $allpath + " "+ $log + ".html" + " TIME" +$log + ".html" + " " + $logfiles)
$process.WaitForExit()
$content = "<h1>IIS REPORT " + $namepc  + "</H1>"
$content += (get-content ($allpath  + "\" + $log + ".html"))
$content += (get-content ($allpath  + "\TIME" + $log + ".html"))

$SmtpClient = new-object system.net.mail.smtpClient
$SmtpServer = "your server"
$SmtpClient.host = $SmtpServer

$mm = new-Object System.Net.Mail.MailMessage(“<a href="mailto:from@yourcompany.com">from@yourcompany.com</a>”,"<a href="mailto:to@yourcompany.com">to@yourcompany.com</a>")
$mm.Subject = "Report IIS " + $namepc
$mm.Body = $content
$mm.Body=$mm.Body.Replace("&lt;cmp&gt;",$namepc  )
$mm.IsBodyHtml = 1
$SmtpClient.Send($mm)
}

Explanation of code :
line 1: I take the computer name to put in the report
line 2 : take the date ( if you want the current date , just comment the line 3)
line 5 : I go to usual path to logfiles (did I say quick and dirty ?)
line 6 : get all W3SVC folders and iterate to send report
line 11 : launching the bat (that contains logparser command ) in order to parse  arguments
line 12 :waiting for the process to exit – in order to can send files.
line 13 to 15: get the output
line 17 to 27 : send the output by email

Homework :
1. Execute script on a system and modify if it does not work
2. Clean up the temporary files after sending email
3. Instead of sending an email, write into a database with the current date.

You can execute sc.bat at regular times ( such as 1:00 AM)

Here is the zip file with sources logiisparser

LogParser download : http://www.microsoft.com/downloads/en/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en

Powershell scripts : http://gallery.technet.microsoft.com/ScriptCenter/en-us/site/search?f[1].Type=SearchText&f[1].Value=internet&f[0].Value=applications&f[0].Type=RootCategory&f[0].Text=Applications&x=0&y=0

Logparser quick and dirty

Sometimes you must find information in text files. Many,many text files, like IIS logs or other custom non-regular formats.

I have a bot from http://www.imified.com/ – and I log the messages with log4net in text files, with another messages.

An entry looks like that :

System.ArgumentException: ;channel=private;botkey=<guid>;userkey=<guid>;user=name@yhaoo.com;network=Yahoo;msg=hello;step=1;value0=hello;to=asdasd

And there are multiple log files that I want to parse and find the email adresses to collect feedback from those persons that use my bot.

LogParser to the rescue! Download from http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en and use this command line

LOGPARSER “Select Text into a.csv from current* where Text like ‘%@%'” -i:TEXTLINE

Explanation of command :

Select Text into a.csv from current* where Text like ‘%@%’ –means find in files that begin with current(current*) all text that contains emails ( ‘%@%’) and put in file a.csv  the results.

-i:TEXTLINE – means the format is text

What can be more simple ?

(Ok, for finding the user name I had to resort to excel, to remove duplicates … )

More I think it is fast enough : for parsing 114 files with 58.8 MB (PC with a 2GB RAM + 7200 RPM ) the results are :

Statistics:
———–
Elements processed: 487176
Elements output:    1044
Execution time:     7.69 seconds

Also logparser can be used for more than text files :

http://support.microsoft.com/kb/910447

http://www.stevebunting.org/udpd4n6/forensics/logparser.htm

More, it can be as a COM DLL in every .NET project, making it a usefull tool . See

http://www.codeproject.com/KB/recipes/SimpleLogParse.aspx

Next time I will show the using Powershell in combination with LogParser.

First Install of tools for programmer

My primary tools are Visual Studio ( and the Express suite) , Sql Server ( and SQL Server Management Studio )( and the Express suite) and Office (Excel, Word)

Those are the modification that I do every time … I wish there were enabled by default :

For VS2010

Go to=> Tools, Options , Html, Formatting ,Check “ Insert attribute value quotes when typing”

image

This saves me a lot of time , when I put : input type=”text” , the “  are inserted automatically.

For SSMS

Go to=> Tools, Options , Designers , uncheck “Prevent saving changes that require table re-creation”

image

As a developer, I do modify tables … and this wont let me the first time …

For Excel, Word

Alt+F11 (Visual Basic Editor), Tools, Options, Editor Tab, uncheck “Auto syntax check”, check “require variable declaration”

image

“require variable declaration”  :This saves you a lot of time debugging or writing “Option Explicit” .

“Auto syntax check “ : I do not want a message box each time I do an error. The error is seen in red … no need for a Msgbox.

How about you, dear reader ? Do you have programs that require after installation modifying of options ?

TT files – generate enum from database

Many times you will program against a table that contains something like an enum , like Status( open=1 , close=2, sent=3, approved=4 )  .

It is peculiar to wrote those status as text in the other tables – and you do not like also to have update their codes in the C# (VB.NET) code source each time you will add another one.

Rather , it is convenient to auto-generate from database at once.

But how to do it in Visual Studio ? The answer is .tt files – the files that generates also POCO

So here it is my own template for such enum from database .

To use ,unzip, add to your project that contains the edmx and do what is says below- and you will see as many  .cs file as tables want to put.

<#
//*********************************************************
//
//    NO Copyright .Use at your own risk.
//    Please modify :
//    1) the names of tables to generate enums : string nameforenum
//    2) the connection to the database : string connectionstring
//    3) the name of the model : string inputFile
//    Then save the file and you will have an enum …
//*********************************************************
#>

GenerateEnum

Why the posts can not be equal

There are multiple times that I have been accused of having made the posts either too heavy, either too simple , either the code is not good, either…

I have to say that :

1. You can not follow me, if you want …

2. The reason of the posts is that I want to share my experiences with another and gather feedback.

So stick with me and please help me with what project should I start

Project

I am trying to think about doing a open source project to experiment with various MVC / EF / WPF /and so on features.
I was thinking about

  1. Image gallery or
  2. Employee management system.

What kind of project do you think it worth the effort to implement ?
Thank you for your suggestions!

C# quiz

Fast small quiz for sunny afternoon :
What is writing at the console those lines : ( please do not use a compiler) :


            decimal? v = 100;
            Console.WriteLine(v);
            decimal? x = null;
            x += v.Value;
            Console.WriteLine(x);

Andrei Ignat weekly software news(mostly .NET)

* indicates required

Please select all the ways you would like to hear from me:

You can unsubscribe at any time by clicking the link in the footer of our emails. For information about our privacy practices, please visit our website.

We use Mailchimp as our marketing platform. By clicking below to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing. Learn more about Mailchimp's privacy practices here.