MariaDB 10.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `domain` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `domain` varchar(63) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `by_dom` (`domain`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;
INSERT INTO `domain` (`id`, `domain`) VALUES (6,'example.com'),(7,'example.net');
CREATE TABLE `message_in` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mtime` int(10) unsigned NOT NULL,
  `ip` varchar(15) NOT NULL,
  `dmarc_dispo` enum('none','quarantine','reject') NOT NULL DEFAULT 'none',
  PRIMARY KEY (`id`),
  UNIQUE KEY `mtime` (`mtime`)
) ENGINE=MyISAM AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4;
INSERT INTO `message_in` (`id`, `mtime`,`ip`, `dmarc_dispo`) VALUES (23,1617810363,'192.0.2.1','none'),(24,1617810364,'192.0.2.1','none'),(25,1617810365,'192.0.2.1','none'),(26,1617810366,'192.0.2.1','none'),(27,1617810367,'192.0.2.1','none'),(28,1617810368,'192.0.2.1','none'),(29,1617810369,'192.0.2.1','none'),(30,1617810373,'192.0.2.1','none'),(31,1617810370,'192.0.2.1','none'),(32,1617810371,'192.0.2.1','none'),(33,1617810374,'192.0.2.1','none'),(34,1617810372,'192.0.2.1','none'),(35,1617810391,'192.0.2.1','none'),(36,1617810392,'192.0.2.1','none'),(37,1617810393,'192.0.2.1','none');
CREATE TABLE `msg_ref` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `message_in` int(10) unsigned NOT NULL COMMENT 'Foreign key to message_in',
  `domain` int(10) unsigned NOT NULL COMMENT 'Foreign key to domain',
  `auth` set('author','spf_helo','spf','dkim','org','dmarc','aligned') NOT NULL,
  `spf` enum('none','neutral','pass','fail','softfail','temperror','permerror') NOT NULL,
  `dkim` enum('none','pass','fail','policy','neutral','temperror','permerror') NOT NULL,
  `dkim_order` tinyint(3) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `by_dom_msg` (`domain`,`message_in`),
  KEY `by_msg_auth` (`message_in`,`auth`)
) ENGINE=MyISAM AUTO_INCREMENT=95 DEFAULT CHARSET=utf8mb4;
INSERT INTO `msg_ref` (`id`, `message_in`, `domain`, `auth`, `spf`, `dkim`, `dkim_order`) VALUES (52,23,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',1),(53,23,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',2),(54,23,7,'dkim','none','pass',3),(55,24,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',1),(56,24,7,'dkim','none','pass',2),(57,25,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',1),(58,25,7,'dkim','none','pass',2),(59,26,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',1),(60,26,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',2),(61,26,7,'dkim','none','pass',3),(62,27,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',1),(63,27,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',2),(64,27,7,'dkim','none','pass',3),(65,28,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',1),(66,28,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',2),(67,28,7,'dkim','none','pass',3),(68,29,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',1),(69,29,7,'dkim','none','pass',2),(70,29,7,'dkim','none','pass',3),(71,30,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',1),(72,30,7,'dkim','none','pass',2),(73,30,7,'dkim','none','pass',3),(74,31,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',1),(75,31,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',2),(76,31,7,'dkim','none','pass',3),(77,32,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',1),(78,32,7,'dkim','none','pass',2),(79,32,7,'dkim','none','pass',3),(80,33,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',1),(81,33,7,'dkim','none','pass',2),(82,33,7,'dkim','none','pass',3),(83,34,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',1),(84,34,7,'dkim','none','pass',2),(85,35,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',1),(86,35,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',2),(87,35,7,'dkim','none','pass',3),(88,35,7,'dkim','none','pass',4),(89,36,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',1),(90,36,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',2),(91,36,7,'dkim','none','pass',3),(92,37,6,'author,spf,dkim,org,dmarc,aligned','pass','fail',1),(93,37,6,'author,spf,dkim,org,dmarc,aligned','pass','pass',2),(94,37,7,'dkim','none','pass',3);
SELECT m.ip AS source, COUNT(DISTINCT(m.id)) AS count,
m.dmarc_dispo AS disposition, 
da.domain AS 'Header from',
dspf.domain AS spf, rspf.spf AS spf_result,
d1.domain AS dkim1, r1.dkim AS dkim1_result,
d2.domain AS dkim2, r2.dkim AS dkim2_result,
d3.domain AS dkim3, r3.dkim AS dkim3_result,
d4.domain AS dkim4, r4.dkim AS dkim4_result
FROM message_in AS m
LEFT JOIN (msg_ref AS rd INNER JOIN domain AS dd ON rd.domain = dd.id)
  ON m.id = rd.message_in AND FIND_IN_SET('dmarc', rd.auth)
LEFT JOIN (msg_ref AS ra INNER JOIN domain AS da ON ra.domain = da.id)
  ON m.id = ra.message_in AND FIND_IN_SET('author', ra.auth)
LEFT JOIN (msg_ref AS rspf INNER JOIN domain AS dspf ON rspf.domain = dspf.id)
  ON m.id = rspf.message_in AND FIND_IN_SET('spf', rspf.auth)
LEFT JOIN (msg_ref AS rhelo INNER JOIN domain AS dhelo ON rhelo.domain = dhelo.id)
  ON m.id = rhelo.message_in AND FIND_IN_SET('spf_helo', rhelo.auth)
LEFT JOIN (msg_ref AS r1 INNER JOIN domain AS d1 ON r1.domain = d1.id)
  ON m.id = r1.message_in AND r1.dkim_order = 1
LEFT JOIN (msg_ref AS r2 INNER JOIN domain AS d2 ON r2.domain = d2.id)
  ON m.id = r2.message_in  AND r2.dkim_order = 2
LEFT JOIN (msg_ref AS r3 INNER JOIN domain AS d3 ON r3.domain = d3.id)
  ON m.id = r3.message_in AND r3.dkim_order = 3
LEFT JOIN (msg_ref AS r4 INNER JOIN domain AS d4 ON r4.domain = d4.id)
  ON m.id = r4.message_in  AND r4.dkim_order = 4
GROUP BY source, disposition, 'Header from',
 spf, spf_result, dkim1, dkim1_result,
 dkim2,  dkim2_result, dkim3, dkim3_result,
 dkim4, dkim4_result


source
count
disposition
Header from
spf
spf_result
dkim1
dkim1_result
dkim2
dkim2_result
dkim3
dkim3_result
dkim4
dkim4_result


192.0.2.1
6
none
example.com
example.com
pass
example.com
pass
example.com
pass
example.net
pass
null
null

192.0.2.1
1
none
example.com
example.com
pass
example.com
pass
example.com
pass
example.net
pass
example.net
pass

192.0.2.1
3
none
example.com
example.com
pass
example.com
pass
example.net
pass
null
null
null
null

192.0.2.1
4
none
example.com
example.com
pass
example.com
pass
example.net
pass
example.net
pass
null
null

192.0.2.1
1
none
example.com
example.com
pass
example.com
fail
example.com
pass
example.net
pass
null
null
source	count	disposition	Header from	spf	spf_result	dkim1	dkim1_result	dkim2	dkim2_result	dkim3	dkim3_result	dkim4	dkim4_result
192.0.2.1	6	none	example.com	example.com	pass	example.com	pass	example.com	pass	example.net	pass	null	null
192.0.2.1	1	none	example.com	example.com	pass	example.com	pass	example.com	pass	example.net	pass	example.net	pass
192.0.2.1	3	none	example.com	example.com	pass	example.com	pass	example.net	pass	null	null	null	null
192.0.2.1	4	none	example.com	example.com	pass	example.com	pass	example.net	pass	example.net	pass	null	null
192.0.2.1	1	none	example.com	example.com	pass	example.com	fail	example.com	pass	example.net	pass	null	null