Revisions to Recommended LogParser queries for IIS monitoring?

Post Undeleted by Jeff Atwood

occurred Aug 14, 2009 at 10:04

Post Deleted by GregD

occurred Aug 7, 2009 at 19:03

edited for formatting; added 4 characters in body

Source Link

edited Aug 6, 2009 at 21:06

GregD

8.8k
1
26
36

logparser -i:FS "SELECT TOP 20 Path, CreationTime from c:\inetpub\wwwroot*.* ORDER BY CreationTime DESC" -rtp:-1

logparser -i:FS "SELECT TOP 20 Path, CreationTime from c:\inetpub\wwwroot\*.* ORDER BY CreationTime DESC" -rtp:-1

Path                                                        CreationTime
----------------------------------------------------------- ------------------
c:\inetpub\wwwroot\Default.asp                              6/22/2003 6:00:01
c:\inetpub\wwwroot\About.asp                                6/22/2003 6:00:00
c:\inetpub\wwwroot\global.asa                               6/22/2003 6:00:00
c:\inetpub\wwwroot\Products.asp                             6/22/2003 6:00:00

20 most recently modified files logparser

logparser -i:FS "SELECT TOP 20 Path, LastWriteTime from c:\inetpub\wwwroot*.* ORDER BY LastWriteTime DESC" -rtp:-1

Files that have resulted in 200 status codes Files that have resulted in 200 status codes (in case trojans were deleted)

logparser "SELECT DISTINCT TO_LOWERCASE(in case trojans were deletedcs-uri-stem) AS URL, Count() AS Hits FROM ex.log WHERE sc-status=200 GROUP BY URL ORDER BY URL" -rtp:-1

logparser "SELECT DISTINCT TO_LOWERCASE(cs-uri-stem) AS URL, Count(*) AS Hits FROM ex*.log WHERE sc-status=200 GROUP BY URL ORDER BY URL" -rtp:-1

URL                                      Hits
---------------------------------------- -----
/About.asp                               122
/Default.asp                             9823
/downloads/setup.exe                     701
/files.zip                               1
/Products.asp                            8341
/robots.txt                              2830

logparser "SELECT DISTINCT date, cs-uri-stem, c-ip, Count() AS Hits FROM ex.log GROUP BY date, c-ip, cs-uri-stem HAVING Hits>50 ORDER BY Hits Desc" -rtp:-1

logparser "SELECT DISTINCT date, cs-uri-stem, c-ip, Count(*) AS Hits FROM ex*.log GROUP BY date, c-ip, cs-uri-stem HAVING Hits>50 ORDER BY Hits Desc" -rtp:-1

date       cs-uri-stem                         c-ip            Hits
---------- ----------------------------------- --------------- ----
2003-05-19 /Products.asp                       203.195.18.24   281
2003-06-22 /Products.asp                       210.230.200.54  98
2003-06-05 /Products.asp                       203.195.18.24   91
2003-05-07 /Default.asp                        198.132.116.174 74

logparser -i:FS "SELECT TOP 20 Path, CreationTime from c:\inetpub\wwwroot\*.* ORDER BY CreationTime DESC" -rtp:-1

Path                                                        CreationTime
----------------------------------------------------------- ------------------
c:\inetpub\wwwroot\Default.asp                              6/22/2003 6:00:01
c:\inetpub\wwwroot\About.asp                                6/22/2003 6:00:00
c:\inetpub\wwwroot\global.asa                               6/22/2003 6:00:00
c:\inetpub\wwwroot\Products.asp                             6/22/2003 6:00:00

20 most recently modified files logparser -i:FS "SELECT TOP 20 Path, LastWriteTime from c:\inetpub\wwwroot*.* ORDER BY LastWriteTime DESC" -rtp:-1

Files that have resulted in 200 status codes (in case trojans were deleted)

logparser "SELECT DISTINCT TO_LOWERCASE(cs-uri-stem) AS URL, Count(*) AS Hits FROM ex*.log WHERE sc-status=200 GROUP BY URL ORDER BY URL" -rtp:-1

URL                                      Hits
---------------------------------------- -----
/About.asp                               122
/Default.asp                             9823
/downloads/setup.exe                     701
/files.zip                               1
/Products.asp                            8341
/robots.txt                              2830

logparser "SELECT DISTINCT date, cs-uri-stem, c-ip, Count(*) AS Hits FROM ex*.log GROUP BY date, c-ip, cs-uri-stem HAVING Hits>50 ORDER BY Hits Desc" -rtp:-1

