Input Formats

Performace Monitor Log Time format in CSV file

Anonymous — Tue, 21 Aug 2007 06:21:17 GMT

Hi all,

First time posting to the forum but hoping you can help me out. I have some performance monitor logs saved to CSV format. However I am having a difficulties querying the file because of the time field, it shows up as "(PDH-CSV 4.0) (Eastern Daylight Time)(240)" in the header row of the CSV file. Running the command LogParser -h -i:CSV myfile.csv -headerRow:on I have determined that the field is a String. I can select the string and out put it to standard out. However if I try to put into a chart with anouther field it will give the error "Column (PDH-CSV 4.0) (Eastern Daylight Time)(240) is not an INTEGER nor a REAL". I try to format using TO_TIMESTAMP however that does not work so well either. Or maybe I just need a little help using the TO_TIMESTAMP FUNCTION.

ALl your help is apprecitated.
Bought the book, but details about querying performance logs a little short.

Chipao78

AD show active users

rublin — Wed, 18 Nov 2009 10:08:15 GMT

I have query "SELECT ipphone, telephoneNumber, mobile, mail, sAMAccountName INTO AD FROM LDAP://domain.name WHERE ipphone IS NOT NULL", but result - all users account. I need only active users (not disable).

Error: Log row too long

Anonymous — Tue, 21 Aug 2007 06:21:11 GMT

Hi all, I got the following error:

Error: Log row too long

LogFile "C:\Program Files\Log Parser 2.2\perfmonlog.csv", Row number 1

when I want to parse my perfmon log. Here is my query:

LogParser.exe "select * from perfmonlog.csv" -i:TSV

Is there a limitation of rows being processed by Logparser? Is there any workaround?

AD + Number of times a user has reset his password

hg363 — Tue, 17 Nov 2009 14:47:35 GMT

Good afternoon to all forum members. Please could some one provide me with the correct SQL string that could be used with LP to interrogate Active Directory and find out how many times a user has changed his or her passowrd, thankyou Mr Ghelani UK

'Filename' is keyword in SELECT statment; how to escape it?

GuyHoozdis — Sun, 08 Nov 2009 23:10:45 GMT

I have a column named "Filename" (and one called "Timestamp" too), but these seem to be keywords for Logparser.

$> LogParser.exe "SELECT Timestamp, Filename FROM datafile.tab" -i:TSV -iSeparator:tab -fixedSep:ON

The above query does not give me the results I expect. Is there a way for me to indicate that I mean the column in the file named "Timestamp" and "Filename" instead? Of course I could change my column names to something like "myFilename", that did work, but I have to imagine there is another way.

Amazing tool! I can't wait to dig into it more.

Parse Errors in NSCA format -- can I do anything?

msdn_dave — Tue, 10 Nov 2009 18:01:17 GMT

The following line causes parse errors ("Log row terminates unexpectedly.") when the input format is NCSA. (IPs altered to protect the guilty.)

299.99.45.146 - - [25/Oct/2009:01:39:44 -0700] "GET /images/expert/spacer.gif\" HTTP/1.1" 404 11124 "http://www.website.com/lms/agentDashboard.page" "Akamai Edge"

I'm pretty sure the problem is the forward slash after spacer.gif.

I'm assuming there is nothing I can do, short of pre-parsing and editing the error out, right?

Also, I had to go to quite a length to discover the problem. I used the -e:9999 tag, but that simply give me line numbers (in some very large files, hard to deal with.) Is there a way to actually output the troublesome lines in the future to STDERR somehow?

Thanks,

Spyware

hg363 — Wed, 28 Oct 2009 16:12:09 GMT

Hi to all forum members. This question was inspired by Trojan.Spambot. Here is a description of the Trojan.

Trojan.SpamBot is a Trojan that sends out mass spam e-mails, consuming and congesting network resources.

This Trojan was detected on a PC on our network and an anti spy ware application informed us that the Trojan was detected in a file called 70.tmp.

My question to the forum is as follows.

Is there any way or method where one could open such a file 70.tmp take a snap shot of its contents and then pipe this into Log Parser, and then get Log Parser to search another suspected remote PC to find evidence of a similar infection?

Convert LastLogon output to standard date and time

hg363 — Tue, 27 Oct 2009 19:22:31 GMT

