| Page Properties | ||||||
|---|---|---|---|---|---|---|
| ||||||
|
...
MS Log Parser may be installed using the Astun 3rd Party installer or downloaded from the Microsoft web site: https://www.iis.net/downloads/community/2010/04/log-parser-22k
“Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows operating system such as the Event Log, the Registry, the file system, and Active Directory.”
...
| Code Block | ||
|---|---|---|
| ||
"c:\program files\log parser 2.2\logparser" -i IISW3C "SELECT TO_UPPERCASE(cs-username) AS User, COUNT(cs-username) AS Visits INTO user.csv FROM C:\Windows\System32\LogFiles\W3SVC1\*.log WHERE cs-uri-stem = '/isharemaps/getsecurity.aspx' GROUP BY TO_UPPERCASE(cs-username) HAVING (Visits > 0)" -o:CSV |
Breakdown
Here's an explanation of the statement used to do the data extraction:
...
| Code Block | ||
|---|---|---|
| ||
logparser -i IISW3C "SELECT SUBSTR(cs-uri-stem, 0, LAST_INDEX_OF(cs-uri-stem,'/')) AS location, SUBSTR(cs-uri-stem, ADD(LAST_INDEX_OF(cs-uri-stem,'/'),1) , STRLEN(cs-uri-stem)) as page, COUNT(*) AS requests FROM *.log GROUP BY cs-uri-stem HAVING SUBSTR(cs-uri-stem, LAST_INDEX_OF(cs-uri-stem,'.'), STRLEN(cs-uri-stem)) = '.aspx' ORDER BY requests DESC" |
Breakdown
SELECT SUBSTR(cs-uri-stem, 0, LAST_INDEX_OF(cs-uri-stem,'/')) AS location, SUBSTR(cs-uri-stem, ADD(LAST_INDEX_OF(cs-uri-stem,'/'),1), STRLEN(cs-uri-stem)) as page, COUNT(*) AS requests | SUBSTR and LAST_INDEX_OF are Log Parser functions and used here to split the url. COUNT(*) counts the number of occurrences of each 'cs-uri-stem' (thanks to the GROUP BY clause) |
FROM *.log | Reads all .log files in the current directory |
GROUP BY cs-uri-stem | Used to define the sets for aggregate functions in the SELECT clause |
HAVING SUBSTR(cs-uri-stem, LAST_INDEX_OF(cs-uri-stem,'.'), STRLEN(cs-uri-stem)) = '.aspx' | Filters for only those requests that end in .aspx |
ORDER BY requests DESC | Ranks the results |
...