« Previous Next »

Thread: How to insert text into Log Parser output from query string

Last post 10-09-2008 3:03 AM by yellowdog.dave. 1 replies.

Average Rating Rate It (5)

RSS

Page 1 of 1 (2 items)

Sort Posts:

Shortcuts

Search | Active Posts | Unanswered Posts | View all users

  • 10-08-2008, 3:28 PM

    • andersce
    • Not Ranked
    • Joined on 10-08-2008, 7:09 PM
    • Posts 1

    How to insert text into Log Parser output from query string

    Reply Contact

    I'm trying to query a text file and build an output that includes inserted text in selected fields.

    Query that works:

    LogParser "SELECT Text INTO Status FROM C:\Download\LogParser\full.backup.log where Text like '%Error%' " -i:TEXTLINE -o:SQL -oConnString:"Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Download\LogParser\LogParser.mdb;

    With an output of:

    ORA-19625: file error
    'ORAO/S-Error: file not found

    I need the output to look like:

    SLREFW04,Oracle,#27OCT2008#,'ORA-19625: file error'
    SLREFW04,Oracle,#27OCT2008#,'ORAO/S-Error: file not found'

    How do I add the text values with the SELECT query? I'm running this query in vbscript so I would really like to assign variables and insert those into the query so I can loop through all my servers.Is there a way to go straight to sql server or would I have to create a csv file and then run log parser on that? Thanks in advance  

     
    Tags:

  • 10-09-2008, 3:03 AM In reply to

    • yellowdog.dave
    • Top 75 Contributor
    • Joined on 07-18-2008, 3:17 AM
    • Johannesburg, South Africa
    • Posts 84

    Re: How to insert text into Log Parser output from query string

    Reply Contact

    Hi Anders, 

    I made some presumptions around what you are trying to achieve here and I decided that you were probably trying to put together a consolidated error report. I then created a csv file containing the errors and their source - and any other info you may find useful.

    It looks like this, where the first field is the token you want to use in your like statement in the select and the rest of the info are your literals.

    ORA-19625,SLREFW04,Oracle,
    ORAO/S-Error,SLREFW04,Oracle,

    Now in a simple DOS emulation FOR loop we can loop through the logs and look for any and all defined tokens that indicate errors. We can use the inbuilt variables to get the date too. This is reflected here;

    The following statement

    for /F "tokens=1,2-4 delims=," %i in (oerrs.txt) do logparser "select strcat('%j,%k,DATE%',Text) as Error from ora.txt where Text like '%%i%'" -i:textline -q:on

    Generates this output

    SLREFW04,Oracle,09/10/2008ORA-19625: file error
    SLREFW04,Oracle,09/10/2008'ORAO/S-Error: file not found

    The following example of how to pump this straight into a SQL server database comes from the help file;

    SQL Output Format Examples

    LogParser "SELECT Path, KeyName, ValuleName INTO MyTable FROM \HKLM" -i:REG 
    -o:SQL -server:MyServer -database:MyDatabase -driver:"SQL Server" -username:TestSQLUser 
    -password:TestSQLPassword -createTable:ON

    You can play with the createtable and cleartable directives to either recreate the table from scratch, clear it at each run or append to it. 
     Hope this helps. 
    Cheers, Dave

     
    Yes, dear
Page 1 of 1 (2 items)
Microsoft Communities