• RSS
  • Twitter
  • FaceBook

Welcome to ISAserver.org

Forums | Register | Login | My Profile | Inbox | RSS RSS icon | My Subscription | My Forums | Address Book | Member List | Search | FAQ | Ticket List | Log Out

Viewing, extracting web proxy logs

Users viewing this topic: none

Logged in as: Guest
  Printable Version
All Forums >> [ISA Server 2004 Firewall] >> Logging and Reporting >> Viewing, extracting web proxy logs Page: [1]
Login
Message << Older Topic   Newer Topic >>
Viewing, extracting web proxy logs - 19.Jan.2006 8:44:50 PM   
jmann

 

Posts: 2
Joined: 19.Jan.2006
Status: offline
I need to be able to extract or at least be able to view daily web proxy logs. Basically the only info I need is the username, time, website accessed. Does anyone know of a way to extract this into a report?

I used to use GFI's Web Monitor, The new one for ISA 2004 however does not have a free version
Post #: 1
RE: Viewing, extracting web proxy logs - 22.Jan.2006 4:35:40 PM   
tshinder

 

Posts: 50013
Joined: 10.Jan.2001
From: Texas
Status: offline
Hi J,

How about using the ISA firewall's firewall log query viewer?

HTH,
Tom

_____________________________

Thomas W Shinder, M.D.

(in reply to jmann)
Post #: 2
RE: Viewing, extracting web proxy logs - 22.Jan.2006 5:28:04 PM   
LLigetfa

 

Posts: 2187
Joined: 10.Aug.2004
From: fort frances.on.ca
Status: offline
If you want raw text, Microsoft released a utilty that will extract your MSDE logs to text.

_____________________________

The School of Hard Knocks is a mean teacher. She gives the exam before the lesson.

(in reply to tshinder)
Post #: 3
RE: Viewing, extracting web proxy logs - 22.Jan.2006 5:45:58 PM   
elmajdal

 

Posts: 6022
Joined: 16.Sep.2004
From: Lebanese in Kuwait
Status: offline
as LLigetfa said,

here is the link of it:
http://www.microsoft.com/downloads/details.aspx?FamilyId=A60A09A0-E4AD-47C7-9961-5E22E65CA986&displaylang=en

(in reply to LLigetfa)
Post #: 4
RE: Viewing, extracting web proxy logs - 23.Jan.2006 5:26:03 PM   
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 "

(in reply to elmajdal)
Post #: 5
RE: Viewing, extracting web proxy logs - 24.Jan.2006 2:49:20 PM   
tshinder

 

Posts: 50013
Joined: 10.Jan.2001
From: Texas
Status: offline
Hi Jason,

Great!
Thanks!
Tom

_____________________________

Thomas W Shinder, M.D.

(in reply to J.F.)
Post #: 6

Page:   [1] << Older Topic    Newer Topic >>
All Forums >> [ISA Server 2004 Firewall] >> Logging and Reporting >> Viewing, extracting web proxy logs Page: [1]
Jump to:

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
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts