No Rain!

Finally a weekend with no rain so far!!

Microsoft LogParser 2.2 Tutorial III

Click Here to see Part II.

In my next post about logparser, I wanted to discuss some usage in a few other formats as well as a few output options. The best way to learn this is by example, so here goes:

Query the Registry

--select from registry
LogParser -i:REG -o:datagrid "SELECT value, valuename from HKLM\Software\Microsoft WHERE ValueName='ProductID'"

Query AD
I would usually use C# or Powershell for this, but it can be done with logparser. Some complex examples can be found here.

Chart Output

--select unique visits and chart it
LogParser -i:IISW3C -o:chart -chartType:Line "SELECT date, count(c-ip) AS visitors INTO myChart.gif from c:\MyIISlog.log GROUP BY date"

--select asp errors by hour and chart it
LogParser -i:IISW3C -o:chart -chartType:Line "SELECT s-ip, TO_TIME(QUANTIZE(TO_TIMESTAMP(date, time),3600)) as timestamp, COUNT(*) AS Error_Frequency FROM c:\MyIISLog.log WHERE sc-status >= 400
AND (EXTRACT_EXTENSION(TO_LOWERCASE(cs-uri-stem)) IN ('asp';'aspx';'ashx';'ascx'))
GROUP BY s-ip, timestamp ORDER BY timestamp ASC"

--select percentage processing time by extension and chart it
LogParser -i:IISW3C -o:chart -chartType:Line "SELECT s-ip, EXTRACT_EXTENSION(cs-uri-stem) AS Extension, TO_INT(MUL(PROPSUM(time-taken),100.0)) AS Processing_Time
FROM c:\MyIISLog.log GROUP BY s-ip, Extension ORDER BY Processing_Time DESC"

Dump into SQL Server

--dump into sql server
LogParser -i:IISW3C "SELECT * INTO WebLogs from c:\MyIIS.log" -server:MyServer\SQLExpress -database:IISLogs -driver:"SQL Server" -createTable:ON

One note for sql server is that you can create the table manually and then import just the fields you want or all of them. If you use createTable:ON then it will make a new table for you in the db (make sure you have rights). The default column length though is 255 and data might get truncated, so I usually create my own table first.

You now have a bunch of examples on input and forensics for logparser and output formats as well. I have just scratched the surface with input/output though.

Next week I'll go into some examples on how to call logparser from code.

Notepad2 for Windows 7

I have been using Notepad2 for a long time as my default notepad. I just updated one of my machines to Windows 7 64 bit and I needed to set notepad2 up. The good news is that a 64 bit version is available but the bad news is that installation is not the same as vista for making it the default. Here is a batch script I found to get it to work.

@echo off
TITLE Notepad2 Install Script for Complete Windows Vista and 7 Notepad Replacement
echo.
echo Notepad2 Install Script for Complete Windows Vista and 7 Notepad Replacement
echo Version 1.2
echo.
echo (c) My Digital Life (www.mydigitallife.info)
echo.
echo.
echo.
echo Confirm to apply? (Press Ctrl-C and answer Y to terminate)
pause
echo.
echo.

if exist %Systemroot%\notepad.original.exe goto exist_notepad2_already
if exist %Systemroot%\System32\notepad.original.exe goto exist_notepad2_already
takeown /f %Systemroot%\notepad.exe
takeown /f %Systemroot%\System32\notepad.exe
icacls %Systemroot%\notepad.exe /grant "%username%":f
icacls %Systemroot%\System32\notepad.exe /grant "%username%":f
IF EXIST %SYSTEMROOT%\SysWOW64 (bcdedit.exe -set loadoptions "DDISABLE_INTEGRITY_CHECKS")
copy %Systemroot%\notepad.exe %Systemroot%\notepad.original.exe
copy %Systemroot%\System32\notepad.exe %Systemroot%\System32\notepad.original.exe
echo.
echo Original notepad.exe has been renamed to "notepad.original.exe" in its original folder.
echo.
copy %~dp0\notepad2.exe %Systemroot%\notepad.exe /y
copy %~dp0\notepad2.exe %systemroot%\System32\notepad.exe /y
echo.
echo Notepad2 installation is completed.
echo If no error occurred, Notepad2 will now replace all Notepad functions.
echo.
pause
exit

:exist_notepad2_already
echo.
echo INSTALLED NOTEPAD2 ALREADY!.
echo.
pause
exit

Microsoft LogParser 2.2 Tutorial II

Click Here to see Part I.

In my next post about logparser, I wanted to discuss the basic usage in a few other formats. The best way to learn this is by example, so here goes:

Event Log Win2k3 and Earlier

--From system event log get the following 4 fields
LogParser -i:EVT -o:datagrid "SELECT TimeGenerated, EventTypeName, SourceName, Message FROM System"

--From system event log get the following 4 fields where the source is PRINT
LogParser -i:EVT -o:datagrid "SELECT TimeGenerated, EventTypeName, SourceName, Message FROM System WHERE SourceName = 'print'"

--Push into .csv file all items where message is like exception or eventid = 666
--ResolveSIDs to show me the real windows login name
LogParser -i:EVT -resolveSIDs:ON "SELECT TimeGenerated, SID, SourceName, Message FROM System INTO E:\File.csv WHERE Message LIKE '%Exception%' OR EventID = 666"

