Guiusepe pushed to branch main at Root / DMARC Report Commits: e551a908 by godp21 at 2025-08-06T11:02:27-03:00 Project working with go backend - - - - - 4 changed files: - + docker-compose.yml - + file - main.go - static/index.html Changes: ===================================== docker-compose.yml ===================================== @@ -0,0 +1,8 @@ +version: "3.8" + +services: + api: + build: . + container_name: dmarc-api + ports: + - "8080:8080" ===================================== file ===================================== @@ -0,0 +1,32 @@ +SELECT + *, + (CASE + WHEN dkim_align = 'fail' THEN 0 + WHEN dkim_align = 'pass' THEN 1 + ELSE 3 + END) + + + (CASE + WHEN spf_align = 'fail' THEN 0 + WHEN spf_align = 'pass' THEN 1 + ELSE 3 + END) + AS dmarc_result_min, + (CASE + WHEN dkim_align = 'fail' THEN 0 + WHEN dkim_align = 'pass' THEN 1 + ELSE 3 + END) + + + (CASE + WHEN spf_align = 'fail' THEN 0 + WHEN spf_align = 'pass' THEN 1 + ELSE 3 + END) + AS dmarc_result_max +FROM + rptrecord +WHERE + serial = 3955 +ORDER BY + ip ASC ===================================== main.go ===================================== @@ -3,7 +3,6 @@ package main import ( "database/sql" "encoding/json" - "encoding/xml" "fmt" "log" "net/http" @@ -20,17 +19,21 @@ const ( ) type Row struct { + Serial int `json:"serial"` MinDate string `json:"mindate"` MaxDate string `json:"maxdate"` Domain string `json:"domain"` Org string `json:"org"` - Messages int `json:"messages"` - ReportID string `json:"report_id"` -} - -// XML structure to extract <report_metadata><report_id>...</report_id></report_metadata> -type ReportMetadata struct { - ReportID string `xml:"report_metadata>report_id"` + ReportID string `json:"reportid"` + Email string `json:"email"` + Xtracon string `json:"extra_contact_info"` + Rcount int `json:"rcount"` + Dkim_align_min int `json:"dkim_align_min"` + Spf_align_min int `json:"spf_align_min"` + Dkim_result_min int `json:"dkim_result_min"` + Spf_result_min int `json:"spf_result_min"` + Dmarc_result_min int `json:"dmarc_result_min"` + Dmarc_result_max int `json:"dmarc_result_max"` } func main() { @@ -48,13 +51,56 @@ func main() { defer db.Close() query := ` - SELECT rpt.mindate, rpt.maxdate, rpt.domain, rpt.org, rpt.raw_xml, - count(*) as messages - FROM report as rpt - JOIN rptrecord as rcd ON rpt.serial = rcd.serial - GROUP BY rpt.serial - ORDER BY messages; + SELECT + report.serial, + mindate, + maxdate, + domain, + org, + reportid, + email, + rcount, + dkim_align_min, + spf_align_min, + dkim_result_min, + spf_result_min, + dmarc_result_min, + dmarc_result_max + FROM + report + LEFT JOIN + ( + SELECT + SUM(rcount) AS rcount, + serial, + MIN( + (CASE WHEN dkim_align = 'fail' THEN 0 WHEN dkim_align = 'pass' THEN 2 ELSE 1 END) + ) AS dkim_align_min, + MIN( + (CASE WHEN spf_align = 'fail' THEN 0 WHEN spf_align = 'pass' THEN 2 ELSE 1 END) + ) AS spf_align_min, + MIN( + (CASE WHEN dkimresult = 'fail' THEN 0 WHEN dkimresult = 'pass' THEN 2 ELSE 1 END) + ) AS dkim_result_min, + MIN( + (CASE WHEN spfresult = 'fail' THEN 0 WHEN spfresult = 'pass' THEN 2 ELSE 1 END) + ) AS spf_result_min, + MIN( + (CASE WHEN dkim_align = 'fail' THEN 0 WHEN dkim_align = 'pass' THEN 1 ELSE 3 END) + + + (CASE WHEN spf_align = 'fail' THEN 0 WHEN spf_align = 'pass' THEN 1 ELSE 3 END) + ) AS dmarc_result_min, + MAX( + (CASE WHEN dkim_align = 'fail' THEN 0 WHEN dkim_align = 'pass' THEN 1 ELSE 3 END) + + + (CASE WHEN spf_align = 'fail' THEN 0 WHEN spf_align = 'pass' THEN 1 ELSE 3 END) + ) AS dmarc_result_max + FROM rptrecord + GROUP BY serial + ) AS rptrecord + ON report.serial = rptrecord.serial ` + rows, err := db.Query(query) if err != nil { http.Error(w, err.Error(), 500) @@ -65,21 +111,97 @@ func main() { var results []Row for rows.Next() { var row Row - var rawXML string - err := rows.Scan(&row.MinDate, &row.MaxDate, &row.Domain, &row.Org, &rawXML, &row.Messages) + err := rows.Scan( + &row.Serial, + &row.MinDate, + &row.MaxDate, + &row.Domain, + &row.Org, + &row.ReportID, + &row.Email, + &row.Rcount, + &row.Dkim_align_min, + &row.Spf_align_min, + &row.Dkim_result_min, + &row.Spf_result_min, + &row.Dmarc_result_min, + &row.Dmarc_result_max, + ) if err != nil { http.Error(w, err.Error(), 500) return } + results = append(results, row) + } - var meta ReportMetadata - if err := xml.Unmarshal([]byte(rawXML), &meta); err == nil { - row.ReportID = meta.ReportID - } else { - row.ReportID = "(invalid XML)" - } + w.Header().Set("Content-Type", "application/json") + json.NewEncoder(w).Encode(results) + }) - results = append(results, row) + http.HandleFunc("/api/details", func(w http.ResponseWriter, r *http.Request) { + serial := r.URL.Query().Get("serial") + if serial == "" { + http.Error(w, "Missing serial parameter", http.StatusBadRequest) + return + } + + psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=require", + host, port, user, password, dbname) + + db, err := sql.Open("postgres", psqlInfo) + if err != nil { + http.Error(w, err.Error(), 500) + return + } + defer db.Close() + + query := ` + SELECT + *, + (CASE WHEN dkim_align = 'fail' THEN 0 WHEN dkim_align = 'pass' THEN 1 ELSE 3 END) + + + (CASE WHEN spf_align = 'fail' THEN 0 WHEN spf_align = 'pass' THEN 1 ELSE 3 END) + AS dmarc_result_min, + (CASE WHEN dkim_align = 'fail' THEN 0 WHEN dkim_align = 'pass' THEN 1 ELSE 3 END) + + + (CASE WHEN spf_align = 'fail' THEN 0 WHEN spf_align = 'pass' THEN 1 ELSE 3 END) + AS dmarc_result_max + FROM rptrecord + WHERE serial = $1 + ORDER BY ip ASC + ` + + rows, err := db.Query(query, serial) + if err != nil { + http.Error(w, err.Error(), 500) + return + } + defer rows.Close() + + columns, _ := rows.Columns() + values := make([]interface{}, len(columns)) + results := []map[string]interface{}{} + + for rows.Next() { + entry := make(map[string]interface{}) + pointers := make([]interface{}, len(columns)) + for i := range pointers { + pointers[i] = &values[i] + } + if err := rows.Scan(pointers...); err != nil { + http.Error(w, err.Error(), 500) + return + } + for i, col := range columns { + val := values[i] + b, ok := val.([]byte) + if ok { + entry[col] = string(b) + } else { + entry[col] = val + } + } + results = append(results, entry) } w.Header().Set("Content-Type", "application/json") @@ -89,4 +211,3 @@ func main() { fmt.Println("Server running at http://localhost:8080") log.Fatal(http.ListenAndServe(":8080", nil)) } - ===================================== static/index.html ===================================== @@ -7,21 +7,33 @@ width: 100%; border-collapse: collapse; margin-top: 1em; - } + } th, td { padding: 8px; border: 1px solid #ccc; text-align: left; - } + } </style> </head> <body> + <style> + .clickable-row { + cursor: pointer; + background-color: #f9f9f9; + } + .clickable-row:hover { + background-color: #e0e0e0; + } + .detail-row td { + background-color: #f1f1f1; + } + </style> <h1>DMARC Report Table</h1> <table id="report-table"> <thead> <tr> <th>Min Date</th> - <th>Max Date</th> + <th>Max Date</th> <th>Domain</th> <th>Org</th> <th>Messages</th> @@ -31,25 +43,59 @@ <tbody></tbody> </table> - <script> - fetch('/api/data') - .then(res => res.json()) - .then(data => { - const tbody = document.querySelector('#report-table tbody'); - data.forEach(row => { - const tr = document.createElement('tr'); - tr.innerHTML = ` - <td>${row.mindate}</td> - <td>${row.maxdate}</td> - <td>${row.domain}</td> - <td>${row.org}</td> - <td>${row.messages}</td> - <td>${row.report_id}</td> - `; - tbody.appendChild(tr); +<script> + fetch('/api/data') + .then(res => res.json()) + .then(data => { + const tbody = document.querySelector('#report-table tbody'); + data.forEach(row => { + // Main row + const tr = document.createElement('tr'); + tr.classList.add('clickable-row'); + tr.innerHTML = ` + <td>${row.mindate}</td> + <td>${row.maxdate}</td> + <td>${row.domain}</td> + <td>${row.org}</td> + <td>${row.rcount || row.messages}</td> + <td>${row.reportid || row.report_id}</td> + `; + + // Collapsible detail row (initially empty) + const detailTr = document.createElement('tr'); + detailTr.classList.add('detail-row'); + detailTr.style.display = 'none'; + const detailTd = document.createElement('td'); + detailTd.colSpan = 6; + detailTd.innerHTML = '<em>Loading...</em>'; + detailTr.appendChild(detailTd); + + // On click, fetch detail by serial + tr.addEventListener('click', () => { + if (detailTr.style.display === 'none') { + fetch(`/api/details?serial=${row.serial}`) + .then(res => res.json()) + .then(detail => { + detailTd.innerHTML = ` + <strong>Details for Serial ${row.serial}:</strong><br> + <pre>${JSON.stringify(detail, null, 2)}</pre> + `; + }) + .catch(err => { + detailTd.innerHTML = `<span style="color:red;">Error loading details</span>`; + }); + + detailTr.style.display = 'table-row'; + } else { + detailTr.style.display = 'none'; + } }); + + tbody.appendChild(tr); + tbody.appendChild(detailTr); }); - </script> + }); +</script> + </body> </html> - View it on GitLab: https://gitlab.c3sl.ufpr.br/root/dmarc-report/-/commit/e551a908f819cd5172881... -- View it on GitLab: https://gitlab.c3sl.ufpr.br/root/dmarc-report/-/commit/e551a908f819cd5172881... You're receiving this email because of your account on gitlab.c3sl.ufpr.br.