Hi again to all forum members. Please can some one kindly assist me with this one.

Having run this query

string_is = " -i:ADS -objClass:user -o:DATAGRID " & """ SELECT cn, lastLogon FROM 'Domain Name' where sAMAccountName='HG363'"""

The output is as follows

Ghelani Hinesh 129010954238990075

How would one then go on and convert this to a normal date such as dd/mm/yyyy + the relevant time?

Kind regards HG363 London, UK

Prining

hg363 — Thu, 22 Oct 2009 21:50:45 GMT

Hi to all forum members.

Is there an SQL script in existance or can one be configured that when executed with Log Parser would show which printers a client printed to from a MS Windows XP Pro PC?

And if this is possible can the script then be inflated to show what dates the print jobs were sent to the printers and if possible the print job name and how many pages?

If some one could kindly show me how this can be done, it would be most helpful, regards HG363 UK London

List all files

hg363 — Mon, 19 Oct 2009 13:32:42 GMT

Hi I have this script which I slightly adapted from MS.

Set objLogParser = CreateObject("MSUtil.LogQuery")
Set objInputFormat = CreateObject("MSUtil.LogQuery.FileSystemInputFormat")
objInputFormat.Recurse = 0

Set objOutputFormat = CreateObject("MSUtil.LogQuery.DataGridOutputFormat")

strQuery = "SELECT Name, CreationTime FROM '\\192.168.204.220\c$\Documents and Settings\gw310\Favorites\*.*' " & "WHERE NOT Attributes LIKE '%D%' ORDER BY CreationTime"
objLogParser.ExecuteBatch strQuery, objInputFormat, objOutputFormat

====================================================================

It does work, however it does not list the files within folders that have been created within the FAVORITES folder.

So essentially what I need is a solution that will list all the files even if they are within a sub-folder which sits within the FAVORITES folder

Can some one please show me how this can be achieved, regards HG363

Extract TO: and FROM: with LogParser from SMTP Logs

FITEC — Wed, 14 Oct 2009 10:08:30 GMT

our goal is to get a CSV which contains all email-addresses sent/received from a specific exchange mailbox. can this be accomplished with LogParser?

Event logs Parsing issue

mritorto — Wed, 07 Oct 2009 13:03:44 GMT

Guys,

Here is my issue I have a batch job that runs log parser to collect the event logs from my servers and dumps the info into an excel spreadsheet.

Some times it works perfectly and some times it does not. The record titles like computername or timegenerated some times show up in the spreadsheet and other times it does not not.

Any idea why. I do have other batch files that run thru out the day looking the servers for specfic event log error messages like account lockouts but the one listed below only runs once per day.

I even run it from a different machine and I get the same results

Any help would be appreciated.

for /f %%i in (servers.txt) do logparser "select distinct timegenerated,EventID,EventTypeName,Strings,ComputerName,Message into eventlogs.csv from \\%%i\application where eventtypename like 'error event' and timegenerated >= to_localtime (sub(system_timestamp(), timestamp ('23','hh') ) ) order by timegenerated " -i:evt -o:csv -filemode:0

* in LIKE clause

desperate_user — Thu, 01 Oct 2009 12:18:03 GMT

Hello,

we have following entries (cs(Referer)) in our logfile:

http://xxxxx.yyyy.zz/corpnews/news1eng.nsf/*00001*/..... which have to be counted.

How can I handle the * inside a LIKE clause? %* doesn't work.

Unfortunately it's not possible to use /%00001%/ ... because there are other cs(Referer) which

will be (e.g. 000011 ...) also counted then.

Thank you very much.

regards

Andreas

P.S.: please excuse my clumsy english.

no recurse option for w3c log files?

massel — Wed, 30 Sep 2009 15:37:46 GMT

Am I correct in understanding that the -recurse option is only available for certain types of input formats (W3C NOT being one of them)? I have W3C log files that are in multiple folders under one root folder. What is the easiest way to have LP go through the subfolders recursively to read the logs?

Thanks, Mike

how can i query netmon capture files to get processID

besmart — Fri, 18 Sep 2009 20:20:14 GMT

i use netmon version 3.3

when i run logparser -h -i:NETMON , i didn't find processId field

how can i query netmon capture files to get processID

