Printable View
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
... 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!
Hi Stan
FYI you could zip both files together to get round these limitations.
Regards
Malcolm
Yes, but it was 3am EDT:doh: I zipped the next one.Quote:
Originally Posted by mdmackillop
I completely understand. :yes
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.
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!
Hey Malcolm,
:think: 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 :rolleyes:
[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.
Hmm.. Anytime I've run it Logparser creates it if it doesn't exit.Quote:
Originally Posted by malik641
Yeah! they can't all be gems:rotlaugh: 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.Quote:
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.
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.Quote:
Originally Posted by malik641
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
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 :reading: (which I assume that I guessed correctly, since you didn't really correct me :))Quote:
Originally Posted by stanl
I would love to see that code :)Quote:
Originally Posted by stanl
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..Quote:
Originally Posted by malik641
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