logparser

Using a variable within the FROM section of a Log Parser Lizard IIS log query

I'm trying to speed up my Log Parser Lizard queries to IIS logs on one of our servers.

This kind of query works but it's very slow:

SELECT TOP 100 * FROM '\\myserver\c$\inetpub\logs\LogFiles\W3SVC1\u_ex*.log'
ORDER BY time DESC

If I specify today's log filename that's a lot quicker:

SELECT TOP 100 * FROM '\\myserver\c$\inetpub\logs\LogFiles\W3SVC1\u_ex190731.log'
ORDER BY time DESC

I'm trying to find a way to achieve this without having to keep changing the filename within the query to match today's date. I can't find any way of using variables or functions like strcat within the FROM section of the query.

So in simpler terms, is there any way to inject today's date into a query like this:

SELECT * FROM 'C:\test\%DATE%.txt'
Solution

  • I found that LogParserLizard supports inline VB.NET code, specified using <% ... %> tags, so it was just a matter of inserting the date using that syntax and it worked fine.

    SELECT TOP 100 *
FROM '\\myserver\c$\inetpub\logs\LogFiles\W3SVC1\u_ex<% return DateTime.Now.ToString("yyMMdd") %>.log'
ORDER BY time DESC

    or in my simplified version it would be:

    SELECT * FROM 'C:\test\<% return DateTime.Now.ToString("yyMMdd") %>.txt'
--converts to `C:\test\190731.txt`