If you want to add my set of Log Parser Studio custom queries to your LPS library, follow these instructions…
Note: if you want to merge these with your existing queries, just copy the inner nodes into your LPS library file – if you just want to use my custom set without searching through the huge list of standard queries, you can overwrite your LPS library file.
You can find the LPS library file next to the LPS.exe file. It is called “LPSV2Library.XML”.
Many of these queries are adapted from the standard versions, and others will be particularly helpful if you…
- Use a load balancer and use X-Forwarded-For
- Run multiple hosts from a single IIS website
<?xml version="1.0" encoding="utf-8"?> <ArrayOfLPQuery xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <LPQuery> <QueryName>CUSTOM: List of User Agents</QueryName> <QueryDescription>List of user agents and popularity. See https://www.stevefenton.co.uk/2016/08/using-log-parser-studio-to-find-user-agents/</QueryDescription> <QueryData> SELECT cs(User-Agent), COUNT(cs(User-Agent)) AS requestcount FROM '[LOGFILEPATH]' WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) GROUP BY cs(User-Agent) ORDER BY COUNT(cs(User-Agent)) DESC</QueryData> <QueryID>248e5a40-bb08-452a-9839-87bda2118791</QueryID> <LogType>W3CLOG</LogType> <QueryCategory>ALL</QueryCategory> <IsFavorite>true</IsFavorite> <DateModified>2016-08-08T18:37:54.2642531+00:00</DateModified> </LPQuery> <LPQuery> <QueryName>CUSTOM: Requests Per Day</QueryName> <QueryDescription>Count of requests per day. See https://www.stevefenton.co.uk/2016/04/using-log-parser-studio-to-get-requests-per-hour-or-minute/</QueryDescription> <QueryData> SELECT date, COUNT(*) AS Total, SUM(sc-bytes) AS TotBytesSent FROM '[LOGFILEPATH]' WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) GROUP BY date ORDER BY date </QueryData> <QueryID>59965b72-929a-4434-a579-8890c60d8069</QueryID> <LogType>W3CLOG</LogType> <QueryCategory>ALL</QueryCategory> <IsFavorite>true</IsFavorite> <DateModified>2016-08-08T18:36:41.2720729+00:00</DateModified> </LPQuery> <LPQuery> <QueryName>CUSTOM: Requests Per Hour</QueryName> <QueryDescription>Count of requests per hour. See https://www.stevefenton.co.uk/2016/04/using-log-parser-studio-to-get-requests-per-hour-or-minute/</QueryDescription> <QueryData> SELECT QUANTIZE(TO_TIMESTAMP(date, time), 3600) AS H, COUNT(*) AS Total, SUM(sc-bytes) AS TotBytesSent FROM '[LOGFILEPATH]' WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) GROUP BY H ORDER BY H</QueryData> <QueryID>b0917339-8acd-4b5c-ab58-d6198fe10de0</QueryID> <LogType>W3CLOG</LogType> <QueryCategory>ALL</QueryCategory> <IsFavorite>true</IsFavorite> <DateModified>2016-08-08T18:34:26.8383854+00:00</DateModified> </LPQuery> <LPQuery> <QueryName>CUSTOM: Requests Per Minute</QueryName> <QueryDescription>Count of requests per minute. See https://www.stevefenton.co.uk/2016/04/using-log-parser-studio-to-get-requests-per-hour-or-minute/</QueryDescription> <QueryData> SELECT QUANTIZE(TO_TIMESTAMP(date, time), 60) AS M, COUNT(*) AS Total, SUM(sc-bytes) AS TotBytesSent FROM '[LOGFILEPATH]' WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) GROUP BY M ORDER BY M</QueryData> <QueryID>d1cc5a6a-a4b5-484a-81fb-5902126aabcf</QueryID> <LogType>W3CLOG</LogType> <QueryCategory>ALL</QueryCategory> <IsFavorite>true</IsFavorite> <DateModified>2016-08-08T18:33:45.1749898+00:00</DateModified> </LPQuery> <LPQuery> <QueryName>CUSTOM: Top 20 URIs</QueryName> <QueryDescription>Top list of URIs.</QueryDescription> <QueryData> SELECT TOP 20 cs-uri-stem, COUNT(*) AS Total, MAX(time-taken) AS MaxTime, AVG(time-taken) AS AvgTime FROM '[LOGFILEPATH]' WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) GROUP BY cs-uri-stem ORDER BY Total DESC </QueryData> <QueryID>2818b23a-5cbe-4e8d-b629-0263313bc4eb</QueryID> <LogType>W3CLOG</LogType> <QueryCategory>ALL</QueryCategory> <IsFavorite>true</IsFavorite> <DateModified>2016-08-08T18:37:54.2642531+00:00</DateModified> </LPQuery> <LPQuery> <QueryName>CUSTOM: Top Addresses For Host Name</QueryName> <QueryDescription>Top list of addresses for a host name. See https://www.stevefenton.co.uk/2016/04/using-log-parser-studio-to-get-request-by-host-name/</QueryDescription> <QueryData> SELECT TOP 20 cs-uri-stem, COUNT(*) AS Total, MAX(time-taken) AS MaxTime, AVG(time-taken) AS AvgTime, AVG(sc-bytes) AS AvgBytes FROM '[LOGFILEPATH]' WHERE cs-host = 'example.com' AND date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) GROUP BY cs-uri-stem ORDER BY Total DESC</QueryData> <QueryID>0cfaa086-b529-4d19-b58b-ccae9de0f4b9</QueryID> <LogType>W3CLOG</LogType> <QueryCategory>ALL</QueryCategory> <IsFavorite>true</IsFavorite> <DateModified>2016-08-08T18:32:12.3246746+00:00</DateModified> </LPQuery> <LPQuery> <QueryName>CUSTOM: Top Host Names</QueryName> <QueryDescription>Top list of host names. See https://www.stevefenton.co.uk/2016/04/using-log-parser-studio-to-get-request-by-host-name/</QueryDescription> <QueryData> SELECT TOP 20 cs-host, COUNT(*) AS Total FROM '[LOGFILEPATH]' WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) GROUP BY cs-host ORDER BY Total DESC</QueryData> <QueryID>41d7033d-8570-48e1-85ba-679755483d58</QueryID> <LogType>W3CLOG</LogType> <QueryCategory>ALL</QueryCategory> <IsFavorite>true</IsFavorite> <DateModified>2016-08-08T18:30:25.5495609+00:00</DateModified> </LPQuery> <LPQuery> <QueryName>CUSTOM: Top IP Rangers</QueryName> <QueryDescription>Top list of first three octets of IP addresses.</QueryDescription> <QueryData>SELECT EXTRACT_PREFIX(c-ip, 2, '.') AS ip-range, COUNT(ip-range) AS requestcount FROM '[LOGFILEPATH]' WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) GROUP BY ip-range ORDER BY COUNT(ip-range) DESC</QueryData> <QueryID>25644ade-083a-4a6d-a326-0e23c56d9389</QueryID> <LogType>W3CLOG</LogType> <QueryCategory>ALL</QueryCategory> <IsFavorite>true</IsFavorite> <DateModified>2016-08-09T08:13:48.2146795+00:00</DateModified> </LPQuery> <LPQuery> <QueryName>CUSTOM: Top IPs</QueryName> <QueryDescription>Top list of IP addresses. See https://www.stevefenton.co.uk/2016/03/using-log-parser-studio-to-find-guilty-ip-addresses/</QueryDescription> <QueryData> SELECT c-ip, count(c-ip) AS requestcount FROM '[LOGFILEPATH]' WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) GROUP BY c-ip ORDER BY count(c-ip) DESC</QueryData> <QueryID>08e43a1e-10b5-44e8-8841-569fc7343c02</QueryID> <LogType>W3CLOG</LogType> <QueryCategory>ALL</QueryCategory> <IsFavorite>true</IsFavorite> <DateModified>2016-08-08T18:28:53.8463084+00:00</DateModified> </LPQuery> <LPQuery> <QueryName>CUSTOM: Top Requests From IP Range</QueryName> <QueryDescription>Top list of IP addresses from a given range.</QueryDescription> <QueryData>SELECT c-ip, EXTRACT_PREFIX(c-ip, 2, '.') AS ip-range, COUNT(c-ip) AS requestcount FROM '[LOGFILEPATH]' WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) AND ip-range = '192.168.2' GROUP BY c-ip, ip-range ORDER BY COUNT(c-ip) DESC</QueryData> <QueryID>05962ae7-84de-414c-95cd-2e95d7d0e8a0</QueryID> <LogType>W3CLOG</LogType> <QueryCategory>ALL</QueryCategory> <IsFavorite>true</IsFavorite> <DateModified>2016-08-09T08:22:44.2563766+00:00</DateModified> </LPQuery> <LPQuery> <QueryName>CUSTOM: Top Slow URLs</QueryName> <QueryDescription>Top list of slow URLs</QueryDescription> <QueryData> SELECT TOP 25 cs-uri-stem AS URL, MAX(time-taken) AS Max, MIN(time-taken) AS Min, Avg(time-taken) AS Average FROM '[LOGFILEPATH]' WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) GROUP BY URL ORDER BY Average DESC </QueryData> <QueryID>8cf173d8-ba44-4aed-ba8c-eaf7f7ce0559</QueryID> <LogType>W3CLOG</LogType> <QueryCategory>ALL</QueryCategory> <IsFavorite>true</IsFavorite> <DateModified>2016-08-08T18:37:54.2642531+00:00</DateModified> </LPQuery> <LPQuery> <QueryName>CUSTOM: Top Status Codes</QueryName> <QueryDescription>Top list of status codes.</QueryDescription> <QueryData> SELECT TOP 25 sc-status, COUNT(*) AS Hits FROM '[LOGFILEPATH]' WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) GROUP BY sc-status ORDER BY Hits DESC </QueryData> <QueryID>e80a8693-c0be-49fd-bcab-7e8f6512cfd7</QueryID> <LogType>W3CLOG</LogType> <QueryCategory>ALL</QueryCategory> <IsFavorite>true</IsFavorite> <DateModified>2016-08-08T18:37:54.2642531+00:00</DateModified> </LPQuery> <LPQuery> <QueryName>CUSTOM: Top Verbs</QueryName> <QueryDescription>Top list of HTTP verbs.</QueryDescription> <QueryData> SELECT TOP 20 cs-method, COUNT(*) AS Total, MAX(time-taken) AS MaxTime, AVG(time-taken) AS AvgTime, AVG(sc-bytes) AS AvgBytesSent FROM '[LOGFILEPATH]' WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) GROUP BY cs-method ORDER BY Total DESC </QueryData> <QueryID>4c13c624-8d15-43b9-b94b-123283467941</QueryID> <LogType>W3CLOG</LogType> <QueryCategory>ALL</QueryCategory> <IsFavorite>true</IsFavorite> <DateModified>2016-08-08T18:37:54.2642531+00:00</DateModified> </LPQuery> <LPQuery> <QueryName>CUSTOM: Top X-Forwarded-For Ranges</QueryName> <QueryDescription>Top list of first three octets of X-Forwarded-For IP addresses.</QueryDescription> <QueryData> SELECT EXTRACT_PREFIX(X-Forwarded-For, 2, '.') AS ip-range, COUNT(ip-range) AS requestcount FROM '[LOGFILEPATH]' WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) GROUP BY ip-range ORDER BY COUNT(ip-range) DESC</QueryData> <QueryID>a3d3722b-5c28-4f72-85f9-8c5c988cfaf9</QueryID> <LogType>W3CLOG</LogType> <QueryCategory>ALL</QueryCategory> <IsFavorite>true</IsFavorite> <DateModified>2016-08-09T08:06:39.306117+00:00</DateModified> </LPQuery> <LPQuery> <QueryName>CUSTOM: Top X-Forwarded-For Top IPs</QueryName> <QueryDescription>Top list of IP addresses from the X-Forwarded-For header. See https://www.stevefenton.co.uk/2016/08/using-log-parser-studio-to-find-guilty-ip-addresses-from-x-forwarded-for/</QueryDescription> <QueryData> SELECT X-Forwarded-For, COUNT(X-Forwarded-For) AS requestcount FROM '[LOGFILEPATH]' WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) GROUP BY X-Forwarded-For ORDER BY COUNT(X-Forwarded-For) DESC</QueryData> <QueryID>1c89edfa-df41-4bbf-af90-0eefcd15ab2e</QueryID> <LogType>W3CLOG</LogType> <QueryCategory>ALL</QueryCategory> <IsFavorite>true</IsFavorite> <DateModified>2016-08-08T18:21:01.0952345+00:00</DateModified> </LPQuery> <LPQuery> <QueryName>CUSTOM: Top Requests From X-Forwarded-For Range</QueryName> <QueryDescription>Top list of requests from a given X-Forwarded-For Range.</QueryDescription> <QueryData> SELECT X-Forwarded-For, EXTRACT_PREFIX(X-Forwarded-For, 2, '.') AS ip-range, COUNT(X-Forwarded-For) AS requestcount FROM '[LOGFILEPATH]' WHERE date > SUB(TO_LOCALTIME(SYSTEM_TIMESTAMP()), TIMESTAMP('0000-01-02 00:00', 'yyyy-MM-dd HH:mm')) AND ip-range = '192.168.0' GROUP BY X-Forwarded-For, ip-range ORDER BY COUNT(X-Forwarded-For) DESC</QueryData> <QueryID>b27f14fe-9c24-44a5-bcd5-1402da6317a0</QueryID> <LogType>W3CLOG</LogType> <QueryCategory>ALL</QueryCategory> <IsFavorite>true</IsFavorite> <DateModified>2016-08-09T08:23:24.5436842+00:00</DateModified> </LPQuery> </ArrayOfLPQuery>