Consulting

Results 1 to 12 of 12

Thread: SQL via Logparser

  1. #1
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location

    SQL via Logparser

    This might be a good forum to discuss the neat things you can accomplish with Microsofts free Logparser tool:

    http://www.microsoft.com/downloads/d...displaylang=en

    I have attached a workbook with VBA code to perform a retrieval of Yahoo RSS feeds. LogParser is required and the code uses an external template for the SQL parsing. [I can only upload 1 file, so refer to my subsequent post]

    Stan

  2. #2
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    ... here is the rss.tpl file... Stan

    NOTE: due to file extension restrictions, the file is named rss.doc - rename it to rss.tpl, which is what the vba code looks for. Enjoy because Logparser rocks!

  3. #3
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Stan
    FYI you could zip both files together to get round these limitations.
    Regards
    Malcolm
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  4. #4
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by mdmackillop
    Hi Stan
    FYI you could zip both files together to get round these limitations.
    Regards
    Malcolm
    Yes, but it was 3am EDT I zipped the next one.

  5. #5
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    I completely understand.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #6
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Hey Stan,

    I think you forgot to mention to create a blank lp.htm file

    Also, because my ActiveWorkbook.Path directory has some spaces in it, I received a SQL syntax error. I fixed that by changing:
    [VBA]cSQL = cSQL & "INTO " & f & " FROM " & cURL
    ' To:
    cSQL = cSQL & "INTO '" & f & "' FROM " & cURL[/VBA]
    I just added single quotes ' before and after the variable f.




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  7. #7
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Joseph,
    The code worked for me without me creating the htm file (but it's there now). On the other hand, I've no idea what the code is doing!
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  8. #8
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Hey Malcolm,

    I see...

    Ok, just tested without the htm file and it works like it did for you. My apologies. What happened for me was, since I received the SQL syntax error, I looked through all the code and saw that along the way and figured I needed to create the htm file myself.

    And just to tweak Stan's code a little, I changed the htm file from opening in IE to whatever the default browser is (I don't like IE). So mine opens in FF.

    Also, I commented the code a little to help you (Malcolm, and whoever else reads this) and me to better understand this. Also, check out the Log Parser help file. Look for titles "XML Input Format Fields; XML Input Format Parameters; XML Output Format Parameters; XML Output Format". Stan, if something I said was wrong in the commented code, please correct me
    [vba]Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, _
    ByVal lpOperation As String, ByVal lpFile As String, _
    ByVal lpParameters As String, ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long

    Private Const SW_NORMAL = 1

    Sub lp()
    'set reference to logparser [c:\Program Files\LogParse 2.2\logparser.dll]
    'also set references to Microsoft HTML Object, Microsoft Internet Controls

    Dim cURL As String

    f = ActiveWorkbook.Path & "\lp.htm"
    f1 = ActiveWorkbook.Path & "\rss.tpl"
    Set oLog = CreateObject("MSUtil.LogQuery")
    cURL = Range("A4").Value
    Set oInput = CreateObject("MSUtil.LogQuery.XMLInputFormat.1")
    Set oOutput = CreateObject("MSUtil.LogQuery.TemplateOutputFormat.1")

    ' Sets the tpl file to output the information to
    oOutput.tpl = f1
    ' FileMode = 1 means that the tpl file will be overwritten
    oOutput.filemode = 1
    ' Tree mode is a certain way the Input reads the XML code
    oInput.fMode = "Tree"
    ' From Help: Specifying "XPath" causes the XML input format to create field
    ' names using the XPath queries for the corresponding nodes or attributes.
    ' I'm not sure how this works out...
    oInput.fNames = "XPath"
    ' SQL statement takes results and stores it into lp.htm (the INTO part does that)
    ' Check out: http://www.markcarter.me.uk/computin...elect_into.htm
    cSQL = "SELECT /rss/channel/item/title AS XX,/rss/channel/item/pubDate AS YY," & _
    "/rss/channel/item/description AS ZZ INTO '" & f & "' FROM " & cURL

    oLog.ExecuteBatch cSQL, oInput, oOutput
    Set oInput = Nothing
    Set oOutput = Nothing
    Set oLog = Nothing

    ' Open Default web browser to htm file
    ShellExecute Application.hwnd, "open", f, vbNullString, vbNullString, SW_NORMAL
    End Sub[/vba]
    Stan,
    This is some pretty cool code, btw. I remember you showing me this in another thread (I still don't understand the problem you were having with the app not working correctly, sorry buddy). I'm starting to understand this better than last time because I am a little better with my SQL knowledge than when I first looked at it.

    I'm not sure what fMode = "Tree" is doing exactly. I tried changing it to "Compact" just to see what happens and I received an error. Stan, could you explain what that "Tree" field mode is used for in this code? I read the help documentation...but I'm not sure how it relates to the code.




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  9. #9
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by malik641
    Hey Stan,

    I think you forgot to mention to create a blank lp.htm file
    Hmm.. Anytime I've run it Logparser creates it if it doesn't exit.

    Also, because my ActiveWorkbook.Path directory has some spaces in it, I received a SQL syntax error. I fixed that by changing:
    [vba]cSQL = cSQL & "INTO " & f & " FROM " & cURL
    ' To:
    cSQL = cSQL & "INTO '" & f & "' FROM " & cURL[/vba]
    I just added single quotes ' before and after the variable f.
    Yeah! they can't all be gems I guess the question is, did it work. I originally posted this on another forum as a separate app that created it's own window to host the RSS feeds. It just sat on the taskbar.

  10. #10
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by malik641
    Stan,
    This is some pretty cool code...
    I'm not sure what fMode = "Tree" is doing exactly.
    Thank you. My understanding is that tree mode sets the parser up to obtain multiple 'branches' of the same markup. I believe tree mode is required for xPath SQL statements.

    Sorry about lack of code comments. It is just amazing what Logparser can render given so few lines of code. I also have a small snippet that creates an Excel sheet from Windows log files for significant events which uses Logparser. Let me know if you want it posted.

    Stan

  11. #11
    Administrator
    2nd VP-Knowledge Base VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Quote Originally Posted by stanl
    Sorry about lack of code comments.
    No worries, Stan. To be honest, I'm kind of glad you didn't comment it because it got me to search through the help file and figure out what's going on (which I assume that I guessed correctly, since you didn't really correct me )

    Quote Originally Posted by stanl
    It is just amazing what Logparser can render given so few lines of code. I also have a small snippet that creates an Excel sheet from Windows log files for significant events which uses Logparser. Let me know if you want it posted.
    I would love to see that code




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  12. #12
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by malik641
    I would love to see that code
    Code snippet for Parsing Event Logs is below. I did want to mention that the code to parse the Yahoo RSS is generic to any RSS feed (that follow the RSS specification) so you can get financial information, blogs etc..

    As for the event log code, you will note I hard-coded the timegenerated variable to June 1, 2007 - you would probably want to offer the user an input to choose 7,14,30 days back from the current date and perform the math and string conversion. Note the OpenXML() method is a 2003 feature. Also, if the event data is quite extensive you can go the extra stp and create a Pivot Table from the xml data.

    [vba]
    Sub eventlog()
    'event log file error types
    '//////////////////////////////////////////////////////////////////////////////////////
    'Error=2 An error event. This indicates a significant problem the user should
    ' know about' usually a loss of functionality or data.
    '
    'FailureAudit=4 A failure audit event. This indicates a security event that occurs when
    ' an audited access attempt fails' for example, a failed attempt to open a file.
    '
    'Information=0 An information event. This indicates a significant, successful operation.
    '
    'SuccessAudit=3 A success audit event. This indicates a security event that occurs when
    ' an audited access attempt is successful' for example, logging on successfully.
    '
    'Warning=1 A warning event. This indicates a problem that is not immediately significant,
    ' but that may signify conditions that could cause future problems.
    '//////////////////////////////////////////////////////////////////////////////////////
    Dim cXML As String
    cXML = ActiveWorkbook.Path & "\events.xml"
    If Dir(cXML) <> "" Then Kill (cXML)
    'Event log dates take this format
    Dim thedate As String
    thedate = "2007-06-01 00:00:00"
    cSQL = "SELECT timegenerated, timewritten, computername, eventid, eventtypename, "
    cSQL = cSQL & "eventcategory, eventcategoryname, sourcename, SID, Resolve_SID(SID), "
    cSQL = cSQL & "message, strings, data INTO " & cXML & " FROM System,Security WHERE "
    cSQL = cSQL & "eventtype IN (1;2) AND timegenerated > '" & thedate & "' "
    cSQL = cSQL & "ORDER BY timegenerated"
    MsgBox cSQL
    Set oLog = CreateObject("MSUtil.LogQuery")
    oLog.maxParseErrors = 100
    Set oInput = CreateObject("MSUtil.LogQuery.EventLogInputFormat")
    Set oOut = CreateObject("MSUtil.LogQuery.XMLOutputFormat.1")
    oLog.ExecuteBatch cSQL, oInput, oOut
    If oLog.lastError <> 0 Then
    cErrors = "Errors:" & vbCrLf
    For Each strMessage In oLog.errorMessages
    cErrors = cErrors & strMessage & vbCrLf
    Next
    MsgBox cErrors
    End If

    If Dir(cXML) <> "" Then
    'this is a 2003 feature
    Application.Workbooks.OpenXML Filename:=cXML, LoadOption:=xlXmlLoadImportToList
    Else
    MsgBox "No Events found Events XML Not Created"
    End If
    End Sub
    [/vba]

    Stan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •