Comments (8)
KlaasBonnema
commented on June 25, 2024
2
@williamdes, Thanks - I learned something new :-)
from dmarc-srg.
williamdes
commented on June 25, 2024
1
Can you edit your post and use fences?
```php
// code
```
from dmarc-srg.
williamdes
commented on June 25, 2024
I agree, I have some people that receive the emails that must be doing forwarding and always report broken dkim and spf
So basically all reports are red because one entry in the report is always failing out of my control
from dmarc-srg.
KlaasBonnema
commented on June 25, 2024
I made the following changes to make this happening:
ReportMapper.php
public function list(array &$filter, array &$order, array &$limit): array
{
$db = $this->connector->dbh();
$list = [];
$f_data = $this->prepareFilterData($filter);
$order_str = $this->sqlOrderList($order, '`rp`.`id`');
$cond_str0 = $this->sqlConditionList($f_data, ' AND ', 0);
$cond_str1 = $this->sqlConditionList($f_data, ' HAVING ', 1);
$limit_str = $this->sqlLimit($limit);
try
{
$st = $db->prepare('SELECT `org`, `begin_time`, `end_time`, `fqdn`, `external_id`, `seen`, SUM(`rcount`) AS `rcount`,' . ' MIN(`dkim_align`) AS `dkim_align`, MIN(`spf_align`) AS `spf_align`,' . ' MIN(`disposition`) AS `disposition`
, (SELECT IFNULL(SUM(f2.rcount),"") FROM `' . $this->connector->tablePrefix('rptrecords') . '` AS `f2` WHERE f2.report_id=rr.report_id AND f2.dkim_align<2) AS dkim_fail_count
, (SELECT IFNULL(SUM(f3.rcount),"") FROM `' . $this->connector->tablePrefix('rptrecords') . '` AS `f3` WHERE f3.report_id=rr.report_id AND f3.spf_align<2) AS spf_fail_count
FROM `' . $this->connector->tablePrefix('rptrecords') . '` AS `rr`
RIGHT JOIN (SELECT `rp`.`id`, `org`, `begin_time`, `end_time`, `external_id`,' . ' `fqdn`, `seen` FROM `' . $this->connector->tablePrefix('reports') . '` AS `rp`
INNER JOIN `' . $this->connector->tablePrefix('domains') . '` AS `d` ON `d`.`id` = `rp`.`domain_id`' . $cond_str0 . $order_str . ') AS `rp` ON `rp`.`id` = `rr`.`report_id` GROUP BY `rp`.`id`' . $cond_str1 . $order_str . $limit_str);
$this->sqlBindValues($st, $f_data, $limit);
$st->execute();
while ($row = $st->fetch(\PDO::FETCH_NUM))
{
$list[] = [
'org_name' => $row[0],
'date' => [
'begin' => new DateTime($row[1]),
'end' => new DateTime($row[2])
],
'domain' => $row[3],
'report_id' => $row[4],
'seen' => (bool) $row[5],
'messages' => $row[6],
'dkim_align' => Common::$align_res[$row[7]],
'spf_align' => Common::$align_res[$row[8]],
'disposition' => Common::$disposition[$row[9]],
'dkim_fail_count' => $row[10],
'spf_fail_count' => $row[11]
];
}
$st->closeCursor();
} catch (\PDOException $e)
{
throw new DatabaseFatalException('Failed to get the report list', - 1, $e);
}
return $list;
}list.js:
_make_row_data(d) {
let rd = {
cells: [],
userdata: { domain: d.domain, time: d.date.begin, org: d.org_name, report_id: d.report_id },
seen: d.seen && true || false
};
rd.cells.push({ content: d.domain, label: "Domain" });
let d1 = new Date(d.date.begin);
let d2 = new Date(d.date.end);
rd.cells.push({ content: date_range_to_string(d1, d2), title: d1.toUIString(true) + " - " + d2.toUIString(true), label: "Date" });
rd.cells.push({ content: d.org_name, label: "Reporting Organization" });
rd.cells.push({ content: d.report_id, class: "report-id" });
rd.cells.push({ content: d.messages, label: "Messages" });
rd.cells.push(new StatusColumn({ dkim_align: d.dkim_align, spf_align: d.spf_align,dkim_fail_count: d.dkim_fail_count, spf_fail_count:d.spf_fail_count }));
return rd;
}class StatusColumn extends ITableCell {
element() {
if (!this._element) {
super.element().setAttribute("data-label", "Result");
}
return this._element;
}
value(target) {
if (target === "dom") {
let d = this._content;
let fr = document.createDocumentFragment();
if (d.dkim_align) {
fr.appendChild(create_report_result_element("DKIM", d.dkim_fail_count,d.dkim_fail_count,d.dkim_align));
}
if (d.spf_align) {
fr.appendChild(create_report_result_element("SPF", d.spf_fail_count,d.spf_fail_count,d.spf_align));
}
return fr;
}
return super.value(target);
}
}from dmarc-srg.
KlaasBonnema
commented on June 25, 2024
I don't understand what you mean with fences.
Following is the actual lines that were changed. I copied complete functions before:
$st = $db->prepare('SELECT org, begin_time, end_time, fqdn, external_id, seen, SUM(rcount) AS rcount,' . ' MIN(dkim_align) AS dkim_align, MIN(spf_align) AS spf_align,' . ' MIN(disposition) AS disposition
, (SELECT IFNULL(SUM(f2.rcount),"") FROM ' . $this->connector->tablePrefix('rptrecords') . ' AS f2 WHERE f2.report_id=rr.report_id AND f2.dkim_align<2) AS dkim_fail_count
, (SELECT IFNULL(SUM(f3.rcount),"") FROM ' . $this->connector->tablePrefix('rptrecords') . ' AS f3 WHERE f3.report_id=rr.report_id AND f3.spf_align<2) AS spf_fail_count'dkim_fail_count' => $row[10],
'spf_fail_count' => $row[11]
fr.appendChild(create_report_result_element("DKIM", d.dkim_fail_count,d.dkim_fail_count,d.dkim_align));
fr.appendChild(create_report_result_element("SPF", d.spf_fail_count,d.spf_fail_count,d.spf_align));from dmarc-srg.
williamdes
commented on June 25, 2024
I don't understand what you mean with fences.
You can edit your comments and use my example
```php
$st = $db->prepare('SELECT org, begin_time, end_time, fqdn, external_id, seen, S...
```
will look like
$st = $db->prepare('SELECT org, begin_time, end_time, fqdn, external_id, seen, S...from dmarc-srg.
williamdes
commented on June 25, 2024
Thank you!
I looks 200% better
It works on many Markdown based platforms, like Stackoverflow
You have one last message to fence just before my example :)
Also, you can propose diffs using diff
Like
- $foo = 1;
+ $foo = 2;from dmarc-srg.
liuch
commented on June 25, 2024
I think this idea is worth implementing. But I need to think about a more optimal SQL query, as nested queries don't seem to be the optimal solution to me.
from dmarc-srg.
Related Issues (20)
- Show DMARC disposition on Report List
- Add DMARC disposition to the Report Detail filter HOT 8
- Enhance information on the Summary Reports page HOT 1
- Summary Reports period selection HOT 3
- error "ACL not available on this IMAP server" HOT 7
- kerberos error HOT 2
- Feature Request -- Support for retrieving DMARC emails from AWS S3 bucket HOT 18
- How to update HOT 4
- Feature Request - Reporting for multiple domains at once HOT 5
- Error: Call to undefined function Liuch\DmarcSrg\getallheaders() HOT 10
- Cannot get reports automatically from mailbox HOT 21
- Unkown tag: NP HOT 2
- Reports counter is for the last 30 days by default HOT 6
- Report list chnages on refresh HOT 1
- Report list changes on refresh HOT 10
- Feature request - Button to show only failed logs
- Fetch all e-mail reports HOT 4
- Please ignore google' <np> tag HOT 2
- Docker Installation Support HOT 4
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from dmarc-srg.