Efficient & Effective analysis of SafeSquid-SWG Logs

[aashish97]

Hello All,

I have been using SafeSquid-SWG for at least a year now, but I still face problems analyzing SafeSquid-SWG Logs.

SafeSquid contains a lot of different logs with different formats. Also, the main logging that I am interested in is the Extended Log (similar to access log of Squid Proxy Server), It includes a lot more important information other than a typical access log file of Squid Proxy Server.

-------------------------------------------------------
SafeSquid-SWG Extended Log Format:
-------------------------------------------------------
"record_id" "client_id" "request_id" "date_time" "elapsed_time" "status" "size" "upload" "download" "bypassed" "client_ip" "username" "method" "url" "http_referer" "useragent""mime" "filter_name" "filtering_reason" "interface" "cachecode" "peercode" "peer" "request_host" "request_tld" "referer_host" "referer_tld" "range" "time_profiles" "user_groups" "request_profiles" "application_signatures" "categories" "response_profiles" "upload_content_types" "download_content_types" "profiles"

--------------------------------------------------------------
SafeSquid-SWG Extended Log Sample Data:
---------------------------------------------------------------
"158166470831RXQGu" "3" "1" "14/Feb/2020:12:48:29" "276" "200" "0" "0" "0" "FALSE" "192.168.0.17" "anonymous@192.168.0.17" "CONNECT" "connect://clients1.google.com:443/" "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.100 Safari/537.36" "-" "-" "-" "192.168.248.190:8080" "UNSPECIFIED" "NONE" "-" "clients1.google.com" "google.com" "-""-" "0" "BUSINESS HOURS,YOUTUBE STREAMING HOURS" "SafeSquid Admin Group With No Auth" "Chrome,Internet Browser,Google,CLIENTS4.GOOGLE.COM,GMAIL WEB APP ALL ADDED" "Chrome,Internet Browser,Google" "Search Engines & Portals" "" "-" "-" "ENFORCE LOW PRIVACY LEVEL,PERMIT PERSONAL GOOGLE ACCOUNTS"

Why I am Not Using Excel to analyze the logs?
Depending upon the use, the log files exceeds the size of around 2GB and becomes almost impossible to do analysis using MS Excel.

Why don't I try Open Source Log Analyzer Tools like calamaris, sarg ... etc?

A Quick Answer :

• Most Opensource log analyzers (specifically Proxy Log Analyzers) accepts the Squid Access log format.

• Development, Maintenance, and Support are no longer done by the Group

• Customization is also very tedious

Detailed Explanation
Yes, currently, I am using calamaris as well as sarg to get my custom reports generated.
But both their drawbacks,

----------------
Calamaris
----------------
Calamaris is like a Summary Reporting which supports Squid Format, Therefore in order to generate Calamris Reports, I need to first convert the Extended log into Access Log and then run Calamaris. which becomes tedious & time-consuming as the log file groups and also the chances of error are high
Calamaris reports do not provide the information that I am looking for. Since I need to convert Extended Logs in to access Log the important fields provided by SafeSquid-SWG are removed, and therefore the reports generated becomes meaningless.
Calamaris also have some problems and since it is no more maintained, I cannot report the problems faced.

--------
Sarg
--------
Sarg is also a problem because of the way the Reports are created, I will run out of inodes within a month.
Sarg again uses the access log, therefore, I have t again convert the logs and rest is the same as calamaris.

---------------------------
Other Log Analyzer
---------------------------
I have also tried other log analyzers and i face the same problems as listed above

A small Conclusion
No Log analyzer that I have used till now, has provided me custom log parsing capabilities.

The solution that I am looking for :
Should be able to deliver an insight of my day to day internet activities.
briefly elaborating on the reason why the site was blocked.
I need a dashboard that can provide a good view of my internet traffic and the applications that are triggering this traffic.

[parth]

A custom web-based solution can be created. An open-source dashboard coupled with chart.js can do the job perfectly well.

The solution can be based on node.js for extensibility and flexibility. The UI of the dashboard will primarily be pure CSS and HTML with Bootstrap for a minimal and responsive experience.

Since we have the data from the logs already, chart.js can be easily manipulated to display the data in whatever format we want. The data can be taken from the log, parsed in JSON format for the library, and then rendered as per your needs.

[aashish97]

Thanks, Parth,

This sounds great, I was tired of parsing my Required Stats via the Command-Line.
It will be great if I can see that on the Dashboard, it reduces a lot of my pain.

[aashish97]

Hello Parth,

Just to help you out with the Dashboard Part.
I have a few OpenSource Reporting Modules Screenshot.

Below is the Kind of Reports provided by the Calamaris Tool. Calamris Provides Summary Reports.

All Request Domains :

image.png (79.95 KiB) Viewed 3142 times

All Users

image.png (77.11 KiB) Viewed 3142 times

Due to Restriction in Uploads, I am not able to upload the .html file of Calamaris Report
But, As you can see, the Reports do not provide any proper insight into the actual Traffic.
System Admins or Clients are more interested in Reports that provide a detailed view/insight of the Traffic.

I have few Snapshots that I am sharing with you.

[aashish97]

I am attaching a few of my aspects of viewing the Reports.

Which Includes:

Top Visited Websites by Most Users of the Network

Slide2.PNG (48.87 KiB) Viewed 3141 times

Top Websites visited the Most [No of Times Requested]

Slide3.PNG (54.25 KiB) Viewed 3141 times

Generally, most users are interested in the detailed view
A small example
No of Websites that got blocked -> the reason why they got blocked -> who was trying to access it -> When etc ......
below is a small Snapshot of a General View of Such Scenario in Reporting

A Detailed view of the Blocked Websites

Slide9.PNG (32.06 KiB) Viewed 3141 times

[aashish97]

Reporting that shows the different applications that are accessing/uploading Data over the Internet.
Applications provide their Name in the HTTP Request Headers which are extracted and logged by SafeSquid-SWG,
few applications might not provide such details.
They can be classified as NOT_PROVIDED

Below details show These applications in 2 Different ways.

Slide7.PNG (46.74 KiB) Viewed 3141 times

Slide8.PNG (43.57 KiB) Viewed 3141 times

[aashish97]

SafeSquid Have Many Content Filters, which will block content depending on the kind of policy designed by the SysAdmin.
A Specific VIew that shows what all things got Filtered by the Respective Content Filters is very helpful.

There are 4 Feilds that explains
Who -> Where -> What -> How Much

Again these Stats are provided in 2 Formats, as shown below.

Slide10.PNG (22.17 KiB) Viewed 3141 times

Slide11.PNG (22.14 KiB) Viewed 3141 times

[aashish97]

Below are Few More Content Filter BLocking Stats:

Slide12.PNG (24.6 KiB) Viewed 3141 times

Slide13.PNG (25.6 KiB) Viewed 3141 times

Slide14.PNG (24.83 KiB) Viewed 3141 times

[aashish97]

Slide14.PNG[/attachment ][attachment=0]Slide15.PNG

[aashish97]

Slide4.PNG (49.07 KiB) Viewed 3141 times

Slide5.PNG (54.86 KiB) Viewed 3141 times

Slide6.PNG (49.11 KiB) Viewed 3141 times