invalid character was found in text content

Anonymous — Tue, 21 Aug 2007 06:21:33 GMT

I am working on a solution for archiving windows event logs using logparser 2.2. I decided to use XML as the file format and wrote a simple script to dump the security log to an XML file without issue. The problem comes when I try to use logparser to read the resulting XML file back in for display in a datagrid. It kicked out an error as follows:

Error: Error loading document "C:\Data\Scripts\logparser\Test.xml": An invalid character was found in text content.

It appears to be caused by the following message in a 680 Event:

Logon attempt by: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0 Logon account: userid Source Workstation: ITSNT139� Error Code: 0x0

I suspect the character at the end of ITSNT139 is the problem. I tried using URLESCAPE on the message and strings field, but this just seems to have problems with other records when I try to read the XML file back in. (Stops in the middle of processing one of the records where it should be doing a URLUNESCAPE on a strings field. It doesn't kick out any errors, but doesn't process the remaining event records, and displays the records processed up to that point.)

One obvious question you may be thinking: why is that character appended onto the computer name in the 680 event? I'm dealing with thousands of computers in the domain, so I may not be able to stop that from happening anyway. This is outside the scope of logparser, but if you have any ideas on this, I'd be willing to look into it further.

All that said, is using XML as a file format for archived windows event logs a bad idea? Is there another way to get around this? (Perhaps by configuring it to ignore bad characters in an XML file?)

Any advise would be appreciated, so thanks in advance.

Find Password Last Set

Anonymous — Tue, 21 Aug 2007 06:21:12 GMT

To get the date/time the password was last set, use the following sample.
logparser -i:ADS "SELECT cn, to_timestamp(add(mul(to_real(pwdlastset),.0000001),TO_REAL(TO_TIMESTAMP('1601, 01 01 00:00','yyyy, MM dd hh:mm')))) AS date_pwd_last_set FROM 'LDAP://example.com/cn=Users,dc=example,dc=com'" -objClass:user

Custom Log File unknown format

akentanaka — Thu, 11 Sep 2008 08:48:48 GMT

Hi,

I have no luck with the log that I'm trying to parse. I'm new to this world of log parsing and my initial thought to parse our custom log (below) is to use TSV but I can't seem to understand how EXTRACT_TOKEN works.

Below is a sample of our log file.

10 Sep 2008 03:09:46,571 [Current Time=Wed Sep 10 03:09:46 BST 2008] [Process Name=:Module1:Module-Utilities:Utilities1:MyAlerts] [Root Job Id=2ad6505aff2ebcfa:182fe42d:11c4132e476:-80002-1221012586509] [Job Id=2ad6505aff2ebcfa:182fe42d:11c4132e476:-80002-1221012586509]
An error occurred that triggered compensation:
Summary: Execution failed.

Caused by:
   Summary: no peer with a running instance of WorkFlowAnalyzer found
10 Sep 2008 03:09:46,571 [Current Time=Wed Sep 10 03:09:46 BST 2008] [Process Name=:Module1:Module-Utilities:Utilities1:MyAlerts:Module[3]:SubModule[2]:Utilities:MyModule[7]] [Root Job Id=2ad6505aff2ebcfa:182fe42d:11c4132e476:-80002-1221012586509] [Job Id=2ad6505aff2ebcfa:182fe42d:11c4132e476:-80002-1221012586509]
The process started.
10 Sep 2008 03:09:46,571 [Current Time=Wed Sep 10 03:09:46 BST 2008] [Process Name=:Module1:Module-Utilities:Utilities1:MyAlerts] [Root Job Id=2ad6505aff2ebcfa:182fe42d:11c4132e476:-80002-1221012586509] [Job Id=2ad6505aff2ebcfa:182fe42d:11c4132e476:-80002-1221012586509]
Adapter request
[adapter request=
<request-data>
<my-action>
    <fields>
      <field>Description</field>
      <field>Detailed_Description</field>
      <field>Reported Source</field>
      <field>Requester Full Name</field>
    </fields>
</my-action>
</request-data>]

It appears that it has 6 parts

1) The date part

2) [Current Time=

3) [Process Name=

4) [Root Job Id=

5) [Job Id=

6) Log message

How can I break the log into 6 columns? I might use Log Parser Lizard to analyze the log file.

