Guiusepe pushed to branch main at Root / DMARC Report

Commits:

4 changed files:

Changes:

  • docker-compose.yml
    1 
    +version: "3.8"
    2 
    +
    3 
    +services:
    4 
    +  api:
    5 
    +    build: .
    6 
    +    container_name: dmarc-api
    7 
    +    ports:
    8 
    +      - "8080:8080"

  • file
    1 
    +SELECT
    2 
    +  *,
    3 
    +  (CASE
    4 
    +		WHEN dkim_align = 'fail' THEN 0
    5 
    +		WHEN dkim_align = 'pass' THEN 1
    6 
    +		ELSE 3
    7 
    +  END)
    8 
    +  +
    9 
    +  (CASE
    10 
    +		WHEN spf_align = 'fail' THEN 0
    11 
    +		WHEN spf_align = 'pass' THEN 1
    12 
    +		ELSE 3
    13 
    +		END)
    14 
    +	AS dmarc_result_min,
    15 
    +	(CASE
    16 
    +		WHEN dkim_align = 'fail' THEN 0
    17 
    +		WHEN dkim_align = 'pass' THEN 1
    18 
    +		ELSE 3
    19 
    +  END)
    20 
    +  +
    21 
    +  (CASE
    22 
    +		WHEN spf_align = 'fail' THEN 0
    23 
    +		WHEN spf_align = 'pass' THEN 1
    24 
    +		ELSE 3
    25 
    +	END)
    26 
    +	AS dmarc_result_max
    27 
    +FROM
    28 
    +  rptrecord
    29 
    +WHERE
    30 
    +	serial = 3955
    31 
    +ORDER BY
    32 
    +	ip ASC

  • main.go
    ... ... @@ -3,7 +3,6 @@ package main
    3 3 
     import (
    4 4 
     	"database/sql"
    5 5 
     	"encoding/json"
    6 
    -	"encoding/xml"
    7 6 
     	"fmt"
    8 7 
     	"log"
    9 8 
     	"net/http"
    ... ... @@ -20,17 +19,21 @@ const (
    20 19 
     )
    21 20
    22 21 
     type Row struct {
    22 
    +  Serial   int    `json:"serial"`
    23 23 
     	MinDate  string `json:"mindate"`
    24 24 
     	MaxDate  string `json:"maxdate"`
    25 25 
     	Domain   string `json:"domain"`
    26 26 
     	Org      string `json:"org"`
    27 
    -	Messages int    `json:"messages"`
    28 
    -	ReportID string `json:"report_id"`
    29 
    -}
    30 
    -
    31 
    -// XML structure to extract <report_metadata><report_id>...</report_id></report_metadata>
    32 
    -type ReportMetadata struct {
    33 
    -	ReportID string `xml:"report_metadata>report_id"`
    27 
    +	ReportID string `json:"reportid"`
    28 
    +  Email    string `json:"email"`
    29 
    +  Xtracon  string `json:"extra_contact_info"`
    30 
    +  Rcount   int    `json:"rcount"`
    31 
    +  Dkim_align_min int `json:"dkim_align_min"`
    32 
    +  Spf_align_min int `json:"spf_align_min"`
    33 
    +  Dkim_result_min int `json:"dkim_result_min"`
    34 
    +  Spf_result_min int `json:"spf_result_min"`
    35 
    +  Dmarc_result_min int `json:"dmarc_result_min"`
    36 
    +  Dmarc_result_max int `json:"dmarc_result_max"`
    34 37 
     }
    35 38
    36 39 
     func main() {
    ... ... @@ -48,13 +51,56 @@ func main() {
    48 51 
     		defer db.Close()
    49 52
    50 53 
     		query := `
    51 
    -			SELECT rpt.mindate, rpt.maxdate, rpt.domain, rpt.org, rpt.raw_xml,
    52 
    -			       count(*) as messages
    53 
    -			FROM report as rpt
    54 
    -			JOIN rptrecord as rcd ON rpt.serial = rcd.serial
    55 
    -			GROUP BY rpt.serial
    56 
    -			ORDER BY messages;
    54 
    +      SELECT
    55 
    +        report.serial,
    56 
    +        mindate,
    57 
    +        maxdate,
    58 
    +        domain,
    59 
    +        org,
    60 
    +        reportid,
    61 
    +        email,
    62 
    +        rcount,
    63 
    +        dkim_align_min,
    64 
    +        spf_align_min,
    65 
    +        dkim_result_min,
    66 
    +        spf_result_min,
    67 
    +        dmarc_result_min,
    68 
    +        dmarc_result_max
    69 
    +      FROM
    70 
    +        report
    71 
    +        LEFT JOIN
    72 
    +          (
    73 
    +            SELECT
    74 
    +              SUM(rcount) AS rcount,
    75 
    +              serial,
    76 
    +              MIN(
    77 
    +                (CASE WHEN dkim_align = 'fail' THEN 0 WHEN dkim_align = 'pass' THEN 2 ELSE 1 END)
    78 
    +              ) AS dkim_align_min,
    79 
    +              MIN(
    80 
    +                (CASE WHEN spf_align = 'fail' THEN 0 WHEN spf_align = 'pass' THEN 2 ELSE 1 END)
    81 
    +              ) AS spf_align_min,
    82 
    +              MIN(
    83 
    +                (CASE WHEN dkimresult = 'fail' THEN 0 WHEN dkimresult = 'pass' THEN 2 ELSE 1 END)
    84 
    +              ) AS dkim_result_min,
    85 
    +              MIN(
    86 
    +                (CASE WHEN spfresult = 'fail' THEN 0 WHEN spfresult = 'pass' THEN 2 ELSE 1 END)
    87 
    +              ) AS spf_result_min,
    88 
    +              MIN(
    89 
    +                (CASE WHEN dkim_align = 'fail' THEN 0 WHEN dkim_align = 'pass' THEN 1 ELSE 3 END)
    90 
    +                +
    91 
    +                (CASE WHEN spf_align = 'fail' THEN 0 WHEN spf_align = 'pass' THEN 1 ELSE 3 END)
    92 
    +              ) AS dmarc_result_min,
    93 
    +              MAX(
    94 
    +                (CASE WHEN dkim_align = 'fail' THEN 0 WHEN dkim_align = 'pass' THEN 1 ELSE 3 END)
    95 
    +                +
    96 
    +                (CASE WHEN spf_align = 'fail' THEN 0 WHEN spf_align = 'pass' THEN 1 ELSE 3 END)
    97 
    +              ) AS dmarc_result_max
    98 
    +            FROM rptrecord
    99 
    +            GROUP BY serial
    100 
    +          ) AS rptrecord
    101 
    +      ON report.serial = rptrecord.serial
    57 102 
     		`
    103 
    +
    58 104 
     		rows, err := db.Query(query)
    59 105 
     		if err != nil {
    60 106 
     			http.Error(w, err.Error(), 500)
    ... ... @@ -65,21 +111,97 @@ func main() {
    65 111 
     		var results []Row
    66 112 
     		for rows.Next() {
    67 113 
     			var row Row
    68 
    -			var rawXML string
    69 
    -			err := rows.Scan(&row.MinDate, &row.MaxDate, &row.Domain, &row.Org, &rawXML, &row.Messages)
    114 
    +			err := rows.Scan(
    115 
    +				&row.Serial,
    116 
    +				&row.MinDate,
    117 
    +				&row.MaxDate,
    118 
    +				&row.Domain,
    119 
    +				&row.Org,
    120 
    +				&row.ReportID,
    121 
    +				&row.Email,
    122 
    +				&row.Rcount,
    123 
    +				&row.Dkim_align_min,
    124 
    +				&row.Spf_align_min,
    125 
    +				&row.Dkim_result_min,
    126 
    +				&row.Spf_result_min,
    127 
    +				&row.Dmarc_result_min,
    128 
    +				&row.Dmarc_result_max,
    129 
    +			)
    70 130 
     			if err != nil {
    71 131 
     				http.Error(w, err.Error(), 500)
    72 132 
     				return
    73 133 
     			}
    134 
    +			results = append(results, row)
    135 
    +		}
    74 136
    75 
    -			var meta ReportMetadata
    76 
    -			if err := xml.Unmarshal([]byte(rawXML), &meta); err == nil {
    77 
    -				row.ReportID = meta.ReportID
    78 
    -			} else {
    79 
    -				row.ReportID = "(invalid XML)"
    80 
    -			}
    137 
    +		w.Header().Set("Content-Type", "application/json")
    138 
    +		json.NewEncoder(w).Encode(results)
    139 
    +	})
    81 140
    82 
    -			results = append(results, row)
    141 
    +	http.HandleFunc("/api/details", func(w http.ResponseWriter, r *http.Request) {
    142 
    +		serial := r.URL.Query().Get("serial")
    143 
    +		if serial == "" {
    144 
    +			http.Error(w, "Missing serial parameter", http.StatusBadRequest)
    145 
    +			return
    146 
    +		}
    147 
    +
    148 
    +		psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=require",
    149 
    +			host, port, user, password, dbname)
    150 
    +
    151 
    +		db, err := sql.Open("postgres", psqlInfo)
    152 
    +		if err != nil {
    153 
    +			http.Error(w, err.Error(), 500)
    154 
    +			return
    155 
    +		}
    156 
    +		defer db.Close()
    157 
    +
    158 
    +		query := `
    159 
    +		SELECT
    160 
    +		  *,
    161 
    +		  (CASE WHEN dkim_align = 'fail' THEN 0 WHEN dkim_align = 'pass' THEN 1 ELSE 3 END)
    162 
    +		  +
    163 
    +		  (CASE WHEN spf_align = 'fail' THEN 0 WHEN spf_align = 'pass' THEN 1 ELSE 3 END)
    164 
    +		  AS dmarc_result_min,
    165 
    +		  (CASE WHEN dkim_align = 'fail' THEN 0 WHEN dkim_align = 'pass' THEN 1 ELSE 3 END)
    166 
    +		  +
    167 
    +		  (CASE WHEN spf_align = 'fail' THEN 0 WHEN spf_align = 'pass' THEN 1 ELSE 3 END)
    168 
    +		  AS dmarc_result_max
    169 
    +		FROM rptrecord
    170 
    +		WHERE serial = $1
    171 
    +		ORDER BY ip ASC
    172 
    +		`
    173 
    +
    174 
    +		rows, err := db.Query(query, serial)
    175 
    +		if err != nil {
    176 
    +			http.Error(w, err.Error(), 500)
    177 
    +			return
    178 
    +		}
    179 
    +		defer rows.Close()
    180 
    +
    181 
    +		columns, _ := rows.Columns()
    182 
    +		values := make([]interface{}, len(columns))
    183 
    +		results := []map[string]interface{}{}
    184 
    +
    185 
    +		for rows.Next() {
    186 
    +			entry := make(map[string]interface{})
    187 
    +			pointers := make([]interface{}, len(columns))
    188 
    +			for i := range pointers {
    189 
    +				pointers[i] = &values[i]
    190 
    +			}
    191 
    +			if err := rows.Scan(pointers...); err != nil {
    192 
    +				http.Error(w, err.Error(), 500)
    193 
    +				return
    194 
    +			}
    195 
    +			for i, col := range columns {
    196 
    +				val := values[i]
    197 
    +				b, ok := val.([]byte)
    198 
    +				if ok {
    199 
    +					entry[col] = string(b)
    200 
    +				} else {
    201 
    +					entry[col] = val
    202 
    +				}
    203 
    +			}
    204 
    +			results = append(results, entry)
    83 205 
     		}
    84 206
    85 207 
     		w.Header().Set("Content-Type", "application/json")
    ... ... @@ -89,4 +211,3 @@ func main() {
    89 211 
     	fmt.Println("Server running at http://localhost:8080")
    90 212 
     	log.Fatal(http.ListenAndServe(":8080", nil))
    91 213 
     }
    92 
    -

  • static/index.html
    ... ... @@ -7,21 +7,33 @@
    7 7 
           width: 100%;
    8 8 
           border-collapse: collapse;
    9 9 
           margin-top: 1em;
    10 
    -    }
    10 
    +    }
    11 11 
         th, td {
    12 12 
           padding: 8px;
    13 13 
           border: 1px solid #ccc;
    14 14 
           text-align: left;
    15 
    -    }
    15 
    +    }
    16 16 
       </style>
    17 17 
     </head>
    18 18 
     <body>
    19 
    +  <style>
    20 
    +    .clickable-row {
    21 
    +      cursor: pointer;
    22 
    +      background-color: #f9f9f9;
    23 
    +    }
    24 
    +    .clickable-row:hover {
    25 
    +      background-color: #e0e0e0;
    26 
    +    }
    27 
    +    .detail-row td {
    28 
    +      background-color: #f1f1f1;
    29 
    +    }
    30 
    +  </style>
    19 31 
       <h1>DMARC Report Table</h1>
    20 32 
       <table id="report-table">
    21 33 
         <thead>
    22 34 
           <tr>
    23 35 
             <th>Min Date</th>
    24 
    -        <th>Max Date</th>
    36 
    +       <th>Max Date</th>
    25 37 
             <th>Domain</th>
    26 38 
             <th>Org</th>
    27 39 
             <th>Messages</th>
    ... ... @@ -31,25 +43,59 @@
    31 43 
         <tbody></tbody>
    32 44 
       </table>
    33 45
    34 
    -  <script>
    35 
    -    fetch('/api/data')
    36 
    -      .then(res => res.json())
    37 
    -      .then(data => {
    38 
    -        const tbody = document.querySelector('#report-table tbody');
    39 
    -        data.forEach(row => {
    40 
    -          const tr = document.createElement('tr');
    41 
    -          tr.innerHTML = `
    42 
    -            <td>${row.mindate}</td>
    43 
    -            <td>${row.maxdate}</td>
    44 
    -            <td>${row.domain}</td>
    45 
    -            <td>${row.org}</td>
    46 
    -            <td>${row.messages}</td>
    47 
    -            <td>${row.report_id}</td>
    48 
    -          `;
    49 
    -          tbody.appendChild(tr);
    46 
    +<script>
    47 
    +  fetch('/api/data')
    48 
    +    .then(res => res.json())
    49 
    +    .then(data => {
    50 
    +      const tbody = document.querySelector('#report-table tbody');
    51 
    +      data.forEach(row => {
    52 
    +        // Main row
    53 
    +        const tr = document.createElement('tr');
    54 
    +        tr.classList.add('clickable-row');
    55 
    +        tr.innerHTML = `
    56 
    +          <td>${row.mindate}</td>
    57 
    +          <td>${row.maxdate}</td>
    58 
    +          <td>${row.domain}</td>
    59 
    +          <td>${row.org}</td>
    60 
    +          <td>${row.rcount || row.messages}</td>
    61 
    +          <td>${row.reportid || row.report_id}</td>
    62 
    +        `;
    63 
    +
    64 
    +        // Collapsible detail row (initially empty)
    65 
    +        const detailTr = document.createElement('tr');
    66 
    +        detailTr.classList.add('detail-row');
    67 
    +        detailTr.style.display = 'none';
    68 
    +        const detailTd = document.createElement('td');
    69 
    +        detailTd.colSpan = 6;
    70 
    +        detailTd.innerHTML = '<em>Loading...</em>';
    71 
    +        detailTr.appendChild(detailTd);
    72 
    +
    73 
    +        // On click, fetch detail by serial
    74 
    +        tr.addEventListener('click', () => {
    75 
    +          if (detailTr.style.display === 'none') {
    76 
    +            fetch(`/api/details?serial=${row.serial}`)
    77 
    +              .then(res => res.json())
    78 
    +              .then(detail => {
    79 
    +                detailTd.innerHTML = `
    80 
    +                  <strong>Details for Serial ${row.serial}:</strong><br>
    81 
    +                  <pre>${JSON.stringify(detail, null, 2)}</pre>
    82 
    +                `;
    83 
    +              })
    84 
    +              .catch(err => {
    85 
    +                detailTd.innerHTML = `<span style="color:red;">Error loading details</span>`;
    86 
    +              });
    87 
    +
    88 
    +            detailTr.style.display = 'table-row';
    89 
    +          } else {
    90 
    +            detailTr.style.display = 'none';
    91 
    +          }
    50 92 
             });
    93 
    +
    94 
    +        tbody.appendChild(tr);
    95 
    +        tbody.appendChild(detailTr);
    51 96 
           });
    52 
    -  </script>
    97 
    +    });
    98 
    +</script>
    99 
    +
    53 100 
     </body>
    54 101 
     </html>
    55 
    -

    • —
    View it on GitLab.
    You're receiving this email because of your account on gitlab.c3sl.ufpr.br. Manage all notifications · Help Notification message regarding https://gitlab.c3sl.ufpr.br/root/dmarc-report/-/commit/e551a908f819cd51728816ebbe6af3406b418883 at 1754489083