Michel Klomp

Logparser in powershell

One of the few examples how to use logparser in powershell is from the Microsoft.com Operations blog.

This script is a good base to create more advanced logparser scripts:

$myQuery = new-object -com MSUtil.LogQuery

$szQuery = “Select top 10 * from r:\ex07011210.log”;

$recordSet = $myQuery.Execute($szQuery)

for(; !$recordSet.atEnd(); $recordSet.moveNext())

{

            $record=$recordSet.getRecord();

            write-host ($record.GetValue(0) + “,”+ $record.GetValue(1));

}

$recordSet.Close();

Logparser input formats

The previous example uses the default logparser object, you can extent this with the logparser input formats. with this formats get information from the event-log, different types of logfiles, the Active Directory, the registry and XML files.
Here are the different ProgId’s you can use.

Input Format ProgId
ADS MSUtil.LogQuery.ADSInputFormat
BIN MSUtil.LogQuery.IISBINInputFormat
CSV MSUtil.LogQuery.CSVInputFormat
ETW MSUtil.LogQuery.ETWInputFormat
EVT MSUtil.LogQuery.EventLogInputFormat
FS MSUtil.LogQuery.FileSystemInputFormat
HTTPERR MSUtil.LogQuery.HttpErrorInputFormat
IIS MSUtil.LogQuery.IISIISInputFormat
IISODBC MSUtil.LogQuery.IISODBCInputFormat
IISW3C MSUtil.LogQuery.IISW3CInputFormat
NCSA MSUtil.LogQuery.IISNCSAInputFormat
NETMON MSUtil.LogQuery.NetMonInputFormat
REG MSUtil.LogQuery.RegistryInputFormat
TEXTLINE MSUtil.LogQuery.TextLineInputFormat
TEXTWORD MSUtil.LogQuery.TextWordInputFormat
TSV MSUtil.LogQuery.TSVInputFormat
URLSCAN MSUtil.LogQuery.URLScanLogInputFormat
W3C MSUtil.LogQuery.W3CInputFormat
XML MSUtil.LogQuery.XMLInputFormat
Using logparser to parse IIS logs

if you use the IISW3CinputFormat you can use the field names instead of de row number to get the information from an IIS logfile, it also skips the comment rows in the logfile.

$ObjLogparser = new-object -com MSUtil.LogQuery
$objInputFormat = new-object -com MSUtil.LogQuery.IISW3CInputFormat

$Query = “Select top 10 * from c:\temp\hb\ex071002.log”;

$recordSet = $ObjLogparser.Execute($Query, $objInputFormat)
for(; !$recordSet.atEnd(); $recordSet.moveNext())
{
    $record=$recordSet.getRecord();
    write-host ($record.GetValue(“s-ip”) + “,”+ $record.GetValue(“cs-uri-query”));
}
$recordSet.Close();

• Share This Post:
• 
• 
• 

Here is a small Powershell script for creating a two-way forest trust.

$localforest = [System.DirectoryServices.ActiveDirectory.Forest]::getCurrentForest()
$strRemoteForest = ‘domain.local’
$strRemoteUser = ‘administrator’
$strRemotePassword = ‘P@ssw0rd’
$remoteContext = New-Object System.DirectoryServices.ActiveDirectory.DirectoryContext(‘Forest’, $strRemoteForest,$strRemoteUser,$strRemotePassword)
$remoteForest = [System.DirectoryServices.ActiveDirectory.Forest]::getForest($remoteContext)
$localForest.CreateTrustRelationship($remoteForest,’Bidirectional’)

• Share This Post:
• 
• 
• 

the following snippet can be used to add more than one user to the grantsendonbehalfto property with Powershell and the Exchange Management Shell

get-mailbox dummy |set-mailbox -grantsendonbehalfto “testuser3″

$a = get-mailbox testuser2 | select-object grantsendonbehalfto
$b = get-mailbox dummy| select-object grantsendonbehalfto

$a.grantsendonbehalfto += $b.grantsendonbehalfto[0]

