J.F.
Posts: 43
Joined: 28.Nov.2005
Status: offline
|
And once you get your log data into W3C textual form, you can use MS's free Log Parser tool (www.logparser.com) to extract the data you want with an SQL query. You can see a bunch of example SQL queries for ISA inside the script named "ISA_LogParser.vbs" at www.isascripts.org (which also performs queries using Log Parser as a COM object).
For example, here's a query to show IPs and FQDNs accessed by each user as shown from the Web Proxy logs:
"SELECT DISTINCT TO_LOWERCASE(cs-username) As User, r-host FROM isaproxylog.w3c
WHERE s-svcname = 'w3proxy' ORDER BY User"
It's output would look like this:
anonymous,login.passport.net
anonymous,shop.dell.com
anonymous,signin.ebay.com
domain\david,127.45.123.58
domain\david,www.google.com
domain\david,www.ibm.com
domain\jason,www.isaserver.com
domain\jason,www.sans.org
Make sure to require authentication from your web proxy clients or else all the lines will say "anonymous".
To use the ISA_LogParser.vbs script above, install Log Parser, open the script in notepad, uncomment the query you want to use, run the script in a CMD with cscript.exe and pass in the ISA web proxy log you want to search as the first argument. And you can use wildcards to pass in multiple log files for an aggregate report, e.g., "cscript.exe isa_logparser.vbs ISALOG_200601*_WEB_*.w3c" would search all the log files for January of 2006 and produce one report from it.
Hope this helps!
JF
--------------------------
PS Here's a listing of all the queries you can find inside the ISA_LogParser.vbs script, and you can get a nice SQL reference help file with log parser when you install it (www.logparser.com):
'--------------------------------------------------------------------
' ISA Web Proxy Logs in W3C format (MSUtil.LogQuery.W3CInputFormat)
'--------------------------------------------------------------------
' Count of unique IP addresses NOT accessing a particular file or filetype from the External network in Web Proxy logs.
'sQuery = "SELECT COUNT(DISTINCT c-ip) FROM " & sFirstArg & " WHERE cs-uri Not Like '%robots.txt' AND cs-Network = 'External' AND rule Like '%www.mydomain.com' "
' Count of unique client IP addresses from the External network in Web Proxy logs.
'sQuery = "SELECT COUNT(DISTINCT c-ip) FROM " & sFirstArg & " WHERE cs-Network = 'External' AND rule Like '%www.mydomain.com' "
' Count of unique IP addresses accessing a particular file or filetype from the External network in Web Proxy logs.
'sQuery = "SELECT COUNT(DISTINCT c-ip) FROM " & sFirstArg & " WHERE cs-uri Like '%.zip' AND cs-Network = 'External' "
' Count of unique IP addresses accessing the robots.txt file from the External network in Web Proxy logs.
'sQuery = "SELECT COUNT(DISTINCT c-ip) FROM " & sFirstArg & " WHERE cs-uri Like '%robots.txt' AND cs-Network = 'External' "
' List of unique client IP addresses in ISA Web Proxy or IIS W3C Extended logs.
'sQuery = "SELECT DISTINCT c-ip FROM " & sFirstArg & " ORDER BY c-ip DESC"
' Count of HTTP Filter error messages from Web Proxy logs.
'sQuery = "SELECT COUNT(*),FilterInfo FROM " & sFirstArg & " WHERE FilterInfo <> Null GROUP BY FilterInfo ORDER BY Count(*) DESC "
' Top 20 external IP addresses that have generated Denied entries in Web Proxy logs.
'sQuery = "SELECT TOP 20 COUNT(*),c-ip FROM " & sFirstArg & " WHERE (Action = 'Denied') AND (cs-Network = 'External') AND (c-ip Not Like '172.16.%') GROUP BY c-ip ORDER BY Count(*) DESC"
' Top 10 external FQDNs that have generated Denied entries in Web Proxy logs (reverse DNS lookups).
'sQuery = "SELECT TOP 10 COUNT(*),REVERSEDNS(c-ip) As fqdn FROM " & sFirstArg & " WHERE (Action = 'Denied') AND (cs-Network = 'External') AND (c-ip Not Like '172.16.%') GROUP BY fqdn ORDER BY Count(*) DESC"
' Top 20 users based on total bytes sent or received as shown from Web Proxy logs.
'sQuery = "SELECT TOP 20 SUM(ADD(cs-bytes,sc-bytes)) As Bytes,TO_LOWERCASE(cs-username) As User FROM " & sFirstArg & " GROUP BY User ORDER BY Bytes DESC "
' IPs and FQDNs accessed by each user as shown from the Web Proxy logs.
'sQuery = "SELECT DISTINCT TO_LOWERCASE(cs-username) As User, r-host FROM " & sFirstArg & " WHERE s-svcname = 'w3proxy' ORDER BY User"
' Percentage of log lines specifically Denied, categorized by governing rule in both Firewall and Web Proxy logs.
'sQuery = "SELECT rule,MUL(PROPCOUNT(*),100) As Percent FROM " & sFirstArg & " WHERE action = 'Denied' GROUP BY rule ORDER BY Percent DESC"
' Percentage of log lines with Allowed or Establish actions, categorized by governing rule in both Firewall and Web Proxy log.
'sQuery = "SELECT rule,MUL(PROPCOUNT(*),100) As Percent FROM " & sFirstArg & " WHERE (action = 'Establish' Or action = 'Allowed') GROUP BY rule ORDER BY Percent DESC"
'--------------------------------------------------------------------
' ISA Firewall Logs in W3C format (MSUtil.LogQuery.W3CInputFormat)
'--------------------------------------------------------------------
' Top 20 external IP addresses that have generated Denied entries in Firewall logs.
'sQuery = "SELECT TOP 20 COUNT(*),EXTRACT_TOKEN(source,0,':') As IpAddr FROM " & sFirstArg & " WHERE (Action = 'Denied') AND ([source network] = 'External') AND (source Not Like '172.16.%') GROUP BY IpAddr ORDER BY Count(*) DESC"
' Percentage of each type of status code in Firewall logs.
'sQuery = "SELECT status,MUL(PROPCOUNT(*),100) As Percent FROM " & sFirstArg & " GROUP BY status ORDER BY Percent DESC"
' Top 20 external IP addresses that have sent Ping of Death packets as shown in Firewall logs.
' In general, use this query to extract Top X list for any status error message type: SYN attacks, LAND attacks, etc.
'sQuery = "SELECT TOP 20 COUNT(*),EXTRACT_TOKEN(source,0,':') As IpAddr FROM " & sFirstArg & " WHERE (status Like '0xc0040019') AND ([source network] = 'External') GROUP BY IpAddr ORDER BY Count(*) DESC"
' Top 20 external IP addresses that have sent packets whose headers were logged as raw hex in Firewall logs (bad sign).
'sQuery = "SELECT TOP 20 COUNT(*),EXTRACT_TOKEN(source,0,':') As IpAddr FROM " & sFirstArg & " WHERE ([IP header] <> Null) AND ([source network] = 'External') GROUP BY IpAddr ORDER BY Count(*) DESC"
' Count of agent applications from Firewall logs.
'sQuery = "SELECT COUNT(*),agent FROM " & sFirstArg & " GROUP BY agent ORDER BY Count(*) DESC "
' Top 20 users based on total bytes sent or received as shown from Firewall logs.
'sQuery = "SELECT TOP 20 SUM(ADD([bytes received],[bytes sent])) As Bytes,TO_LOWERCASE(username) As User FROM " & sFirstArg & " GROUP BY User ORDER BY Bytes DESC "
' Percentage of log lines specifically Denied, categorized by governing rule in both Firewall and Web Proxy logs.
'sQuery = "SELECT rule,MUL(PROPCOUNT(*),100) As Percent FROM " & sFirstArg & " WHERE action = 'Denied' GROUP BY rule ORDER BY Percent DESC"
' Percentage of log lines with Allowed or Establish actions, categorized by governing rule in both Firewall and Web Proxy log.
'sQuery = "SELECT rule,MUL(PROPCOUNT(*),100) As Percent FROM " & sFirstArg & " WHERE (action = 'Establish' Or action = 'Allowed') GROUP BY rule ORDER BY Percent DESC"
'--------------------------------------------------------------------
' IIS Logs in W3C format (MSUtil.LogQuery.IISW3CInputFormat)
'--------------------------------------------------------------------
' Unique referrer URLs in IIS logs with some qualifiers to filter out noise in W3C Extended format for IIS.
'sQuery = "SELECT COUNT(c-ip),cs(Referer) FROM " & sFirstArg & " WHERE (c-ip Not Like '172.16%') AND (c-ip <> '68.93.110.249') AND (cs(Referer) Not Like '%isascripts.org%') AND (cs(Referer) Not Like '%microsoft.public.%') GROUP BY cs(Referer) ORDER BY Count(c-ip) DESC "
' Unique user-agents in IIS logs with some qualifiers to filter out noise in W3C Extended format for IIS.
'sQuery = "SELECT COUNT(*),cs(User-Agent) FROM " & sFirstArg & " WHERE (c-ip Not Like '172.16%') AND (c-ip <> '68.93.110.249') GROUP BY cs(User-Agent) ORDER BY Count(*) DESC "
' List of unique client IP addresses in ISA Web Proxy or IIS W3C Extended logs.
'sQuery = "SELECT DISTINCT c-ip FROM " & sFirstArg & " ORDER BY c-ip DESC"
' Unique referrer FQDNs in IIS logs with some qualifiers to filter out noise in W3C Extended format for IIS.
'sQuery = "SELECT COUNT(c-ip),EXTRACT_TOKEN(cs(Referer),2,'/') As fqdn FROM " & sFirstArg & " WHERE (c-ip Not Like '172.16%') AND (c-ip <> '68.93.110.249') AND (cs(Referer) Not Like '%isascripts.org%') AND (cs(Referer) Not Like '%microsoft.public.%') GROUP BY fqdn HAVING Count(*) > 1 ORDER BY Count(c-ip) DESC "
|
|
My Subscription |
My Forums |
Address Book |
Member List |
Search |
FAQ |
Ticket List |
Log Out
Printable Version
Viewing, extracting web proxy logs - 
RE: Viewing, extracting web proxy logs - 
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread