#!/bin/bash

rm -f /opt/output/* /tmp/*

# download all CVE information from NIST
for i in $(seq 0 2000 9000000)
do
    echo "CVSS scores loop offset $i"
    CONTENT=$(curl -s -H "apiKey: $NIST_API_KEY" "https://services.nvd.nist.gov/rest/json/cves/2.0/?resultsPerPage=2000&startIndex=$i") 

    echo "$CONTENT" > /tmp/CVSS_raw_$i.json
    cat /tmp/CVSS_raw_$i.json | jq '.vulnerabilities[].cve' > /tmp/CVSS_$i.json
    rm -f /tmp/CVSS_raw_$i.json

    RESULTS=$(echo $CONTENT | jq '.resultsPerPage')
    if [[ "$RESULTS" -lt "2000" ]]; then
       echo "break"
       break
    fi 
    sleep 3 # API limit 50 requests per minute, lets be nice and slow down even more
done

# extract CVSS information into flat json file
jq -c '.' /tmp/CVSS_*.json > /tmp/CVSS_full.json
jq -c '. | del(.references) | del(.configurations) | del(.weaknesses) | del(.descriptions) | .CVE=(.id|tostring) | del(.id)' /tmp/CVSS_full.json > /tmp/CVSS_reduced.json 
jq -c '[paths(scalars) as $path | { ($path | map(tostring) | join("_")): getpath($path) } ] | add' /tmp/CVSS_reduced.json > /tmp/CVSS_flat.json
jq -c '. 
| .["CVSS2_vectorString"] = .metrics_cvssMetricV2_0_cvssData_vectorString
| .["CVSS2_accessVector"] = .metrics_cvssMetricV2_0_cvssData_accessVector
| .["CVSS2_accessComplexity"] = .metrics_cvssMetricV2_0_cvssData_accessComplexity
| .["CVSS2_authentication"] = .metrics_cvssMetricV2_0_cvssData_authentication
| .["CVSS2_confidentialityImpact"] = .metrics_cvssMetricV2_0_cvssData_confidentialityImpact
| .["CVSS2_integrityImpact"] = .metrics_cvssMetricV2_0_cvssData_integrityImpact
| .["CVSS2_availabilityImpact"] = .metrics_cvssMetricV2_0_cvssData_availabilityImpact
| .["CVSS2_baseScore"] = .metrics_cvssMetricV2_0_cvssData_baseScore
| .["CVSS2_baseSeverity"] = .metrics_cvssMetricV2_0_cvssData_baseSeverity
| .["CVSS2_exploitabilityScore"] = .metrics_cvssMetricV2_0_exploitabilityScore
| .["CVSS2_impactScore"] = .metrics_cvssMetricV2_0_impactScore
| .["CVSS3_vectorString"] = .metrics_cvssMetricV30_0_cvssData_vectorString 
| .["CVSS3_attackVector"] = .metrics_cvssMetricV30_0_cvssData_attackVector 
| .["CVSS3_attackComplexity"] = .metrics_cvssMetricV30_0_cvssData_attackComplexity 
| .["CVSS3_privilegesRequired"] = .metrics_cvssMetricV30_0_cvssData_privilegesRequired 
| .["CVSS3_userInteraction"] = .metrics_cvssMetricV30_0_cvssData_userInteraction 
| .["CVSS3_scope"] = .metrics_cvssMetricV30_0_cvssData_scope 
| .["CVSS3_confidentialityImpact"] = .metrics_cvssMetricV30_0_cvssData_confidentialityImpact 
| .["CVSS3_integrityImpact"] = .metrics_cvssMetricV30_0_cvssData_integrityImpact 
| .["CVSS3_availabilityImpact"] = .metrics_cvssMetricV30_0_cvssData_availabilityImpact 
| .["CVSS3_baseScore"] = .metrics_cvssMetricV30_0_cvssData_baseScore 
| .["CVSS3_baseSeverity"] = .metrics_cvssMetricV30_0_cvssData_baseSeverity 
| .["CVSS3_exploitabilityScore"] = .metrics_cvssMetricV30_0_exploitabilityScore 
| .["CVSS3_impactScore"] = .metrics_cvssMetricV30_0_impactScore 
| .["CVSS3_acInsufInfo"] = .metrics_cvssMetricV30_0_acInsufInfo 
| .["CVSS3_obtainAllPrivilege"] = .metrics_cvssMetricV30_0_obtainAllPrivilege 
| .["CVSS3_obtainUserPrivilege"] = .metrics_cvssMetricV30_0_obtainUserPrivilege 
| .["CVSS3_obtainOtherPrivilege"] = .metrics_cvssMetricV30_0_obtainOtherPrivilege 
| .["CVSS3_userInteractionRequired"] = .metrics_cvssMetricV30_0_userInteractionRequired 
| { CVE, CVSS2_vectorString, CVSS2_accessVector, CVSS2_accessComplexity, CVSS2_authentication, CVSS2_confidentialityImpact, CVSS2_integrityImpact, CVSS2_availabilityImpact, CVSS2_baseScore, CVSS2_baseSeverity, CVSS2_exploitabilityScore, CVSS2_impactScore, CVSS3_vectorString, CVSS3_attackVector, CVSS3_attackComplexity, CVSS3_privilegesRequired, CVSS3_userInteraction, CVSS3_scope, CVSS3_confidentialityImpact, CVSS3_integrityImpact, CVSS3_availabilityImpact, CVSS3_baseScore, CVSS3_baseSeverity, CVSS3_exploitabilityScore, CVSS3_impactScore, CVSS3_acInsufInfo, CVSS3_obtainAllPrivilege, CVSS3_obtainUserPrivilege, CVSS3_obtainOtherPrivilege, CVSS3_userInteractionRequired }
' /tmp/CVSS_flat.json > /tmp/CVSS.json
mv /tmp/CVSS.json /opt/output/

# create csv file from json files
tail -1 /opt/output/CVSS.json | jq -r 'keys | @csv' > /tmp/CVSS.csv
jq -r 'map(tostring) | @csv' < /opt/output/CVSS.json >> /tmp/CVSS.csv
mv /tmp/CVSS.csv /opt/output/

# import csv file into sqlite database
sqlite3 /tmp/database.sqlite > /dev/null 2>&1 <<EOF
.mode csv
.import /opt/output/CVSS.csv cvss
EOF

# download EPSS inforamtion
wget -q https://epss.cyentia.com/epss_scores-current.csv.gz -O /tmp/epss_scores-current.csv.gz
gzip -d /tmp/epss_scores-current.csv.gz

# create json from csv file
tail -n +2 /tmp/epss_scores-current.csv | \
jq --slurp --raw-input --raw-output \
    'split("\n") | .[1:] | map(split(",")) |
        map({"CVE": .[0],
             "EPSS": .[1],
             "Percentile": .[2]})' > /tmp/EPSS_raw.json
jq -c '.[]' /tmp/EPSS_raw.json > /tmp/EPSS.json
mv /tmp/EPSS.json /opt/output/

# generate json file from cvs to have the header information right 
tail -1 /opt/output/EPSS.json | jq -r 'keys | @csv' > /tmp/EPSS.csv
jq -r 'map(tostring) | @csv' < /opt/output/EPSS.json >> /tmp/EPSS.csv
mv /tmp/EPSS.csv /opt/output/

# import csv into sqlite database
sqlite3 /tmp/database.sqlite > /dev/null 2>&1 <<EOF
.mode csv
.import /opt/output/EPSS.csv epss
EOF

# download CISA known expploited vulnerabilities list
wget -q https://www.cisa.gov/sites/default/files/csv/known_exploited_vulnerabilities.csv -O /tmp/CISA_known_exploited.csv
sed -i 's/cveID/CVE/' /tmp/CISA_known_exploited.csv
wget -q https://www.cisa.gov/sites/default/files/feeds/known_exploited_vulnerabilities.json -O /tmp/CISA_known_exploited_raw.json
jq .vulnerabilities[] /tmp/CISA_known_exploited_raw.json > /tmp/CISA_known_exploited.json
rm -f /tmp/CISA_known_exploited_raw.json

mv /tmp/CISA_known_exploited.json /opt/output
mv /tmp/CISA_known_exploited.csv /opt/output

sqlite3 /tmp/database.sqlite > /dev/null 2>&1 <<EOF
.mode csv
.import /opt/output/CISA_known_exploited.csv cisa
EOF

# join tables in sqlite to have CVE number, CVSS information and EPSS score in one file (json and csv)
sqlite3 /tmp/database.sqlite > /dev/null 2>&1 <<EOF
.mode json
.once /tmp/CVE_array.json
SELECT 
    cvss.CVE, cvss.CVSS2_accessComplexity, cvss.CVSS2_accessVector, cvss.CVSS2_authentication, cvss.CVSS2_availabilityImpact, cvss.CVSS2_baseScore, cvss.CVSS2_baseSeverity, cvss.CVSS2_confidentialityImpact, cvss.CVSS2_exploitabilityScore, cvss.CVSS2_impactScore, cvss.CVSS2_integrityImpact, cvss.CVSS2_vectorString, cvss.CVSS3_attackComplexity, cvss.CVSS3_attackVector, cvss.CVSS3_availabilityImpact, cvss.CVSS3_baseScore, cvss.CVSS3_baseSeverity, cvss.CVSS3_confidentialityImpact, cvss.CVSS3_exploitabilityScore, cvss.CVSS3_impactScore, cvss.CVSS3_integrityImpact, cvss.CVSS3_privilegesRequired, cvss.CVSS3_scope, cvss.CVSS3_userInteraction, cvss.CVSS3_vectorString, cvss.CVSS3_acInsufInfo, cvss.CVSS3_obtainAllPrivilege, cvss.CVSS3_obtainUserPrivilege, cvss.CVSS3_obtainOtherPrivilege, cvss.CVSS3_userInteractionRequired, 
    epss.EPSS, epss.Percentile as EPSS_Percentile, 
    cisa.dateAdded as CISA_dateAdded, cisa.requiredAction as CISA_RequiredAction
FROM cvss
LEFT JOIN epss ON cvss.CVE = epss.CVE
LEFT JOIN cisa ON cvss.CVE = cisa.CVE;
EOF
jq -c '.[]' /tmp/CVE_array.json > /tmp/CVE.json

sqlite3 /tmp/database.sqlite > /dev/null 2>&1 <<EOF
.headers on
.mode csv
.separator ";"
.mode quote
.once /tmp/CVE.csv
SELECT 
    cvss.CVE, cvss.CVSS2_accessComplexity, cvss.CVSS2_accessVector, cvss.CVSS2_authentication, cvss.CVSS2_availabilityImpact, cvss.CVSS2_baseScore, cvss.CVSS2_baseSeverity, cvss.CVSS2_confidentialityImpact, cvss.CVSS2_exploitabilityScore, cvss.CVSS2_impactScore, cvss.CVSS2_integrityImpact, cvss.CVSS2_vectorString, cvss.CVSS3_attackComplexity, cvss.CVSS3_attackVector, cvss.CVSS3_availabilityImpact, cvss.CVSS3_baseScore, cvss.CVSS3_baseSeverity, cvss.CVSS3_confidentialityImpact, cvss.CVSS3_exploitabilityScore, cvss.CVSS3_impactScore, cvss.CVSS3_integrityImpact, cvss.CVSS3_privilegesRequired, cvss.CVSS3_scope, cvss.CVSS3_userInteraction, cvss.CVSS3_vectorString, cvss.CVSS3_acInsufInfo, cvss.CVSS3_obtainAllPrivilege, cvss.CVSS3_obtainUserPrivilege, cvss.CVSS3_obtainOtherPrivilege, cvss.CVSS3_userInteractionRequired, 
    epss.EPSS, epss.Percentile as EPSS_Percentile, 
    cisa.dateAdded as CISA_dateAdded, cisa.requiredAction as CISA_RequiredAction
FROM cvss
LEFT JOIN epss ON cvss.CVE = epss.CVE
LEFT JOIN cisa ON cvss.CVE = cisa.CVE;
EOF
mv /tmp/CVE.json /tmp/CVE.csv /opt/output

# copy sqlite database to output
mv /tmp/database.sqlite /opt/output/