get-mailbox testuser2 |set-mailbox -grantsendonbehalfto $($a.grantsendonbehalfto)

• Share This Post:
• 
• 
• 

Here is a small vbscript that uses logparser to get the most used ISA rules. This can be useful if you have multiple allow or deny rules and the order of this rules is not important. In this situation you want to place the most used rule on top.

 

There are some requirements for this script. You need to install logparser on a client or server. And you need to use the w3c logfile format on ISA server.

 

 

Set objLogParser = CreateObject("MSUtil.LogQuery")

Set objInputFormat = CreateObject("MSUtil.LogQuery.W3CInputFormat")

 

strQuery = "SELECT DISTINCT rule, count(rule) as Hits FROM '"

strQuery = strQuery & "\\stisa01\D$\log\isa\ISALOG_20061219_WEB_000.w3c"

strQuery = strQuery & "' GROUP BY rule ORDER BY Hits DESC"

 

Set objRecordSet = objLogParser.Execute(strQuery, objInputFormat)

Do While Not objRecordSet.AtEnd

          Set objRecord = objRecordSet.GetRecord

         WScript.echo objRecord.GetValue("rule") & vbTab & objRecord.GetValue("Hits")

          objRecordSet.MoveNext

Loop

• Share This Post:
• 
• 
• 

If you run a MSH script you probably get an error. The reason for this is that the standard execution policy for MSH is set to restricted.
This is the secure by default mode from MSH. To configure MSH to run scripts you need to change a registry key.
HKEY_LOCAL_MACHINE\ SOFTWARE\Microsoft\Msh\Microsoft.Management.Automation.msh ExecutionPolicy
the default value of this key is restricted, you need to change this to RemoteSigned

MSH recognizes changes to your execution policy immediately.

If you download MSH and unpack the zip file, you find a text file. about_signing.help.txt in this text file you find a more detailed description of this.

There is only one thing. Don’t use unrestricted, RemoteSigned is “unrestricted” enough. You only need unrestricted when you want outlook or internet explorer run unsigned scripts directly from the internet.

• Share This Post:
• 
• 
• 

Introduction.

 

