UTM best practise guide for strict webfiltering

Based on the previous framework, below is the query to identify all sites that were blocked because of SSL Certificate problems:

I export the query results to Excel, then fill in the blank contact column with an email address (obtained from WHOIS).
I often use an SSL Checker to determine why the site failed, and put that reason into the blank "Reason" column.
Once the Excel file is populated, I use Word mail-merge to send the emails.   I average about 10-15 problem sites per day.;

SELECT Max(T.EventTime) AS LastSeen, 

"" AS Reason,
"" AS Contact,
ParseURL([T].[ItmUrl],"D") AS FQDN,
T.ItmDstip,
T.ItmReferer,
Count(T.EventTime) AS Events

FROM ParsedFile AS T
WHERE (((T.ItmError)="") AND ((T.ItmId)="0002"))
GROUP BY ParseURL([T].[ItmUrl],"D"), T.ItmDstip, T.ItmReferer
ORDER BY ParseURL([T].[ItmUrl],"D"), T.ItmReferer;

I made this Access database as per your instructions and it works wonderfully for pulling logs apart. Thanks for taking the time to share it with us. I noticed that the forum is replacing [ W ] with a icon of a withered rose. So to get around that click "Reply" to the post with the icon in it and it gives the original text.

Advanced Topics for using Microsoft Access for Log Parsing

HANDLING MULTIPLE DAYS

My example does everything based on parsing of a text file.  UTM generates one log file per day.   If you need to merge multiple days, you can combine the text files suing the comand prompt copy command:

copy file1.txt+file2.txt+file3.txt  combinedfile.txt

or

copy file*.txt combinedfile.txt

However, Access only supports text files of 2GB or less.   My log files are running about 1GB per weekday, so combining has limited usefulness.  

LOADING THE DATA INTO A SQL DATABASSE

One option is to load the parsed data into a SQL database, so that you can do queries across any time period.   I have found that loading the data takes a lot of time, and woory about the rapid file growth that would be involved.   As a result, I have a SQL database available but I am not actively populating it.   Omitting references to an SQL database also makes my examples easier for getting started.

FILES OVER 2GB

In large environments, your log file might be over 2GB for a single day.  If that happens, you will have to split the log file. into sections of 2GB or less.   I recently had a problem like this and opted to split the file by putting alternate lines into two different files, so I am posting it as a starter.   You will probably want to keep all log entries in time order, so you would probably want to use a counter to control when the file changed, rather than alternating, and you might even need to extend the logic to split into more than 2 files, depending on the size of your logs.   This script was created as a text file with .vbs extension, and then executed with cscript filename.vbs.

Const ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set InFile = objFSO.OpenTextFile("infile.txt",, True)
Set OutFile1 = objFSO.OpenTextFile("outfile1.txt",ForAppending, True)
Set OutFile2 = objFSO.OpenTextFile("outfile2.txt",ForAppending, True)
cntr = 0
While not Infile.AtEndOfStream
  InFile.ReadLine intext
  if cntr = 0 then
    OutFile1.WriteLine intext
    cntr = 1
  else
     OutFile2.WriteLine intext
    cntr = 0
  end if
Wend
OutFile1.Close
OutFile2.Close
Infile.Close

WARNED AND PROCEEDED

I decided that I don't care if a user is warned about a website but does not click the proceed button.  However, if they click through, then I want to ensure that the event is reviewed and that any uncategorized sites are submitted for categorization.   This was a little bit complicated because the PROCEEDED event does not have any user information included.   My conceptual logic seqeunce is:

(a) find all of the "PROCEEDED" events, and save them to a temporary table
(b) find the "WARN" event which preceded it and the ALLOW event which followed it.
(c) combine all three events into one report line.

The Access query sequence follows, which I have assembled into a Macro

1) Clean out the temporary table

DELETE TblProceeded.* FROM TblProceeded;

(Primary Key is ItmUrl, ItmSrcip, EventTime, ItmID

2) Add the Proceeded entries into the temporary table:

INSERT INTO TblProceeded ( ItmId, ItmUrl, ItmSrcip, EventTime )
SELECT PF.ItmId, PF.ItmUrl, PF.ItmSrcip, PF.EventTime
FROM ParsedFile AS PF
WHERE (((PF.ItmId)="0072"));

3) Get the related records to determine "Who did it"

INSERT INTO TblProceeded ( EventTime, ItmId, ItmSrcip, ItmUrl, ItmUser, ItmAd_domain, ItmStatuscode, ItmError, ItmCategory, ItmReputation, ItmApplication )
SELECT PF.EventTime, PF.ItmId, PF.ItmSrcip, PF.ItmUrl, PF.ItmUser, PF.ItmAd_domain, PF.ItmStatuscode, PF.ItmError, PF.ItmCategory, PF.ItmReputation, PF.ItmApplication
FROM TblProceeded INNER JOIN ParsedFile AS PF ON (TblProceeded.ItmUrl = PF.ItmUrl) AND (TblProceeded.ItmSrcip = PF.ItmSrcip)
WHERE (((PF.ItmId)<>"0072"));