date       cs-uri-stem                         c-ip            Hits
---------- ----------------------------------- --------------- ----
2003-05-19 /Products.asp                       203.195.18.24   281
2003-06-22 /Products.asp                       210.230.200.54  98
2003-06-05 /Products.asp                       203.195.18.24   91
2003-05-07 /Default.asp                        198.132.116.174 74

logparser -i:FS "SELECT TOP 20 Path, CreationTime from c:\inetpub\wwwroot*.* ORDER BY CreationTime DESC" -rtp:-1

Path                                                        CreationTime
----------------------------------------------------------- ------------------
c:\inetpub\wwwroot\Default.asp                              6/22/2003 6:00:01
c:\inetpub\wwwroot\About.asp                                6/22/2003 6:00:00
c:\inetpub\wwwroot\global.asa                               6/22/2003 6:00:00
c:\inetpub\wwwroot\Products.asp                             6/22/2003 6:00:00

20 most recently modified files

logparser -i:FS "SELECT TOP 20 Path, LastWriteTime from c:\inetpub\wwwroot*.* ORDER BY LastWriteTime DESC" -rtp:-1

Files that have resulted in 200 status codes (in case trojans were deleted)

logparser "SELECT DISTINCT TO_LOWERCASE(cs-uri-stem) AS URL, Count() AS Hits FROM ex.log WHERE sc-status=200 GROUP BY URL ORDER BY URL" -rtp:-1

URL                                      Hits
---------------------------------------- -----
/About.asp                               122
/Default.asp                             9823
/downloads/setup.exe                     701
/files.zip                               1
/Products.asp                            8341
/robots.txt                              2830

logparser "SELECT DISTINCT date, cs-uri-stem, c-ip, Count() AS Hits FROM ex.log GROUP BY date, c-ip, cs-uri-stem HAVING Hits>50 ORDER BY Hits Desc" -rtp:-1

date       cs-uri-stem                         c-ip            Hits
---------- ----------------------------------- --------------- ----
2003-05-19 /Products.asp                       203.195.18.24   281
2003-06-22 /Products.asp                       210.230.200.54  98
2003-06-05 /Products.asp                       203.195.18.24   91
2003-05-07 /Default.asp                        198.132.116.174 74

Source Link

answered Aug 6, 2009 at 20:58

GregD

8.8k
1
26
36

These were all found here (which is an excellent guide for parsing your IIS logfiles, btw):

20 newest files on your website

logparser -i:FS "SELECT TOP 20 Path, CreationTime from c:\inetpub\wwwroot\*.* ORDER BY CreationTime DESC" -rtp:-1

Path                                                        CreationTime
----------------------------------------------------------- ------------------
c:\inetpub\wwwroot\Default.asp                              6/22/2003 6:00:01
c:\inetpub\wwwroot\About.asp                                6/22/2003 6:00:00
c:\inetpub\wwwroot\global.asa                               6/22/2003 6:00:00
c:\inetpub\wwwroot\Products.asp                             6/22/2003 6:00:00

20 most recently modified files logparser -i:FS "SELECT TOP 20 Path, LastWriteTime from c:\inetpub\wwwroot*.* ORDER BY LastWriteTime DESC" -rtp:-1

Path                                                        LastWriteTime
----------------------------------------------------------- ------------------
c:\inetpub\wwwroot\Default.asp                              6/22/2003 14:00:01
c:\inetpub\wwwroot\About.asp                                6/22/2003 14:00:00
c:\inetpub\wwwroot\global.asa                               6/22/2003 6:00:00
c:\inetpub\wwwroot\Products.asp                             6/22/2003 6:00:00

Files that have resulted in 200 status codes (in case trojans were deleted)

logparser "SELECT DISTINCT TO_LOWERCASE(cs-uri-stem) AS URL, Count(*) AS Hits FROM ex*.log WHERE sc-status=200 GROUP BY URL ORDER BY URL" -rtp:-1

URL                                      Hits
---------------------------------------- -----
/About.asp                               122
/Default.asp                             9823
/downloads/setup.exe                     701
/files.zip                               1
/Products.asp                            8341
/robots.txt                              2830

Show any IP address that hit the same page more than 50 times in a single day

logparser "SELECT DISTINCT date, cs-uri-stem, c-ip, Count(*) AS Hits FROM ex*.log GROUP BY date, c-ip, cs-uri-stem HAVING Hits>50 ORDER BY Hits Desc" -rtp:-1

date       cs-uri-stem                         c-ip            Hits
---------- ----------------------------------- --------------- ----
2003-05-19 /Products.asp                       203.195.18.24   281
2003-06-22 /Products.asp                       210.230.200.54  98
2003-06-05 /Products.asp                       203.195.18.24   91
2003-05-07 /Default.asp                        198.132.116.174 74

Stack Exchange Network

Return to Answer

Post Timeline