A few weeks ago I read an article from securityfocus (http://www.securityfocus.com/infocus/1721), how you can use MRTG as an Intrusion detection tool. The theory behind the article is that most attacks create some type of anomaly in the way the system is used, high amount of traffic or a high rate of errors. And with MRTG you can make this anomaly visual.

And the human brain has no problem in detecting an anomaly in a visual representation.

 

At the end of the article they give logparser as a source of information for MRTG. End that was the moment they got my attention, unfortunately that was when the end of the article. Another problem was that I has no intention to install MRTG, I only want to make the graphs

 

And here starts the real fun. MRTG is build around the RRDtool from Tobias Oetiker. And you can use the RRDtool perfectly without the rest of MRTG.

 

Logparser.

 

Logparser is a great free tool from Microsoft. It is written by Gabriele Giuseppini a Software Design Engineer from the test department. The first version of logparser was an internal testing tool inside Microsoft. Version 2 was made publicly available at the website, version 2.1 was a  part of the IIS 6 resource kit tools and version 2.2 was made available January 2005.

 

Here is a brief introduction how logparser works. Logparser need three things, an input format, an output format and a sort of SQL query. The SQL query is a dialect of SQL.

 

On technet there are to great articles about the logparser. One is written by Gabriele Giuseppini himself (http://www.microsoft.com/technet/community/columns/profwin/pw0505.mspx ) and one written by the Scripting Guys (http://www.microsoft.com/technet/community/columns/scripts/sg0105.mspx ). The article from the scripting guys shows you how to use the logparser directly in a script with a com object.

 

If you download (http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en ) the logparser and install it. You can find a portable help file in the application directory. This help file give you the parameters of all the properties of the logparser.

 

There is also an unofficial logparser website (http://www.logparser.com/ ) from Mike Gunderloy. And there is a book dedicated to the logparser tool, written by Gabriele Giuseppini and Mark Burnett.

 

RRDtool.

 

What is the RRDtool? The RRDtool or Round Robin Database tool is a tool that can store date in a database and create graphs with it. And the realy great thing is that the database is not growing. It keeps almost the same size as when you created it.

 

The RRD tool is created by Tobias Oetiker as a part of MRTG to monitor the internet connection of a university. But you can use almost every source as long as the input is a number.

 

On the RRD website (http://people.ee.ethz.ch/~oetiker/webtools/rrdtool/index.en.html ) there are some really good tutorials, and it is recommended to read them before you use the RRDtool. From this website you can also download the RRDtool, the only problem is that you need to compile it. If you download the MRTGbundle (http://www.openinnovations.com/mrtgbundle.html ) there is a complete version of the RRDtool in the packet. If you unpack the MRTGbundle, you can copy the RRDtool directory to your scripting directory or your application directory and start using it.

 

 

Create Database.

 

Before you can use the RRDtool you need to create the database. In this tutorial (http://people.ee.ethz.ch/~oetiker/webtools/rrdtool/tut/rrdtutorial.en.html ) they show you how and why, and this page(http://people.ee.ethz.ch/~oetiker/webtools/rrdtool/doc/rrdcreate.en.html ) contains all the parameters.

 

I used a script to do that.

‘#start script.

Set WshShell = WScript.CreateObject("WScript.Shell")

 

strCMD = ".\bin\rrdtool.exe create Eservicing.rrd"

strCMD = strCMD & " --start N "

strCMD = strCMD & " -s 300"

strCMD = strCMD & " DS:Hits:GAUGE:600:0:2000000"

strCMD = strCMD & " DS:Error400:GAUGE:600:0:2000000"

strCMD = strCMD & " DS:Error500:GAUGE:600:0:2000000"

strCMD = strCMD & " RRA:AVERAGE:0.5:1:288"

strCMD = strCMD & " RRA:AVERAGE:0.5:2:2016"

strCMD = strCMD & " RRA:AVERAGE:0.5:4:2232"

strCMD = strCMD & " RRA:AVERAGE:0.5:12:8760"

 

WshShell.Run strCMD

‘#end script.

 

I explain this script line by line.

Set WshShell = WScript.CreateObject("WScript.Shell")

This line create a shell object you need to run the RRDtool .

In the next 10 lines I create the command line that I run in the last line.

strCMD = ".\bin\rrdtool.exe create Eservicing.rrd"

this starts the RRDtool with the create function and give the name of the database.

strCMD = strCMD & " --start N "

--start set the start time of the database and N is the current time. The RRDtool works with Unixtime, this are the seconds from 1 January 1970.

strCMD = strCMD & " -s 300"

-s is the seconds between a database update.

strCMD = strCMD & " DS:Hits:GAUGE:600:0:2000000"

strCMD = strCMD & " DS:Error400:GAUGE:600:0:2000000"

strCMD = strCMD & " DS:Error500:GAUGE:600:0:2000000"

with this three lines I create three data sources. DS stands for data source, Hits is the name of the data source GAUGE is one of the four type’s of data sources, 600 are the seconds between the records if there is no input after 600 the value is NULL, 0 is the minimum value of the record and 200000 is the maximum value.

strCMD = strCMD & " RRA:AVERAGE:0.5:1:288"

strCMD = strCMD & " RRA:AVERAGE:0.5:2:2016"

strCMD = strCMD & " RRA:AVERAGE:0.5:4:2232"

strCMD = strCMD & " RRA:AVERAGE:0.5:12:8760"

this four lines create four Round Robin Archives. RRA stands for Round Robin Archive, AVERAGE is one of the four consolidation functions, 0.5 is the consolidation interval, 1 is the number of data sources that are consolidate in one record in the Round Robin Archive. If every 600 seconds a DS is created and the value is 4 instead of 1 every 2400 seconds there will be a record add to the archive, the last value is the number of records the archive contains.

The first line create a Round Robin Archive with a consolidation interval of 0.5. every data source gets a record in the archive and the archive is 288 records long.

WshShell.Run strCMD

And with this line the command is executed.

 

 

Update database.

 

With the next script I use logparser to evaluate the logfile from a IIS server. I run this script every 5 minutes. The results are written to the RRD database.

 

‘#start script

Const ForReading = 1, ForWriting = 2, ForAppending = 8

'-------------------------------------------------------------------------

LogDir = "\\server\d$\log\sys\www\site\W3SVC1\"

Set WSHShell = CreateObject("Wscript.Shell")

Set fso = CreateObject("Scripting.FileSystemObject")

Set objLogParser = CreateObject("MSUtil.LogQuery")

Set objDictIISlogslist = CreateObject("Scripting.Dictionary")

 

Dim strDate

Dim count

Error400 = 0

Error500 = 0

'-------------------------------------------------------------------------

Main

'-------------------------------------------------------------------------

'-------------------------------------------------------------------------

Sub Main

            Call MakeStrDate

            Call GetUniqueHits

            Call GetStatus

            Call UpdateRRD

End Sub

'-------------------------------------------------------------------------

' ----------------------------------------------------------------------------

Sub MakeStrDate

            strMonth = Month(Now)

            If Len(strMonth) = 1 Then

                        strMonth = "0" & CStr(strMonth)

            End If

            strDay = Day(Now)

            If Len(strDay) = 1 Then

                        strDay = "0" & CStr(strDay)

            End If

            strYear =Right(Year(Now),2)

            strDate = strYear & strMonth & strDay

End Sub

' ----------------------------------------------------------------------------

'-------------------------------------------------------------------------

Sub GetUniqueHits

            Set objInputFormat = CreateObject("MSUtil.LogQuery.IISW3CInputFormat")

            objInputFormat.recurse = -1

            objInputFormat.iCheckPoint = strDate & ".lpc"

            strQuery = "SELECT count(*) as UniqueHits FROM '" & _

LogDir & "\ex" & strDate & ".log'"

            Set objRecordSet = objLogParser.Execute(strQuery, objInputFormat)

            Do While Not objRecordSet.AtEnd

               Set objRecord = objRecordSet.GetRecord

               count = objRecord.GetValue("UniqueHits")

               objRecordSet.MoveNext

            Loop

End Sub

'-------------------------------------------------------------------------

'-------------------------------------------------------------------------

Sub GetStatus

            Set objInputFormat = CreateObject("MSUtil.LogQuery.IISW3CInputFormat")

            objInputFormat.recurse = -1

            objInputFormat.iCheckPoint = strDate & "Error.lpc"

            strQuery = "SELECT sc-status , COUNT(*) as Hits FROM '" & LogDir & "\ex" & strDate & ".log' WHERE sc-status > 399 GROUP BY sc-status ORDER BY Hits DESC"

            Set objRecordSet = objLogParser.Execute(strQuery, objInputFormat)

            Do While Not objRecordSet.AtEnd

                        Set objRecord = objRecordSet.GetRecord

                        If  objRecord.GetValue("sc-status") > 399 And objRecord.GetValue("sc-status") < 500  Then

                                    Error400 = Error400 + objRecord.GetValue("Hits")

                        End If

                        If  objRecord.GetValue("sc-status") > 499 And objRecord.GetValue("sc-status") < 600  Then

                                    Error500 = Error500 + objRecord.GetValue("Hits")

                        End If

                        objRecordSet.MoveNext

            Loop

           

End Sub

'-------------------------------------------------------------------------

'-------------------------------------------------------------------------

Sub UpdateRRD

            strRun = ".\bin\rrdtool update Eservicing.rrd N:" & count & ":" & Error400 & ":" & Error500

             X = WshShell.Run(strRun,0,True)

End Sub

'-------------------------------------------------------------------------

‘#end script

 

I’m not going to explain this script line by line. It’s not a very difficult script. The procedures GetUniqueHits and Getstatus are using logparser. And UpdateRRD procedure uses the RRDtool to update the database.

 

If you need some help with this script feel free to send me an email or place your question in the comment.

 

Creating a Graphic with the RRDtool.

 

With the next script I create a graphic with the RRDtool.

 

Set WshShell = WScript.CreateObject("WScript.Shell")

 

strCMD = ".\bin\rrdtool graph .\graph\intranetNLweek.gif"

strCMD = strCMD & " --start N-1w --end N"

strCMD = strCMD & " --vertical-label " & Chr(34) & "Hits " & Chr(34)

strCMD = strCMD & " --title INTRANET"

strCMD = strCMD & " DEF:Xhits=.\database\intranetNL.rrd:Hits:AVERAGE"

strCMD = strCMD & " DEF:Xerror400=.\database\intranetNL.rrd:Error400:AVERAGE"

strCMD = strCMD & " DEF:Xerror500=.\database\intranetNL.rrd:Error500:AVERAGE"

strCMD = strCMD & " LINE2:Xhits#FF0000:" & Chr(34) & "Hits" & Chr(34)

strCMD = strCMD & " LINE2:Xerror400#00FF00:" & Chr(34) & "400 Errors" & Chr(34)

strCMD = strCMD & " LINE2:Xerror500#0000FF:" & Chr(34) & "500 Errors" & Chr(34)

 

WshShell.Run strCMD

 

There’re two important parts in this script, the DEF line this line defines the Data Sources you use. And the LINE2 defines a line in the graphic.

 

And this is the result.

 

 

• Share This Post:
• 
• 
• 

If you take the security settings in a GPO, and look closer to the audit policy. You will see 2 logon events. Audit account logon event and audit logon event.

What are the differences and why did Microsoft give them such a confusing name?

 

I was looking for some resources to answer this question, when google show me this page. http://blogs.msdn.com/ericfitz/archive/2005/08/04/447934.aspx a blog from the windows auditing team.

 

So here is there explanation for the bad naming.  The answer is actually pretty simple- we're bad at choosing names.  "Account Logon" isn't really about logon, it's about credential validation.

 

And these are the differences.

Audit Logon/Logoff generates events for the creation and destruction of logon sessions.  These events occur on the machine which was accessed.  In the case of an interactive logon, these would be generated on the machine which was logged on to.  In the case of network logon, for example, accessing a share, these events would be generated on the machine hosting the resource that was accessed.

Audit Account Logon generates events for credential validation. These events occur on the machine which is authoritative for the credentials.  For domain accounts, the domain controller is authoritative. For local accounts, the local machine is authoritative.  Since domain accounts are used much more frequently in enterprise environments than local accounts, most of the Account Logon events in a domain environment occur on the domain controllers which are authoritative for the domain accounts.  However, these events can occur on any machine, and may occur in conjunction with or on separate machines from logon/logoff events.

 

If you want to know more about auditing account logon events and logon events. This blog(http://blogs.msdn.com/ericfitz/default.aspx ) is a good place to start.

• Share This Post:
• 
• 
• 

Some command line tools ask for the UNIX time. If you’re using Jscript, this is no a problem. But VBscript doesn’t have a function that does this.

 

So here is some nice one-liner that gives the UNIX time

 

WScript.echo DateDiff("s", "12/31/1969 00:00:00", _
 DateSerial(Year(Now), Month(Now), Day(Now)) _
 + TimeSerial(Hour(Now), Minute(Now), Second(Now)))

• Share This Post:
• 
• 
• 

This are the two must have resources for every serious vbscripter.

The portable script center, this file containing most of the script samples of the “real” script center on TechNet. In a very usable CHM file.

http://www.microsoft.com/downloads/details.aspx?FamilyID=b4cb2678-dafb-4e30-b2da-b8814fe2da5a&DisplayLang=en

And the Windows Script 5.6 documentation, this is the standard help file for Windows Scripting Host. This is very helpful if you need the parameters for a WSH method.

http://www.microsoft.com/downloads/details.aspx?FamilyId=01592C48-207D-4BE1-8A76-1C4099D7BBB9&displaylang=en

• Share This Post:
• 
• 
• 

So this is the First post of my first blog :)

• Share This Post:
• 
• 
•