Thanks for your help!

Regular Expression for EVT

satya_thakker — Thu, 10 Sep 2009 03:03:26 GMT

Hello,

I am trying to parse the Event Log for a regular expression, say a website address or the credit card number etc. I have the reg ex but can't figure out how to write the query to use it.

Thanks in advance

- Satya

CSV: Indicate Column Data Type

Victoria Popic — Fri, 28 Aug 2009 22:03:40 GMT

Hello,

Is there a way to tell the LogParser what the column types of a given input CSV file are?

The problem is the following: when values are missing in more than 10 rows (dtLines default), the column is assumed to be of type STRING (although it is an INTEGER column), which causes integer comparisons and other operations (e.g. averages) to fail with type mismatch errors. I would prefer not to increase the -dtLines parameter. Is there a way to fix this?

Thank you for your help.

Tough one: CANNOT figure out how to get the last field to format properly

maximusdm — Wed, 05 Aug 2009 15:01:49 GMT

Hi all. please help!

Everything works perfect except for the last field "ExceptionDetails" which contains various lines in the log. This is my command line:

logparser -i:TSV -iSeparator:"|" -headerRow:OFF -iHeaderFile:"c:\Header.tsv" -nSkipLines:2 -iTsFormat:"yyyy-MM-dd?hh:mm:ss" -o:CSV "select UTCtime,VHOtime,EventID,EventType,ComponentDoingTheLogging,
ComponentCausingTheLogging,ProcessID,ThreadID,Message,ExceptionDetails INTO C:\a.csv FROM C:\Trace.log"

This is a sample of 1 line of my log:

2009:07:30 15:00:23:41|2009:07:30 11:00:23:41T-4|13609|Warning|Microsoft.Iptv.Server.Notification.DeliveryService.NDS.Unexpected exception when registering NDS|NotificationDeliveryService.exe||2688|6|The system encountered an unexpected exception when attempting to start the NDS|
Exception Details:
Type: SoapException
Message: Server was unable to process request. ---> The ConnectionString property has not been initialized.
Source: System.Web.Services
StackTrace: at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.EndInvoke(IAsyncResult asyncResult)
at Microsoft.Iptv.Proxy.Notification.Controller.NotificationController.EndSetDeliveryService(IAsyncResult asyncResult)
at Microsoft.Iptv.Server.Notification.DeliveryService.NDS.FirstBootThread()^

So the goal is to fit everything (after "Exception Details:") in one cell. When I open the file in excel everything is messed up. If I output to a datagrid everything is messed up to.

Thank you for your help.

Cannot get TSV input format to work properly

maximusdm — Thu, 30 Jul 2009 20:16:25 GMT

logparser "select UTCtime,Localtime INTO C:\a.tsv FROM C:\temp\TraceSink.log" -i:TSV -iSeparator:"|"
-iHeaderFile:"c:\temp\TraceLogHeader.tsv" -nSkipLines:2 -iTsFormat:"yyyy-MM-dd?hh:mm:ss"

Error: SELECT clause: Syntax Error: unknown field 'UTCtime'.
The closest match for input format 'TSV' is 'UTCtime,Localtime,EventID,EventType,ComponentDoingTheLogging,ComponentCausingTheLogging,
UnknownField,ProcessID,ThreadID,Message,ExceptionDetails'

c:\temp\TraceLogHeader.tsv:
------------------------------------------
UTCtime,Localtime,EventID,EventType,ComponentDoingTheLogging,ComponentCausingTheLogging,UnknownField,ProcessID,ThreadID,Message,ExceptionDetails

If I use the same command line above to read the same altered TraceSink.log file (using "," commas as delimiters instead and using :CSV as input format) the results are successful.
However, the original file: "TraceSink.log" uses "|" as a delimiter, hence my choice of using TSV format which gives me the "iSeparator" option.

Any ideas?

Thank you.

Break string

hg363 — Thu, 09 Jul 2009 19:46:01 GMT

Hi to all forum members. Can some one please assist me. I am using LP to interrogate a text file and pull out TEXTLINE data. Here is an example

204 00:13:21:5f:04:d4 learned 0 bridging 1/9

Is there any clever way of telling LP that when it finds this data to break it down like this

204

00:13:21:5f:04:d4

learned