--AppPool Recycle
Select top 25 TO_STRING(TimeGenerated, 'hh:mm:ss') as dateTime, Message
   from \\MyServer\System
           where SourceName in ('W3SVC';'WAS') and
       EventID in (1117;1080;1079;1078;1077;1076;1011;1010;1009)
   ORDER BY dateTime DESC

Event Log Vista and Later
The logparser code is the same but the event logs in Windows Vista have to be saved or converted to the new Event Log file format ".evtx". The Windows Events Command Line Utility can be used to do this. At the command prompt:

wevtutil epl application.evt application.evtx /lf:true

This should now let you parse it with logparser.

Generic Log4Net

--A basic default log4net file
select extract_token(field1,0,',') as DateTime,
extract_token(field1, -1, ' ') as Module, 
extract_token(field1,2,' ') as Type,
field2 as Comment
from c:\MyLog4NetFile.log
where field1 like '2009%'

FileMon

--Show me Access Denies from filemon log in console screen
LOGPARSER "Select Text from C:\Filemon.log where Text like '%Access Denied%'" -i:TEXTLINE -q:Off

NetMon

--min and max date from my netmon trace
logparser -i:NETMON "SELECT MIN(DateTime) as Start, MAX(DateTime) as End from mytrace.cap

--selext all and push to csv file from mytrace
logparser -i:NETMON -o:CSV "select * INTO Output.csv from mytrace.cap

Here are some basic examples using logparser against some formats. Look for more examples later today.

Microsoft LogParser 2.2 Tutorial I

One of my favorite tools that I use all the time is logparser. This tool is well known among many sys admins, but not as well known in the development world. To quote from Microsoft "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®." Basically, it is a tool that can query anything! Some things I have used it for are IIS, Log4Net, FileMon, Exchange, Event, and Akamai logs. I have also used it to create charts and CSV files from these files.

I wanted to start a tutorial that would give insight into this great tool and will show you how to use it. I will also show how you can call it from C# and powershell and some great libraries to use.

LogParser is a console based application but there are some people who created various UI's for it. You can write the sql directly or as I like to do it store it in a .sql file and call it like this:

LOGPARSER -i:IISW3C file:C:\MyLogParserFile.sql -o:DataGrid

There are tons of switches that can be added, and we will go through this in in further articles but note that -i stands for input. Although logparser can usually figure out the format, you should try and tell it what format the file is. In this case, I wanted it to know that the file is an IIS log file. The -o stands for output and I wanted it to show the output in a datagrid. If you leave the -o out it will display it in the default format which is in the console. You can try logparser -h and it will give you some more details.

I wanted to start off by showing some basic queries against IIS logs.

CountClients.sql

select count(*) from c:\MyIIS.log

Top10LongestTimeTakenPagesOnAverage.sql

SELECT
top 10 TO_LOWERCASE(cs-uri-stem),
cs-uri-query,
TO_LOCALTIME(TO_TIMESTAMP(date, time)) as timestamp,
sc-bytes,
time-taken

FROM
c:\MyIIS.Log

ORDER BY
time-taken DESC

Top25MostPopularPages.sql

SELECT
top 25 cs-uri-stem,
count(cs-uri-stem) As Hits
FROM c:\MyIIS.log

GROUP BY
cs-uri-stem

ORDER BY
 count(cs-uri-stem) DESC

CountUniqueClients.sql

SELECT count(distinct c-ip) from c:\MyIIS.log

Please let me know if you had any specifics you would like me to discuss.

Powershell Modules

.ps1 and .psm1...here is a great explanation on the subject.

Convert path to Dos 8.3 notation using C#

I had an issue working with LogParser and spaces in my folders when generating output for charts. Although the escape character worked in the logparser console, it did not work using the API directly. A solution is to use the short path name and this solution is not really tied to logparser and really can be used anywhere where you have a problem like this. The code looks like this:

 using System;
 using System.Text;
 using System.IO;
 using System.Runtime.InteropServices;

 public class PathHelper
 {
     [DllImport("kernel32.dll", SetLastError = true, CharSet = CharSet.Auto)]
     private static extern int GetShortPathName(
         [MarshalAs(UnmanagedType.LPTStr)] string path,
         [MarshalAs(UnmanagedType.LPTStr)] StringBuilder shortPath,
         int shortPathLength);

     public static string GetShortPathName(string path)
     {
         StringBuilder shortPath = new StringBuilder(500);
         if(0 == GetShortPathName(path, shortPath, shortPath.Capacity))
         {
             if (Marshal.GetLastWin32Error() == 2)
             {
                 throw new Exception("Since the file/folder doesn't exist yet, the system can't generate short name.");
             }
             else
             {
                 throw new Exception("GetShortPathName Win32 error is " + Marshal.GetLastWin32Error());
             }
         }
         return shortPath.ToString();
     }
 }

Then just call this method and send it your path and you should get the shorter DOS format. This is a really nice trick that has helped me on many occasions.

Powershell and Subversion

This is a neat trick on how to have post-commit email send to you via Powershell.