######### dns ########
# Never forward plain names (without a dot or domain part)
domain-needed
# Never forward addresses in the non-routed address spaces
bogus-priv
# dont read resolv.conf   use the defined servers instead
no-resolv
server=8.8.8.8
server=8.8.4.4
# increase dns cache from 512 to 4096
cache-size=4096

######### dhcp ##########
# Add local-only domains here, queries in these domains are answered
# from /etc/hosts or DHCP only
local=/home/
# Set this (and domain: see below) if you want to have a domain
# automatically added to simple names in a hosts-file.
expand-hosts
# adds my localdomain to each dhcp host
domain=home
# my private dhcp range + subnetmask + 14d lease time
dhcp-range=192.168.0.100,192.168.0.254,255.255.255.0,8h
# set route to my local network router
dhcp-option=option:router,192.168.0.1
#windows 7 float fix
#http://brielle.sosdg.org/archives/522-Windows-7-flooding-DHCP-server-with-DHCPINFORM-messages.html
dhcp-option=252,"\n"

###### logging ############
# own logfile
log-facility=/var/log/dnsmasq.log
log-async
# log dhcp infos
# log-dhcp
# debugging dns
log-queries

/var/log/dnsmasq.log
 {
         monthly
         missingok
         notifempty
         delaycompress
         sharedscripts
         postrotate
                 [ ! -f /var/run/dnsmasq/dnsmasq.pid ] || kill -USR2 `cat /var/run/dnsmasq/dnsmasq.pid`
         endscript
         create 0644 dnsmasq root
 }

<VirtualHost *:80>
 	# The ServerName directive sets the request scheme, hostname and port that
 	# the server uses to identify itself. This is used when creating
 	# redirection URLs. In the context of virtual hosts, the ServerName
 	# specifies what hostname must appear in the request's Host: header to
 	# match this virtual host. For the default virtual host (this file) this
 	# value is not decisive as it is used as a last resort host regardless.
 	# However, you must set it for any further virtual host explicitly.
 	#ServerName www.example.com
 
 	ServerAdmin webmaster@localhost
 	DirectoryIndex 1px.gif
 	DocumentRoot /var/www/html
 
 	# Available loglevels: trace8, ..., trace1, debug, info, notice, warn,
 	# error, crit, alert, emerg.
 	# It is also possible to configure the loglevel for particular
 	# modules, e.g.
 	#LogLevel info ssl:warn
 
 	ErrorLog ${APACHE_LOG_DIR}/error.log
 	CustomLog ${APACHE_LOG_DIR}/access.log combined
 
 	# For most configuration files from conf-available/, which are
 	# enabled or disabled at a global level, it is possible to
 	# include a line for only one particular virtual host. For example the
 	# following line enables the CGI configuration for this host only
 	# after it has been globally disabled with "a2disconf".
 	#Include conf-available/serve-cgi-bin.conf
 </VirtualHost>

-- Total
select count(*)
from DNS_LOG_LINES
order by LOG_TIME desc
go

-- Group by query type
SELECT QUERY_TYPE, count(*) as TOTAL
FROM DNS_LOG_LINES
GROUP BY QUERY_TYPE
ORDER BY 2 DESC
go

-- Check top requesters
select QUERY_TARGET, count(*) as TOTAL
from DNS_LOG_LINES
where QUERY_TYPE = 'query[A]'
group by QUERY_TARGET
order by 2 desc
go

-- Check top requested domains
SELECT QUERY_DOMAIN, count(*) as TOTAL
FROM DNS_LOG_LINES
GROUP BY QUERY_DOMAIN
ORDER BY 2 DESC
go

-- Check top advertisers filtered
select QUERY_DOMAIN, count(*) as TOTAL
from DNS_LOG_LINES
where QUERY_TARGET = '192.168.0.4'
group by QUERY_DOMAIN
order by 2 desc
//limit 20
go

-- Count advertisers
select count(*) as TOTAL
from DNS_LOG_LINES
where QUERY_TARGET = '192.168.0.4'
go

-- Count advertisers per hour
select FROM_UNIXTIME(LOG_TIME/1000, '%d/%m/%Y %H') as LOG_DATE, count(*) as TOTAL
from DNS_LOG_LINES
where QUERY_TARGET = '192.168.0.4'
and STR_TO_DATE(FROM_UNIXTIME(LOG_TIME/1000, '%d/%m/%Y'), '%d/%m/%Y') > CURRENT_DATE - INTERVAL 24 hour
group by FROM_UNIXTIME(LOG_TIME/1000, '%d/%m/%Y %H')
order by 1 desc
go

-- Count advertisers per day
select FROM_UNIXTIME(LOG_TIME/1000, '%d/%m/%Y') as LOG_DATE, count(*) as TOTAL
from DNS_LOG_LINES
where QUERY_TARGET = '192.168.0.4'
and STR_TO_DATE(FROM_UNIXTIME(LOG_TIME/1000, '%d/%m/%Y'), '%d/%m/%Y') > CURRENT_DATE - INTERVAL 30 DAY
group by FROM_UNIXTIME(LOG_TIME/1000, '%d/%m/%Y')
order by 1 desc
go

-- Table size
SELECT 
table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM INFORMATION_SCHEMA.TABLES 
WHERE table_schema = "adtrap"
go