<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=us-ascii"><meta name=Generator content="Microsoft Word 14 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 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;}
span.EmailStyle17
{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.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></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>
<BR>
<BR>
<div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Hi Gaurav,<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'>Try following code and change as per your requirement (I used it to capture uniqued IP address)<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Create empty file "/tmp/out_log” before starting, so that values will be stored and can be used even after log rotation.<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>#!/usr/bin/perl<o:p></o:p></p><p class=MsoNormal>use File::Copy qw(copy);<o:p></o:p></p><p class=MsoNormal>#Search unique IP address for DNS queries from query log.<o:p></o:p></p><p class=MsoNormal>my @val;<o:p></o:p></p><p class=MsoNormal>my $i=0;<o:p></o:p></p><p class=MsoNormal>#Read previously read IPs from file.<o:p></o:p></p><p class=MsoNormal>open (iplog, "/tmp/out_log") or die "File doesn't exists";<o:p></o:p></p><p class=MsoNormal>while (my $ips = <iplog>)<o:p></o:p></p><p class=MsoNormal>{<o:p></o:p></p><p class=MsoNormal>push (@ip_addr, $ips);<o:p></o:p></p><p class=MsoNormal>}<o:p></o:p></p><p class=MsoNormal>close(iplog);<o:p></o:p></p><p class=MsoNormal>while ($i <20)<o:p></o:p></p><p class=MsoNormal>{<o:p></o:p></p><p class=MsoNormal>open (OUT_LOG, '>/tmp/out_log');<o:p></o:p></p><p class=MsoNormal>#Copy all the logs to a separate location<o:p></o:p></p><p class=MsoNormal>copy("/var/named/chroot/var/log/named.log.$i","/tmp/named.log.$i");<o:p></o:p></p><p class=MsoNormal>#Read them one by one and capture values<o:p></o:p></p><p class=MsoNormal>open(logs, "/tmp/Non-anycast/logs/named.log.$i") or die "File: named.log.$i doesnot exist";<o:p></o:p></p><p class=MsoNormal>while (my $line = <logs>)<o:p></o:p></p><p class=MsoNormal>{<o:p></o:p></p><p class=MsoNormal> chomp;<o:p></o:p></p><p class=MsoNormal> ($dt,$tm,$clt, $ip_port,$query, $tt ) = split(/\ /,$line);<o:p></o:p></p><p class=MsoNormal> ($ip,$port) = split(/\#/, $ip_port);<o:p></o:p></p><p class=MsoNormal> push (@ip_addr, $ip);<o:p></o:p></p><p class=MsoNormal> }<o:p></o:p></p><p class=MsoNormal>close (logs);<o:p></o:p></p><p class=MsoNormal>$i=$i+1;<o:p></o:p></p><p class=MsoNormal>}<o:p></o:p></p><p class=MsoNormal>#Get unique IPs from the array (previous logs and current logs)<o:p></o:p></p><p class=MsoNormal>my @unique_ip = do { my %seen; grep { !$seen{$_}++ } @ip_addr };<o:p></o:p></p><p class=MsoNormal>@val = sort (@unique_ip);<o:p></o:p></p><p class=MsoNormal>print " " . $#val . " \n" ;<o:p></o:p></p><p class=MsoNormal>#Write it to the file again for next time.<o:p></o:p></p><p class=MsoNormal>print OUT_LOG "@val ";<o:p></o:p></p><p class=MsoNormal>#end of script<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'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Regards,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Naveen<o:p></o:p></span></p><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> bind-users-bounces+naveen.kumar=cw.com@lists.isc.org [mailto:bind-users-bounces+naveen.kumar=cw.com@lists.isc.org] <b>On Behalf Of </b>Mike Hale<br><b>Sent:</b> Saturday, December 29, 2012 10:37 PM<br><b>To:</b> Gaurav Kansal<br><b>Cc:</b> bind-users@lists.isc.org<br><b>Subject:</b> Re: open-source tool for filter out stats from dns logs<o:p></o:p></span></p><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>I looked for one a while back and couldn't find any good ones. All the ones I saw simply used the named.stats file, which wasn't enough.<o:p></o:p></p></div><div><p class=MsoNormal> <o:p></o:p></p></div><div><p class=MsoNormal>I ended up exporting all query logs to a backend server and coding a script that imported the queries into MySQL, which I then accessed with a php app (which, after having 7 million queries in the db, takes a good three minutes to load :/ ). <o:p></o:p></p></div><div><p class=MsoNormal> <o:p></o:p></p></div></div><div><p class=MsoNormal style='margin-bottom:12.0pt'><o:p> </o:p></p><div><p class=MsoNormal>On Sat, Dec 29, 2012 at 8:56 AM, Gaurav Kansal <<a href="mailto:gaurav.kansal@nic.in" target="_blank">gaurav.kansal@nic.in</a>> wrote:<o:p></o:p></p><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Dear Team,<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'>I want to collect some stats (such as most which zone has queried most, which DNS Server queries us most and so on) based on my DNS Logs.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Is there any open-source tool available to filter out these statistics using my dns logs. <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'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='color:#76923C'>Thanks and Regards,</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='color:#76923C'>Gaurav Kansal</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='color:#76923C'>Mob – 9910118448</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='color:#76923C'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='color:#76923C'>Happy New Year 2013.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='color:gray'>IPv4 is Over,</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='color:gray'>Are your ready for new Network.</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'> <o:p></o:p></p></div></div><p class=MsoNormal><br>_______________________________________________<br>Please visit <a href="https://lists.isc.org/mailman/listinfo/bind-users" target="_blank">https://lists.isc.org/mailman/listinfo/bind-users</a> to unsubscribe from this list<br><br>bind-users mailing list<br><a href="mailto:bind-users@lists.isc.org">bind-users@lists.isc.org</a><br><a href="https://lists.isc.org/mailman/listinfo/bind-users" target="_blank">https://lists.isc.org/mailman/listinfo/bind-users</a><o:p></o:p></p></div><p class=MsoNormal><br><br clear=all><br>-- <br>09 F9 11 02 9D 74 E3 5B D8 41 56 C5 63 56 88 C0 <o:p></o:p></p></div></div>
<BR>
<BR>
<hr width="100%">
<font face="arial" size=2>
This e-mail has been scanned for viruses by the Cable&Wireless Worldwide e-mail security system. For more information on a proactive managed e-mail secure service, visit http://www.cw.com/managed-exchange
<br />
<br />
The information contained in this e-mail is confidential and may also be subject to legal privilege. It is intended only for the recipient(s) named above. If you are not named above as a recipient, you must not read, copy, disclose, forward or otherwise use the information contained in this email. If you have received this e-mail in error, please notify the sender (whose contact details are above) immediately by reply e-mail and delete the message and any attachments without retaining any copies.
<br />
<br />
Cable & Wireless Worldwide plc
Registered in England and Wales. Company Number 07029206
Registered office: Waterside House, Longshot Lane, Bracknell, Berkshire, RG12 1XL, England
</font>
<hr width="100%">
</body></html>