4) Query to find the best pair between "01" (allow) and 72 (proceeded).   This is not run separately, it is nested in a later query.

Query name: QryTblProceeded_0172

SELECT T72.ItmUrl, T72.ItmSrcip, T72.ItmId AS ID72, T01.ItmId AS ID01, T72.EventTime, Max(T01.EventTime) AS T01Time, T01.ItmUser, T01.ItmAd_domain, T01.ItmStatuscode, T01.ItmError, T01.ItmCategory, T01.ItmReputation, T01.ItmApplication, HttpErrorCodes.HttpErrorText
FROM (TblProceeded AS T72 INNER JOIN TblProceeded AS T01 ON (T72.ItmUrl = T01.ItmUrl) AND (T72.ItmSrcip = T01.ItmSrcip)) LEFT JOIN HttpErrorCodes ON T01.ItmStatuscode = HttpErrorCodes.ItmStatuscode
GROUP BY T72.ItmUrl, T72.ItmSrcip, T72.ItmId, T01.ItmId, T72.EventTime, T01.ItmUser, T01.ItmAd_domain, T01.ItmStatuscode, T01.ItmError, T01.ItmCategory, T01.ItmReputation, T01.ItmApplication, HttpErrorCodes.HttpErrorText
HAVING (((T72.ItmId)="0072") AND ((T01.ItmId)="0001") AND ((Max(T01.EventTime))>=[T72].[EventTime]))
ORDER BY T72.ItmUrl;

5) Query to find the best pair between "71"  (warned) and "72" (proceeded).   This is also referenced by query nesting, as shown in the next step

Query name: QryTblProceeded_7172

SELECT T72.ItmUrl, T72.ItmSrcip, T72.ItmId AS ID72, T71.ItmId AS ID71, T72.EventTime, Max(T71.EventTime) AS T71Time, T71.ItmUser, T71.ItmAd_domain, T71.ItmStatuscode, T71.ItmError, T71.ItmCategory, T71.ItmReputation, T71.ItmApplication
FROM TblProceeded AS T72 INNER JOIN TblProceeded AS T71 ON (T72.ItmUrl = T71.ItmUrl) AND (T72.ItmSrcip = T71.ItmSrcip)
GROUP BY T72.ItmUrl, T72.ItmSrcip, T72.ItmId, T71.ItmId, T72.EventTime, T71.ItmUser, T71.ItmAd_domain, T71.ItmStatuscode, T71.ItmError, T71.ItmCategory, T71.ItmReputation, T71.ItmApplication
HAVING (((T72.ItmId)="0072") AND ((T71.ItmId)="0071") AND ((Max(T71.EventTime))<=[T72].[EventTime]))
ORDER BY T72.ItmUrl;

6) Combine everything together

SELECT T01.ItmUrl, T01.ItmSrcip, Format$(T01.T01Time,"yyyy-mm-dd hh:nn:ss am/pm ddd") AS PassTime, Format$(T01.EventTime,"yyyy-mm-dd hh:nn:ss am/pm ddd") AS ProceedTime, Format$(T71.T71Time,"yyyy-mm-dd hh:nn:ss am/pm ddd") AS WarnTime, T01.ItmUser, T01.ItmAd_domain, T01.ItmStatuscode, T01.ItmError, T01.ItmCategory, T01.ItmReputation, T01.ItmApplication, TblCategoryIDs.ItmCategoryname
FROM (QryTblProceeded_0172 AS T01 INNER JOIN QryTblProceeded_7172 AS T71 ON (T01.ItmUrl = T71.ItmUrl) AND (T01.ItmSrcip = T71.ItmSrcip) AND (T01.EventTime = T71.EventTime)) LEFT JOIN TblCategoryIDs ON T01.ItmCategory = TblCategoryIDs.ItmCategory;

Sorry about the wilted rose.  Thanks for exmplaining the workaround.

UPDATE:  I have begun finding discrepancies between the UTM and TrustedSource classifications.

Sophos support now says that I need to use this URL:   https://www.sophos.com/en-us/threat-center/reassessment-request.asp instead of (or in addition to) www.trustedsource.org.   (The related Sophos knowledgebase entry is Article ID: 119440)   

The Sophos site is a big disappointment, because it is much less efficient to use for multiple submission:   only one URL at a time, no go-back button, no memory of you from one submit to the next.  

I also suggested that Sophos reconsider the references to www.trustedsource.org in section 9.1 of the UTM admin guide.   Technically, the reference is to reputation issues rather than categorization, so maybe they use trustedsource for reputation only.

I am finding quite a few sites that have port 443 open, even though they do not intend to be an HTTPS site, and have not installed a certificate.   These systems are hosted on akamai and have a default akamai certificate installed.   Since the port is open, the google webcrawler will find it and index the site using https.   When our users do a google search, they will be blocked when they follow the link.  

The site owner generally does not care when I point out the problem.  I suspect that the search result will be slow to disappear from google even if it is fixed, which can only increase the reluctance of the site owner to make the change.

Thanks for the links to the best practice guides. Highly appreciated.