diff options
| -rwxr-xr-x | bin/acme/srtool_jira.py | 10 | ||||
| -rwxr-xr-x | bin/common/srtool_common.py | 5 | ||||
| -rwxr-xr-x | bin/common/srtool_defect.py | 13 | ||||
| -rwxr-xr-x | bin/common/srtool_update.py | 32 | ||||
| -rwxr-xr-x | bin/mitre/srtool_mitre.py | 2 | ||||
| -rwxr-xr-x | bin/nist/srtool_nist.py | 6 | ||||
| -rw-r--r-- | lib/orm/management/commands/lsupdates.py | 3 | ||||
| -rwxr-xr-x | lib/orm/migrations/0002_updates.py | 19 | ||||
| -rw-r--r-- | lib/orm/models.py | 5 | ||||
| -rw-r--r-- | lib/srtgui/tables.py | 17 | ||||
| -rw-r--r-- | lib/srtgui/templates/management.html | 2 |
11 files changed, 79 insertions, 35 deletions
diff --git a/bin/acme/srtool_jira.py b/bin/acme/srtool_jira.py index a0a90851..dc72404b 100755 --- a/bin/acme/srtool_jira.py +++ b/bin/acme/srtool_jira.py @@ -214,10 +214,10 @@ def do_update_jira(): log.write("=============================================================================\n") log.write("\n") - # Reset datasource's update_time as today - sql = "UPDATE orm_datasource SET update_time=?,lastModifiedDate=? WHERE data='jira_status'" - date_string = datetime.today().strftime('%Y-%m-%d %H:%M:%S') - c.execute(sql, (date_string,date_string,) ) + # Reset datasource's lastModifiedDate as today + sql = "UPDATE orm_datasource SET lastModifiedDate=? WHERE name='Jira'" + date_string = datetime.now().strftime(ORM.DATASOURCE_DATETIME_FORMAT) + c.execute(sql, (date_string,) ) conn.commit() c.close() @@ -433,7 +433,7 @@ def update_project_issues(project, issues, conn, log): sql = ''' INSERT into orm_cve (name, name_sort, priority, status, comments, comments_private, cve_data_type, cve_data_format, cve_data_version, public, publish_state, publish_date, description, publishedDate, lastModifiedDate, recommend, recommend_list, cvssV3_baseScore, cvssV3_baseSeverity, cvssV2_baseScore, cvssV2_severity, packages, srt_updated) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''' - c.execute(sql, (cve_name, cve_name_sort, d.priority, cve_status, '', '', '', '', '', 1, 0, '', 'Created from defect %s' % d.name, '', '', 0, '', '', '', '', '', '', datetime.now())) + c.execute(sql, (cve_name, cve_name_sort, d.priority, cve_status, '', '', '', '', '', 1, 0, '', 'Created from defect %s' % d.name, '', '', 0, '', '', '', '', '', '', datetime.now().strftime(ORM.DATASOURCE_DATETIME_FORMAT))) # Find the new id print("FINDING CVE ID for %s\n" % cve_name) diff --git a/bin/common/srtool_common.py b/bin/common/srtool_common.py index 46996aec..4cba2dc2 100755 --- a/bin/common/srtool_common.py +++ b/bin/common/srtool_common.py @@ -360,7 +360,7 @@ def score_new_cves(cve_filter): packages = ?, score_date = ? WHERE id = ?''' - cur_write.execute(sql, (recommend, recommend_list, cve_packages, time_now.strftime('%Y-%m-%d %H:%M:%S'), cve[ORM.CVE_ID])) + cur_write.execute(sql, (recommend, recommend_list, cve_packages, time_now.strftime(ORM.DATASOURCE_DATETIME_FORMAT), cve[ORM.CVE_ID])) write_count += 1 if verbose: print(" %d:%s:%s" % (recommend,recommend_list,cve_packages)) @@ -499,9 +499,12 @@ def gen_schema_header(): fd.write(" %s_%s = '%s'\n" % ('DATASOURCE','FREQUENCY_STR', \ 'Minute,Hourly,Daily,Weekly,Monthly,OnDemand,OnStartup' \ )) + fd.write(" %s_%s = '%s'\n" % ('DATASOURCE','DATE_FORMAT','%Y-%m-%d')) + fd.write(" %s_%s = '%s'\n" % ('DATASOURCE','DATETIME_FORMAT','%Y-%m-%d %H:%M:%S')) fd.write("\n\n") fd.write(" # General routine to return string name of a constant (e.g. 'DATASOURCE_FREQUENCY_STR')\n") + fd.write(" @staticmethod\n") fd.write(" def get_orm_string(value,string_set):\n") fd.write(" string_list = string_set.split(',')\n") fd.write(" string_count = len(string_list)\n") diff --git a/bin/common/srtool_defect.py b/bin/common/srtool_defect.py index 7cee1f85..d2a6878f 100755 --- a/bin/common/srtool_defect.py +++ b/bin/common/srtool_defect.py @@ -46,6 +46,11 @@ except ImportError: srtDbName = 'srt.sqlite' +# load the srt.sqlite schema indexes +dir_path = os.path.dirname(os.path.dirname(os.path.realpath(__file__))) +sys.path.insert(0, dir_path) +from common.srt_schema import ORM + ################################# # Helper methods # @@ -178,10 +183,10 @@ def update_defects(): log.write("=============================================================================\n") log.write("\n") - # Reset datasource's update_time as today - sql = "UPDATE orm_datasource SET update_time=?,lastModifiedDate=? WHERE data='defect_status'" - date_string = datetime.today().strftime('%Y-%m-%d %H:%M:%S') - c.execute(sql, (date_string,date_string,) ) + # Reset datasource's lastModifiedDate as today + sql = "UPDATE orm_datasource SET lastModifiedDate=? WHERE name='Defect'" + date_string = datetime.today().strftime(ORM.DATASOURCE_DATETIME_FORMAT) + c.execute(sql, (date_string,) ) conn.commit() c.close() diff --git a/bin/common/srtool_update.py b/bin/common/srtool_update.py index 64501f69..c282f40b 100755 --- a/bin/common/srtool_update.py +++ b/bin/common/srtool_update.py @@ -94,9 +94,9 @@ def run_updates(force_all,name_filter,is_trial): cur_write = conn.cursor() time_now = datetime.now() #datetime.now(pytz.utc) - print("SRTool Update: time_now = %s" % time_now.strftime('%Y-%m-%d %H:%M:%S')) + print("SRTool Update: time_now = %s" % time_now.strftime(ORM.DATASOURCE_DATETIME_FORMAT)) status_str = "====================\n" - status_str += "Update: Date=%s,Filter='%s',Force=%s\n" % (time_now.strftime('%Y-%m-%d %H:%M:%S'),name_filter,force_all) + status_str += "Update: Date=%s,Filter='%s',Force=%s\n" % (time_now.strftime(ORM.DATASOURCE_DATETIME_FORMAT),name_filter,force_all) #get sources that have update command sources = cur.execute("SELECT * FROM orm_datasource").fetchall() @@ -125,11 +125,11 @@ def run_updates(force_all,name_filter,is_trial): # Get the datasource values update_frequency = source[ORM.DATASOURCE_UPDATE_FREQUENCY] - if not source[ORM.DATASOURCE_LASTMODIFIEDDATE]: - # Force update if no registed modified date for datasource (e.g. Init) - last_modified_date = time_now-timedelta(days=365) + if not source[ORM.DATASOURCE_LASTUPDATEDDATE]: + # Force update if no registed updated date for datasource (i.e. at Init phase) + last_updated_date = time_now - timedelta(days=365) else: - last_modified_date = datetime.strptime(source[ORM.DATASOURCE_LASTMODIFIEDDATE], '%Y-%m-%d %H:%M:%S') + last_updated_date = datetime.strptime(source[ORM.DATASOURCE_LASTUPDATEDDATE], ORM.DATASOURCE_DATETIME_FORMAT) # Get the update presets update_time = source[ORM.DATASOURCE_UPDATE_TIME] delta_minutes = get_tag_key(update_time,'minutes',None) @@ -156,7 +156,7 @@ def run_updates(force_all,name_filter,is_trial): continue elif ORM.DATASOURCE_ONSTARTUP == update_frequency: continue - testdate = last_modified_date + testdiff + testdate = last_updated_date + testdiff # Adjust for update presets if None != delta_minute: @@ -175,10 +175,10 @@ def run_updates(force_all,name_filter,is_trial): # Not yet? if testdate > time_now: - status_str += " Skip '%s': update time not reached (%s)\n" % (source[ORM.DATASOURCE_DESCRIPTION],testdate.strftime('%Y-%m-%d %H:%M:%S')) + status_str += " Skip '%s': update time not reached (%s)\n" % (source[ORM.DATASOURCE_DESCRIPTION],testdate.strftime(ORM.DATASOURCE_DATETIME_FORMAT)) continue else: - status_str += " UPDATE '%s': update time reached (%s)\n" % (source[ORM.DATASOURCE_DESCRIPTION],testdate.strftime('%Y-%m-%d %H:%M:%S')) + status_str += " UPDATE '%s': update time reached (%s)\n" % (source[ORM.DATASOURCE_DESCRIPTION],testdate.strftime(ORM.DATASOURCE_DATETIME_FORMAT)) # Execute the update if is_trial: @@ -187,15 +187,15 @@ def run_updates(force_all,name_filter,is_trial): else: print("Update required\t...\texecuting '%s'" % (source[ORM.DATASOURCE_UPDATE])) status_str += " > EXECUTE: execute '%s'\n" % (source[ORM.DATASOURCE_UPDATE]) - master_write("SRTOOL_UPDATE:%s:%s:%s\n" %(time_now.strftime('%Y-%m-%d %H:%M:%S'),source[ORM.DATASOURCE_DESCRIPTION],source[ORM.DATASOURCE_UPDATE])) + master_write("SRTOOL_UPDATE:%s:%s:%s\n" %(time_now.strftime(ORM.DATASOURCE_DATETIME_FORMAT),source[ORM.DATASOURCE_DESCRIPTION],source[ORM.DATASOURCE_UPDATE])) update_command = source[ORM.DATASOURCE_UPDATE] if force_all: update_command += " --force" os.system(os.path.join(script_pathname, update_command)) - # Reset datasource's last_modified_date - sql = "UPDATE orm_datasource SET lastModifiedDate=? WHERE id=?" - cur_write.execute(sql, (time_now.strftime('%Y-%m-%d %H:%M:%S'),source[ORM.DATASOURCE_ID],) ) + # Reset datasource's last_updated_date + sql = "UPDATE orm_datasource SET lastUpdatedDate=? WHERE id=?" + cur_write.execute(sql, (time_now.strftime(ORM.DATASOURCE_DATETIME_FORMAT),source[ORM.DATASOURCE_ID],) ) conn.commit() conn.close() @@ -239,6 +239,8 @@ def list(): continue frequency_str = ORM.get_orm_string(source[ORM.DATASOURCE_UPDATE_FREQUENCY],ORM.DATASOURCE_FREQUENCY_STR) print(format_str % (source[ORM.DATASOURCE_DATA],source[ORM.DATASOURCE_SOURCE],source[ORM.DATASOURCE_NAME],frequency_str,source[ORM.DATASOURCE_UPDATE_TIME],source[ORM.DATASOURCE_DESCRIPTION])) + if verbose: + print(format_str % ('',source[ORM.DATASOURCE_LASTMODIFIEDDATE],source[ORM.DATASOURCE_LASTUPDATEDDATE],'','','')) ################################# # Start 'cron' job for updates @@ -246,7 +248,7 @@ def list(): def cron_start(): pid = os.getpid() - master_write("SRTOOL_UPDATE:%s:Starting -v update cron job, pid=%s\n" % (datetime.now().strftime('%Y-%m-%d %H:%M:%S'),pid)) + master_write("SRTOOL_UPDATE:%s:Starting -v update cron job, pid=%s\n" % (datetime.now().strftime(ORM.DATASOURCE_DATETIME_FORMAT),pid)) # Preserve this app's pid srt_update_pid_file = os.path.join(script_pathname,SRT_UPDATE_PID_FILE) @@ -276,7 +278,7 @@ def cron_stop(): # Kill the update app os.system("kill %s" % pid) os.system("rm %s" % srt_update_pid_file) - master_write("SRTOOL_UPDATE:%s:Stopping -^ update cron job, pid=%s\n" % (datetime.now().strftime('%Y-%m-%d %H:%M:%S'),pid)) + master_write("SRTOOL_UPDATE:%s:Stopping -^ update cron job, pid=%s\n" % (datetime.now().strftime(ORM.DATASOURCE_DATETIME_FORMAT),pid)) else: print("No running update task file found") diff --git a/bin/mitre/srtool_mitre.py b/bin/mitre/srtool_mitre.py index 5920b4dd..7504100e 100755 --- a/bin/mitre/srtool_mitre.py +++ b/bin/mitre/srtool_mitre.py @@ -110,7 +110,7 @@ def get_cve_default_status(is_init,publishedDate): date_delta = timedelta(days=cve_init_new_delta) init_new_date = datetime.now(pytz.utc) - date_delta - #print("\nPreset new data = %s" % init_new_date.strftime("%Y%m%d")) + #print("\nPreset new data = %s" % init_new_date.strftime("%Y-%m-%d")) init_new_date = init_new_date.strftime("%Y-%m-%d") if is_init: diff --git a/bin/nist/srtool_nist.py b/bin/nist/srtool_nist.py index 8138387c..e69420c7 100755 --- a/bin/nist/srtool_nist.py +++ b/bin/nist/srtool_nist.py @@ -111,7 +111,7 @@ def get_cve_default_status(is_init,publishedDate): date_delta = timedelta(days=cve_init_new_delta) init_new_date = datetime.now(pytz.utc) - date_delta - #print("\nPreset new data = %s" % init_new_date.strftime("%Y%m%d")) + #print("\nPreset new data = %s" % init_new_date.strftime("%Y-%m-%d")) init_new_date = init_new_date.strftime("%Y-%m-%d") if is_init: @@ -174,7 +174,7 @@ def update_nist(is_init,datasource_description, url_file, url_meta, cve_file, in # Force update if no registed modified date for datasource (e.g. Init) date_past = date_new-timedelta(days=1) else: - date_past = datetime.strptime(ds[ORM.DATASOURCE_LASTMODIFIEDDATE], '%Y-%m-%d %H:%M:%S') + date_past = datetime.strptime(ds[ORM.DATASOURCE_LASTMODIFIEDDATE], ORM.DATASOURCE_DATETIME_FORMAT) log.write("BEGINNING NIST %s\n" % ('INITS' if is_init else 'UPDATES')) #determine if we are out of date and apply updates if true @@ -200,7 +200,7 @@ def update_nist(is_init,datasource_description, url_file, url_meta, cve_file, in # Reset datasource's lastModifiedDate as today sql = "UPDATE orm_datasource SET lastModifiedDate = ? WHERE id='%s'" % ds[ORM.DATASOURCE_ID] - c.execute(sql, (datetime.today().strftime('%Y-%m-%d %H:%M:%S'),) ) + c.execute(sql, (datetime.today().strftime(ORM.DATASOURCE_DATETIME_FORMAT),) ) conn.commit() ####### diff --git a/lib/orm/management/commands/lsupdates.py b/lib/orm/management/commands/lsupdates.py index 365c8ab4..2a89a811 100644 --- a/lib/orm/management/commands/lsupdates.py +++ b/lib/orm/management/commands/lsupdates.py @@ -130,7 +130,8 @@ class Command(BaseCommand): updated_source=DataSource.objects.get(id=id) updated_source.loaded = True if update_modified: - updated_source.lastModifiedDate = datetime.today().strftime('%Y-%m-%d %H:%M:%S') + updated_source.lastModifiedDate = datetime.today().strftime(DataSource.DATETIME_FORMAT) + updated_source.lastUpdatedDate = datetime.today().strftime(DataSource.DATETIME_FORMAT) updated_source.save() def nist_cwe(self, content): diff --git a/lib/orm/migrations/0002_updates.py b/lib/orm/migrations/0002_updates.py new file mode 100755 index 00000000..44bca14b --- /dev/null +++ b/lib/orm/migrations/0002_updates.py @@ -0,0 +1,19 @@ +# -*- coding: utf-8 -*- +from __future__ import unicode_literals + +from django.db import migrations, models + + +class Migration(migrations.Migration): + + dependencies = [ + ('orm', '0001_initial'), + ] + + operations = [ + migrations.AddField( + model_name='datasource', + name='lastUpdatedDate', + field=models.CharField(max_length=50, blank=True), + ), + ] diff --git a/lib/orm/models.py b/lib/orm/models.py index ed6cee31..fd0fb0e9 100644 --- a/lib/orm/models.py +++ b/lib/orm/models.py @@ -151,6 +151,10 @@ class DataSource(models.Model): (ONSTARTUP, 'OnStartup'), ) + # Global date format + DATE_FORMAT = '%Y-%m-%d' + DATETIME_FORMAT = '%Y-%m-%d %H:%M:%S' + key = models.CharField(max_length=20) data = models.CharField(max_length=20) source = models.CharField(max_length=20) @@ -164,6 +168,7 @@ class DataSource(models.Model): update_frequency = models.IntegerField(choices=FREQUENCY, default=DAILY) loaded = models.BooleanField(default=False) lastModifiedDate = models.CharField(max_length=50, blank=True) + lastUpdatedDate = models.CharField(max_length=50, blank=True) update_time = models.CharField(max_length=50, blank=True) def get_frequency_text(self): diff --git a/lib/srtgui/tables.py b/lib/srtgui/tables.py index 09446fae..4da603b9 100644 --- a/lib/srtgui/tables.py +++ b/lib/srtgui/tables.py @@ -1667,12 +1667,21 @@ class SourcesTable(ToasterTable): ) self.add_column(title="Data Modified", + help_text="Last upstream date", hideable=False, orderable=True, field_name="lastModifiedDate", ) - self.add_column(title="Updates", + self.add_column(title="Updated", + help_text="Last update date", + hideable=True, + hidden=True, + #orderable=True, + field_name="lastUpdatedDate", + ) + + self.add_column(title="Update Freq.", hideable=False, orderable=True, field_name="update_frequency", @@ -1680,9 +1689,9 @@ class SourcesTable(ToasterTable): static_data_template="{{data.get_frequency_text}}", ) - self.add_column(title="Updated", - hideable=False, - orderable=True, + self.add_column(title="Update Detail", + hideable=True, + hidden=True, field_name="update_time", ) diff --git a/lib/srtgui/templates/management.html b/lib/srtgui/templates/management.html index 558b7a22..fe40ecb3 100644 --- a/lib/srtgui/templates/management.html +++ b/lib/srtgui/templates/management.html @@ -51,7 +51,7 @@ </tr> <tr> - <td><a class="btn btn-info btn-lg" href="{% url 'sources' %}">Manage Sources</a></td> + <td><a class="btn btn-info btn-lg" href="{% url 'sources' %}?nocache=1">Manage Sources</a></td> <td>Manage source list, perform manual pulls</td> </tr> {% endif %} |