TFTP and Emulate view
I enhance a bit Markus' SQL query with
I enhance a bit Markus' SQL query with
(
SELECT
COUNT(*)
FROM
(
SELECT
MIN(a.download) AS download
FROM
downloads AS a
JOIN
connections AS b ON(a.connection = b.connection)
GROUP BY
a.download_md5_hash
HAVING
strftime('%Y-%m-%d',MIN(b.connection_timestamp),'unixepoch','localtime')
= strftime('%Y-%m-%d',connections.connection_timestamp,'unixepoch','localtime')
) AS newdownloads
NATURAL JOIN downloads
WHERE
download_url LIKE 'tftp://%'
)AS uniq_this_day_via_tftp,
(
SELECT
COUNT(*)
FROM
(
SELECT
MIN(a.download) AS download
FROM
downloads AS a
JOIN
connections AS b ON(a.connection = b.connection)
GROUP BY
a.download_md5_hash
HAVING
strftime('%Y-%m-%d',MIN(b.connection_timestamp),'unixepoch','localtime')
= strftime('%Y-%m-%d',connections.connection_timestamp,'unixepoch','localtime')
) AS newdownloads
NATURAL JOIN downloads
WHERE
download_url LIKE 'emulate://%'
)AS uniq_this_day_via_emulate
now the query became this;
2010-06-20|142|15|129|13|13|0|0
2010-06-19|127|16|116|16|16|0|0
2010-06-18|111|15|100|13|13|0|0
2010-06-17|96|25|87|20|19|0|1
2010-06-16|71|4|67|3|2|1|0
2010-06-15|67|2|64|2|2|0|0
2010-06-14|65|8|62|8|8|0|0
2010-06-12|57|8|54|8|8|0|0
2010-06-09|49|4|46|4|4|0|0
2010-06-08|45|7|42|6|6|0|0
2010-06-06|38|7|36|7|7|0|0
2010-06-05|31|8|29|8|8|0|0
2010-06-03|23|3|21|3|3|0|0
2010-05-31|20|2|18|2|2|0|0
2010-05-26|18|4|16|2|2|0|0
2010-05-25|14|2|14|2|2|0|0
2010-05-23|12|4|12|4|4|0|0
2010-05-22|8|3|8|3|3|0|0
2010-05-20|5|5|5|5|5|0|0
and the plot output became this;
with each emulate and TFTP uniq binaries both equal to one, see how low the plot line is :-\
Comments