def fetch_reports(self):
self.logger.debug("entered fetch_reports()")
self.cursor.execute("""
SELECT scan_id
FROM virus_total_submissions
WHERE json is NULL and
(LOCALTIMESTAMP - submit_time) > '5 minutes' and
(LOCALTIMESTAMP - submit_time) < '3 days'
ORDER BY submit_time ASC""")
scan_ids = [row[0] for row in self.cursor.fetchall()]
self.logger.debug("fetch_reports(): %s scan reports to be fetched",
len(scan_ids))
query_count = 0
for scan_id in scan_ids:
if query_count == self.QUERY_RATE_LIMIT:
self.logger.debug("Query limit reached. Sleeping for a min.")
time.sleep(self.ONE_MIN)
query_count = 0
query_count += 1
try:
json = vt_api.get_vt_report(scan_id)
if not json:
self.logger.debug("No json")
continue
report = simplejson.loads(json)
# Sometimes, we get the old reports wrongly
if (report["response_code"] != 1) or (report['scan_id'] !=
scan_id):
self.logger.debug("Response code %s for scan_id %s" %
(report["response_code"], scan_id))
continue
self.update_table_with_report(scan_id, report, json)
except Exception as e:
self.logger.exception(
"Error in fetching report for scan_id %s: %s" %
(scan_id, e))
continue
def fetch_reports(self):
self.logger.debug("entered fetch_reports()")
self.cursor.execute("""
SELECT scan_id
FROM virus_total_submissions
WHERE json is NULL and
(LOCALTIMESTAMP - submit_time) > '5 minutes' and
(LOCALTIMESTAMP - submit_time) < '3 days'
ORDER BY submit_time ASC""")
scan_ids = [row[0] for row in self.cursor.fetchall()]
self.logger.debug("fetch_reports(): %s scan reports to be fetched",
len(scan_ids))
query_count = 0
for scan_id in scan_ids:
if query_count == self.QUERY_RATE_LIMIT:
self.logger.debug(
"Query limit reached. Sleeping for a min.")
time.sleep(self.ONE_MIN)
query_count = 0
query_count += 1
try:
json = vt_api.get_vt_report(scan_id)
if not json:
self.logger.debug("No json")
continue
report = simplejson.loads(json)
# Sometimes, we get the old reports wrongly
if (report["response_code"] != 1) or (
report['scan_id'] != scan_id):
self.logger.debug("Response code %s for scan_id %s" %
(report["response_code"], scan_id))
continue
self.update_table_with_report(scan_id, report, json)
except Exception as e:
self.logger.exception(
"Error in fetching report for scan_id %s: %s" % (scan_id, e))
continue
def db_virus_total(dump_id):
logging.config.fileConfig(LOG_CONF_FILE)
logger = logging.getLogger("amico_logger")
util.setup_socks()
conn = util.connect_to_db()
cursor = conn.cursor()
# Exit if this sha1 has been queried in the past VT_QUERY_INTERVAL period
prev_query_time = datetime(MINYEAR, 1, 1, 0, 0, 0, 0)
time_now = datetime.now()
cursor.execute(
"""
SELECT sha1, md5
FROM pe_dumps
WHERE dump_id = %s""", (dump_id, ))
(sha1, md5) = cursor.fetchone()
try:
cursor.execute(
"SELECT query_time, vt_id FROM virus_total_scans "
"WHERE sha1 = %s "
"ORDER by query_time DESC", (sha1, ))
res = cursor.fetchone()
if res:
prev_query_time = res[0]
vt_id = res[1]
except:
print "sha1:%s no previous VT query" % (sha1, )
pass
vt_query_period = timedelta(days=VT_QUERY_INTERVAL)
if (time_now - prev_query_time) < vt_query_period:
print "sha1:%s has been queried recently. Skipping..." % (sha1, )
cursor.execute(
"""
INSERT INTO ped_vts_mapping (dump_id, vt_id)
VALUES (%s, %s)""", (dump_id, vt_id))
conn.close()
return
tries = 0
success = False
while tries < MAX_TRIES:
try:
tries += 1
json = vt_api.get_vt_report(md5)
if not json:
continue
report = simplejson.loads(json)
if report["response_code"] == 1:
insert_report(cursor, report, sha1, md5, json, dump_id)
success = True
break
elif report["response_code"] == 0:
cursor.execute(
"""
INSERT INTO virus_total_scans(sha1, md5, query_time)
VALUES (%s, %s, CLOCK_TIMESTAMP())
RETURNING vt_id
""", (sha1, md5))
vt_id = cursor.fetchone()[0]
cursor.execute(
"""
INSERT INTO ped_vts_mapping (dump_id, vt_id)
VALUES (%s, %s)""", (dump_id, vt_id))
print "Virus Total: No scan report exists in the VT database"
success = True
break
else:
logger.exception("Unknown response code! %s" %
(report["response_code"], ))
time.sleep(1)
except Exception as e:
print e
logger.exception(
"Try %s. Error in fetching report for md5 %s: %s" %
(tries, md5, e))
time.sleep(5)
if not success:
cursor.execute(
"""
INSERT INTO ped_vts_mapping (dump_id)
VALUES (%s)""", (dump_id, ))
logger.warning("Giving up on dump_id: %s's VT report" % (dump_id, ))
cursor.close()
conn.close()
def db_virus_total(dump_id):
logging.config.fileConfig(LOG_CONF_FILE)
logger = logging.getLogger("amico_logger")
util.setup_socks()
conn = util.connect_to_db()
cursor = conn.cursor()
# Exit if this sha1 has been queried in the past VT_QUERY_INTERVAL period
prev_query_time = datetime(MINYEAR, 1, 1, 0, 0, 0, 0)
time_now = datetime.now()
cursor.execute("""
SELECT sha1, md5
FROM pe_dumps
WHERE dump_id = %s""",
(dump_id,))
(sha1, md5) = cursor.fetchone()
try:
cursor.execute("SELECT query_time, vt_id FROM virus_total_scans "
"WHERE sha1 = %s "
"ORDER by query_time DESC", (sha1,))
res = cursor.fetchone()
if res:
prev_query_time = res[0]
vt_id = res[1]
except:
print "sha1:%s no previous VT query" % (sha1, )
pass
vt_query_period = timedelta(days=VT_QUERY_INTERVAL)
if (time_now - prev_query_time) < vt_query_period:
print "sha1:%s has been queried recently. Skipping..." % (sha1, )
cursor.execute("""
INSERT INTO ped_vts_mapping (dump_id, vt_id)
VALUES (%s, %s)""",
(dump_id, vt_id))
conn.close()
return
tries = 0
success = False
while tries < MAX_TRIES:
try:
tries += 1
json = vt_api.get_vt_report(md5)
if not json:
continue
report = simplejson.loads(json)
if report["response_code"] == 1:
insert_report(cursor, report, sha1, md5, json, dump_id)
success = True
break
elif report["response_code"] == 0:
cursor.execute("""
INSERT INTO virus_total_scans(sha1, md5, query_time)
VALUES (%s, %s, CLOCK_TIMESTAMP())
RETURNING vt_id
""", (sha1, md5))
vt_id = cursor.fetchone()[0]
cursor.execute("""
INSERT INTO ped_vts_mapping (dump_id, vt_id)
VALUES (%s, %s)""",
(dump_id, vt_id))
print "Virus Total: No scan report exists in the VT database"
success = True
break
else:
logger.exception("Unknown response code! %s" %
(report["response_code"],))
time.sleep(1)
except Exception as e:
print e
logger.exception("Try %s. Error in fetching report for md5 %s: %s"
% (tries, md5, e))
time.sleep(5)
if not success:
cursor.execute("""
INSERT INTO ped_vts_mapping (dump_id)
VALUES (%s)""",
(dump_id,))
logger.warning("Giving up on dump_id: %s's VT report" % (dump_id,))
cursor.close()
conn.close()
