CWE 要因情報を追加する about jvn HOT 5 CLOSED

alter table jvn_vulnerability rename to jvn_vulnerability_old;
insert into jvn_vulnerability(
identifier,title,link,description,issued_date,modified_date,public_date,ticket_modified_date)
select * from jvn_vulnerability_old;

\copy jvn_cwe_work FROM '/var/jvn/cwe.txt';

update jvn_vulnerability a
set    cweid = b.cweid, cwetitle=b.cwetitle
from   jvn_cwe_work as b
where  a.identifier = b.identifier;

drop table jvn_vulnerability_old;

select cwetitle, count(cwetitle) as c from jvn_vulnerability group by cwetitle order by c desc;

cd ~/JVNDB/work/
cat cwe_kind.txt  | while read LINE; do rec=($LINE); grep "${rec[1]}" no_cwe.txt; [ $? -eq 0 ] && echo $LINE; done|tac
cat result.txt | awk -F, '/^CWE.[0-9]*/{cwe=$1}/^JVNDB/{print $1 "\t" cwe;}'

CREATE TEMPORARY TABLE jvn_cwe_master (
 id serial NOT NULL PRIMARY KEY,
 cweid       varchar(32),
 cwetitle    text
);
insert into jvn_cwe_master(cweid, cwetitle) select distinct cweid, cwetitle from jvn_vulnerability order by cweid;
select identifier, title, cweid, cwetitle from jvn_vulnerability a
where  cweid is not null
and not exists (select 1 from jvn_cwe_master b where a.cweid = b.cweid and a.cwetitle = b.cwetitle)

 for FILE in `ls CWE-*`; do CODE=`echo $FILE | sed 's/.txt//'`;  sed "s/\$/\t$CODE\t/" $FILE ; done