bridging

1/9

and if there is please can some one give me an example and demonstrate how this can be done, need this last bit to complete a project. Regards hg363 UK

advice on this format please

theanykey — Thu, 09 Jul 2009 19:27:36 GMT

I have received access logs from our web host and they use Sun Java System Web Server. I would like to use Logparser on these files, but the format does not appear to be directly supported. Any advice on a plugin to use with Log Parser or how to reformat these to fit a standard that Logparser supports? Thanks for all advice.

column headings:

format=%Ses->client.ip% - %Req->vars.auth-user% [%SYSDATE%] "%Req->reqpb.clf-request%" %Req->srvhdrs.clf-status% %Req->srvhdrs.content-length% "%Req->headers.referer%" "%Req->headers.user-agent%"

one row:

98.204.26.178 - - [02/Jul/2009:17:41:27 -0500] "GET /css/main.css HTTP/1.1" 200 1151 "http://www.domain.com/index.html" "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Trident/4.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648; InfoPath.1; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; MS-RTC LM 8)"

Remove lines of data

hg363 — Wed, 08 Jul 2009 15:28:08 GMT

Hi please can some one with very strong SQL skills in Log Parser help me find a solution to this problem.

I have a text file with data that look like this

Vlan      Mac Address          Type       Protocol    Operation    Interface
------+-------------------+--------------+-----------+------------+-----------
   220   00:1f:29:db:37:af     learned              0     bridging      1/2
   220   00:22:64:a9:27:bc     learned              0     bridging      1/5
   220   00:11:43:c2:ef:02     learned              0     bridging      1/6
     1   00:20:da:fd:5b:60     learned          10806     bridging      1/7
   220   00:0d:56:1e:84:b9     learned              0     bridging      1/7
   220   00:13:21:5e:8a:81     learned              0     bridging      1/7
   220   00:18:71:6d:09:24     learned              0     bridging      1/7
   220   00:19:bb:4d:7f:71     learned              0     bridging      1/7
   220   00:19:bb:5f:68:bf     learned              0     bridging      1/7
   220   00:1a:4b:11:0c:f3     learned          10806     bridging      1/7
   220   00:1f:29:1b:db:f9     learned              0     bridging      1/7
   220   00:22:64:aa:16:58     learned              0     bridging      1/7
   220   00:c0:b7:6c:4f:9e     learned              0     bridging      1/7
   220   00:50:60:02:cf:f9     learned          10806     bridging      1/8
   220   00:60:9f:92:9d:a9     learned              0     bridging      1/8
   220   00:c0:b7:cb:0f:2e     learned          10806     bridging      1/8
     1   00:d0:95:33:44:40     learned          10806     bridging      1/9
     1   00:d0:95:53:6c:80     learned          10806     bridging      1/9
   204   00:00:74:9b:32:61     learned              0     bridging      1/9
   204   00:08:02:5e:12:02     learned              0     bridging      1/9
   204   00:13:21:5f:04:d4     learned              0     bridging      1/9
   204   00:14:c2:57:b7:d4     learned              0     bridging      1/9
   204   00:17:a4:15:c5:d6     learned              0     bridging      1/9
   204   00:19:bb:5c:c1:2e     learned              0     bridging      1/9
   204   00:1e:0b:3f:49:50     learned              0     bridging      1/9
   204   00:22:64:a9:28:3e     learned              0     bridging      1/9
   204   00:d0:95:33:44:43     learned          10806     bridging      1/9
   204   00:d0:95:53:6c:83     learned          10806     bridging      1/9
   220   00:00:74:a9:87:5a     learned              0     bridging      1/9
   220   00:19:bb:5f:6a:69     learned              0     bridging      1/9
   220   00:1c:c4:63:13:0e     learned              0     bridging      1/9
   220   00:1c:c4:63:18:d3     learned              0     bridging      1/9
   220   00:1c:c4:64:20:b9     learned              0     bridging      1/9
   220   00:22:64:a8:6f:1a     learned              0     bridging      1/9
   220   00:c0:b7:cb:0e:d4     learned              0     bridging      1/9

What I need is LP to interrogate this text file and remove all lines of data where an occurance under the (interface heading) appears more then once.

In other words all lines of data that have 1/7 + 1/8 + 1/9 will be removed