<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Arial Rounded MT Bold";
panose-1:2 15 7 4 3 5 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:918052900;
mso-list-type:hybrid;
mso-list-template-ids:538244116 -2049127348 67698691 67698693 67698689 67698691 67698693 67698689 67698691 67698693;}
@list l0:level1
{mso-level-start-at:0;
mso-level-number-format:bullet;
mso-level-text:-;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Calibri",sans-serif;
mso-fareast-font-family:Calibri;}
@list l0:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l0:level3
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
@list l0:level4
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Symbol;}
@list l0:level5
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l0:level6
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
@list l0:level7
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Symbol;}
@list l0:level8
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l0:level9
{mso-level-number-format:bullet;
mso-level-text:;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I updated the repo: <a href="http://gogs.ngtech.co.il/elicro/squid-sql-logger">http://gogs.ngtech.co.il/elicro/squid-sql-logger</a><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>The additions are:<o:p></o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><span style='mso-list:Ignore'>-<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span dir=LTR></span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>GoLang mysql logging service source code.<o:p></o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><span style='mso-list:Ignore'>-<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span dir=LTR></span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Static pre-compiled binaries for(linux, windows, all bsd, Darwin, linux_arm.. , linux_mips...).<o:p></o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo1'><![if !supportLists]><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><span style='mso-list:Ignore'>-<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span dir=LTR></span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Installation instructions for the pre-compiled binaries for linux_amd64 version.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Tested to work under very high load.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>There is one improvement I had in mind but I will add it later on.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><br>All The Bests,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Eliezer<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Arial Rounded MT Bold",sans-serif;color:#1F497D'>----<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Arial Rounded MT Bold",sans-serif;color:#1F497D'><a href="http://ngtech.co.il/lmgtfy/"><span style='color:#0563C1'>Eliezer Croitoru</span></a><br>Linux System Administrator<br>Mobile: +972-5-28704261<br>Email: eliezer@ngtech.co.il<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><img border=0 width=183 height=69 style='width:1.9062in;height:.7187in' id="Picture_x0020_1" src="cid:image003.png@01D3EC8F.49469FC0"><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> Alex K <rightkicktech@gmail.com> <br><b>Sent:</b> Sunday, May 13, 2018 14:23<br><b>To:</b> Eliezer Croitoru <eliezer@ngtech.co.il><br><b>Cc:</b> squid-users@lists.squid-cache.org<br><b>Subject:</b> Re: [squid-users] Collecting squid logs to DB<o:p></o:p></span></p><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>Thanx Eliezer and Amos for the feedback. I just saw the logformat directive and will experiment with that.<o:p></o:p></p><div><p class=MsoNormal>Yes, I have a small group of users (up to 30 - 40 devices) but the hardware is a relatively small appliance (4G RAM, 4 cores 2GHz, SSD).<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>Alex<o:p></o:p></p></div></div><p class=MsoNormal style='margin-bottom:12.0pt'><o:p> </o:p></p><div><div><p class=MsoNormal>On Sun, May 13, 2018, 11:37 Eliezer Croitoru <<a href="mailto:eliezer@ngtech.co.il">eliezer@ngtech.co.il</a>> wrote:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>To lose the stress on the DB you can use a custom format as Amos suggested but..</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I think that when you will define and write what you want to log exactly you will get what you need and want.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>The general squid access log is pretty lose and I believe that with these days hardware the difference will only be seen on systems with thousands or millions of clients requests.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>If this is a small place it’s not required.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>All The Bests,</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Eliezer</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Arial Rounded MT Bold",sans-serif;color:#1F497D'>----</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Arial Rounded MT Bold",sans-serif;color:#0563C1'>Eliezer Croitoru</span><span style='font-size:11.0pt;font-family:"Arial Rounded MT Bold",sans-serif;color:#1F497D'><br>Linux System Administrator<br>Mobile: +972-5-28704261<br>Email: <a href="mailto:eliezer@ngtech.co.il">eliezer@ngtech.co.il</a></span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> Alex K <<a href="mailto:rightkicktech@gmail.com">rightkicktech@gmail.com</a>> <br><b>Sent:</b> Sunday, May 13, 2018 01:56<br><b>To:</b> Eliezer Croitoru <<a href="mailto:eliezer@ngtech.co.il">eliezer@ngtech.co.il</a>><br><b>Cc:</b> <a href="mailto:squid-users@lists.squid-cache.org">squid-users@lists.squid-cache.org</a><br><b>Subject:</b> Re: [squid-users] Collecting squid logs to DB</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;margin-bottom:12.0pt'>+++ Including list +++<o:p></o:p></p><div><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;margin-bottom:12.0pt'>Hi Eliezer, <o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;margin-bottom:12.0pt'>I have used the following lines to instruct squid to log at mariadb: <br><span style='font-family:"Courier New"'><br>logfile_daemon /usr/lib/squid/log_db_daemon<br>access_log daemon:/127.0.0.1/squid_log/access_log/squid/squid squid</span><o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Through testing it seems that sometimes squid is not logging anything. I don't know why. After a restart it seems to unblock and write to DB. <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>The access_log table is currently InnoDB and I am wondering if MyISAM will behave better. <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;margin-bottom:12.0pt'>I would prefer if I could have real time access log. My scenario is that when a user disconnects from squid, an aggregated report of the sites that the user browsed will be available under some web portal where the user has access. Usually there will be up to 20 users connected concurrently so I have to check if this approach is scalable. If this approach is not stable then I might go with log parsing (perhaps logstash or some custom parser) which will parse and generate an aggregated report once per hour or day. <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Is there a way I format the log and pipe to DB only some interesting fields in order to lessen the stress to DB?<o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>On Sun, May 13, 2018 at 1:25 AM, Eliezer Croitoru <<a href="mailto:eliezer@ngtech.co.il">eliezer@ngtech.co.il</a>> wrote:<o:p></o:p></p><blockquote style='border:none;border-right:solid #CCCCCC 1.0pt;padding:0in 0in 0in 0in;margin-left:4.8pt;margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Hey Alex,</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>How did you used to log into the DB? What configuration lines have you used?</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Also what log format have you used?</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Is it important to have realtime data in the DB or a periodic parsing is also an option?</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Eliezer</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Arial Rounded MT Bold",sans-serif;color:#1F497D'>----</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Arial Rounded MT Bold",sans-serif;color:#0563C1'>Eliezer Croitoru</span><span style='font-size:11.0pt;font-family:"Arial Rounded MT Bold",sans-serif;color:#1F497D'><br>Linux System Administrator<br>Mobile: +972-5-28704261<br>Email: <a href="mailto:eliezer@ngtech.co.il">eliezer@ngtech.co.il</a></span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> squid-users <<a href="mailto:squid-users-bounces@lists.squid-cache.org">squid-users-bounces@lists.squid-cache.org</a>> <b>On Behalf Of </b>Alex K<br><b>Sent:</b> Saturday, May 5, 2018 01:20<br><b>To:</b> <a href="mailto:squid-users@lists.squid-cache.org">squid-users@lists.squid-cache.org</a><br><b>Subject:</b> [squid-users] Collecting squid logs to DB</span><o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><div><div><div><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;margin-bottom:12.0pt'>Hi all, <o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I had a previous setup on Debian 7 with squid and I was using mysar to collect squid logs and store them to DB and provide some browsing report at the end of the day. <o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;margin-bottom:12.0pt'>Now at Debian 9, trying to upgrade the whole setup, I see that mysar does not compile. <o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Checking around I found mysar-ng but this has compilation issues on Debian 9 also. <o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;margin-bottom:12.0pt'>Do you suggest any tool that does this job? Does squid support logging to DB natively? (I am using mysql/mariadb)<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Some other tool I stumbled on is <a href="https://github.com/paranormal/blooper">https://github.com/paranormal/blooper</a>. <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Thanx a bunch,<o:p></o:p></p></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Alex<o:p></o:p></p></div></div></div></div></div></blockquote></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div></div></div></blockquote></div></div></div></div></body></html>