MySQL COUNT within GROUP_CONCAT
4
I have created MySQL table like this.
CREATE TABLE `log_info` (
`log_id` int(11) NOT NULL AUTO_INCREMENT,
`log_datetime` datetime NOT NULL,
`log_attacker_ip` int(11) NOT NULL,
`log_event` varchar(250) NOT NULL,
`log_service_port` varchar(10) NOT NULL,
`log_target_ip` int(11) NOT NULL,
`log_severity` varchar(3) NOT NULL,
PRIMARY KEY (`log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=latin1
With a values of this:
INSERT INTO `log_info` (`log_id`, `log_datetime`, `log_attacker_ip`, `log_event`, `log_service_port`, `log_target_ip`, `log_severity`) VALUES
(1, '2018-11-13 00:16:45', 16843009, 'Traffic forward message', '80', 0, '5'),
(2, '2018-11-13 00:17:21', 16843009, 'Traffic forward message', '80', 0, '5'),
(3, '2018-11-13 00:17:24', 16843009, 'Traffic forward message', '80', 0, '5'),
(4, '2018-11-13 00:17:27', 16843009, 'Traffic forward message', '80', 0, '5'),
(5, '2018-11-13 00:17:30', 16843009, 'Traffic forward message', '80', 0, '5'),
(6, '2018-11-13 00:17:32', 16843009, 'Traffic forward message', '80', 0, '2'),
(7, '2018-11-13 00:17:34', 16843009, 'Traffic forward message', '80', 0, '5'),
(8, '2018-11-13 00:17:36', 16843009, 'Traffic forward message', '80', 0, '5'),
(9, '2018-11-13 00:17:39', 16843009, 'Traffic forward message', '80', 0, '1'),
(10, '2018-11-13 00:17:41', 16843009, 'Traffic forward message', '80', 0, '5'),
(11, '2018-11-13 00:17:44', 16843009, 'Traffic forward message', '80', 0, '1'),
(12, '2018-11-13 00:17:46', 16843009, 'Traffic forward message', '80', 0, '5'),
(13, '2018-11-13 00:17:48', 16843009, 'Traffic forward message', '80', 0, '4'),
(14, '2018-11-13 00:17:50', 16843009, 'Traffic forward message', '80', 0, '5'),
(15, '2018-11-13 00:17:53', 16843009, 'Traffic forward message', '80', 0, '5'),
(16, '2018-11-13 00:17:55', 16843009, 'Traffic forward message', '80', 0, '5'),
(17, '2018-11-13 00:17:57', 16843009, 'Traffic forward message', '80', 0, '5'),
(18, '2018-11-13 00:17:59', 16843009, 'ICMP', '80', 0, '3'),
(19, '2018-11-13 01:55:07', 16843009, 'ICMP', '80', 0, '5'),
(101, '2018-11-13 22:11:15', 134744072, 'bla', '443', 134744072, '4'),
(102, '2018-11-13 22:48:12', 134744072, 'bla', '443', 134744072, '4'),
(103, '2018-11-13 22:48:15', 134744072, 'bla', '443', 134744072, '4'),
(104, '2018-11-13 22:50:52', 2071690107, 'grrr', '21', 167837997, '2'),
(105, '2018-11-13 22:50:55', 2071690107, 'grrr', '21', 167837997, '2'),
(106, '2018-11-13 22:50:57', 2071690107, 'grrr', '21', 167837997, '2'),
(107, '2018-11-13 22:51:00', 2071690107, 'grrr', '21', 167837997, '2'),
(108, '2018-11-13 22:51:02', 2071690107, 'grrr', '21', 167837997, '2'),
(109, '2018-11-13 22:51:15', 2071690107, 'grrr', '21', 167903493, '2'),
(110, '2018-11-13 22:52:35', 2071690107, 'shhh', '0', 168433945, '1'),
(111, '2018-11-13 22:52:39', 2071690107, 'shhh', '0', 168433945, '1'),
(112, '2018-11-13 23:04:59', 134744072, 'bla', '443', 134744072, '4');
I having a little trouble to split COUNT(portno) for column [occurences] by using GROUP_CONCAT.
My Query:
SELECT MAX(log_id) AS 'log_id', MAX(log_datetime) AS 'recent_datetime', INET_NTOA(log_attacker_ip) AS 'attacker_IP', GROUP_CONCAT(DISTINCT log_service_port SEPARATOR ', ') AS 'portno', COUNT(*) AS 'occurences'
FROM log_info
WHERE log_datetime > NOW() - INTERVAL 30 DAY
AND log_datetime <= NOW()
GROUP BY attacker_IP
ORDER BY recent_datetime DESC
Here is my result:
+--------+---------------------+-----------------+--------+------------+
| log_id | recent_datetime | attacker_IP | portno | occurences |
+--------+---------------------+-----------------+--------+------------+
| 112 | 2018-11-13 23:04:59 | 8.8.8.8 | 443 | 4 |
| 111 | 2018-11-13 22:52:39 | 123.123.123.123 | 0, 21 | 8 |
| 19 | 2018-11-13 01:55:07 | 1.1.1.1 | 80 | 19 |
+--------+---------------------+-----------------+--------+------------+
I need to use GROUP_CONCAT on the column [occurences] so that it separate just like column [portno].
mysql aggregate-functions
asked Nov 24 '18 at 22:49
basyirstarbasyirstar
435
add a comment |
4
I have created MySQL table like this.
CREATE TABLE `log_info` (
`log_id` int(11) NOT NULL AUTO_INCREMENT,
`log_datetime` datetime NOT NULL,
`log_attacker_ip` int(11) NOT NULL,
`log_event` varchar(250) NOT NULL,
`log_service_port` varchar(10) NOT NULL,
`log_target_ip` int(11) NOT NULL,
`log_severity` varchar(3) NOT NULL,
PRIMARY KEY (`log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=latin1
With a values of this:
INSERT INTO `log_info` (`log_id`, `log_datetime`, `log_attacker_ip`, `log_event`, `log_service_port`, `log_target_ip`, `log_severity`) VALUES
(1, '2018-11-13 00:16:45', 16843009, 'Traffic forward message', '80', 0, '5'),
(2, '2018-11-13 00:17:21', 16843009, 'Traffic forward message', '80', 0, '5'),
(3, '2018-11-13 00:17:24', 16843009, 'Traffic forward message', '80', 0, '5'),
(4, '2018-11-13 00:17:27', 16843009, 'Traffic forward message', '80', 0, '5'),
(5, '2018-11-13 00:17:30', 16843009, 'Traffic forward message', '80', 0, '5'),
(6, '2018-11-13 00:17:32', 16843009, 'Traffic forward message', '80', 0, '2'),
(7, '2018-11-13 00:17:34', 16843009, 'Traffic forward message', '80', 0, '5'),
(8, '2018-11-13 00:17:36', 16843009, 'Traffic forward message', '80', 0, '5'),
(9, '2018-11-13 00:17:39', 16843009, 'Traffic forward message', '80', 0, '1'),
(10, '2018-11-13 00:17:41', 16843009, 'Traffic forward message', '80', 0, '5'),
(11, '2018-11-13 00:17:44', 16843009, 'Traffic forward message', '80', 0, '1'),
(12, '2018-11-13 00:17:46', 16843009, 'Traffic forward message', '80', 0, '5'),
(13, '2018-11-13 00:17:48', 16843009, 'Traffic forward message', '80', 0, '4'),
(14, '2018-11-13 00:17:50', 16843009, 'Traffic forward message', '80', 0, '5'),
(15, '2018-11-13 00:17:53', 16843009, 'Traffic forward message', '80', 0, '5'),
(16, '2018-11-13 00:17:55', 16843009, 'Traffic forward message', '80', 0, '5'),
(17, '2018-11-13 00:17:57', 16843009, 'Traffic forward message', '80', 0, '5'),
(18, '2018-11-13 00:17:59', 16843009, 'ICMP', '80', 0, '3'),
(19, '2018-11-13 01:55:07', 16843009, 'ICMP', '80', 0, '5'),
(101, '2018-11-13 22:11:15', 134744072, 'bla', '443', 134744072, '4'),
(102, '2018-11-13 22:48:12', 134744072, 'bla', '443', 134744072, '4'),
(103, '2018-11-13 22:48:15', 134744072, 'bla', '443', 134744072, '4'),
(104, '2018-11-13 22:50:52', 2071690107, 'grrr', '21', 167837997, '2'),
(105, '2018-11-13 22:50:55', 2071690107, 'grrr', '21', 167837997, '2'),
(106, '2018-11-13 22:50:57', 2071690107, 'grrr', '21', 167837997, '2'),
(107, '2018-11-13 22:51:00', 2071690107, 'grrr', '21', 167837997, '2'),
(108, '2018-11-13 22:51:02', 2071690107, 'grrr', '21', 167837997, '2'),
(109, '2018-11-13 22:51:15', 2071690107, 'grrr', '21', 167903493, '2'),
(110, '2018-11-13 22:52:35', 2071690107, 'shhh', '0', 168433945, '1'),
(111, '2018-11-13 22:52:39', 2071690107, 'shhh', '0', 168433945, '1'),
(112, '2018-11-13 23:04:59', 134744072, 'bla', '443', 134744072, '4');
I having a little trouble to split COUNT(portno) for column [occurences] by using GROUP_CONCAT.
My Query:
SELECT MAX(log_id) AS 'log_id', MAX(log_datetime) AS 'recent_datetime', INET_NTOA(log_attacker_ip) AS 'attacker_IP', GROUP_CONCAT(DISTINCT log_service_port SEPARATOR ', ') AS 'portno', COUNT(*) AS 'occurences'
FROM log_info
WHERE log_datetime > NOW() - INTERVAL 30 DAY
AND log_datetime <= NOW()
GROUP BY attacker_IP
ORDER BY recent_datetime DESC
Here is my result:
+--------+---------------------+-----------------+--------+------------+
| log_id | recent_datetime | attacker_IP | portno | occurences |
+--------+---------------------+-----------------+--------+------------+
| 112 | 2018-11-13 23:04:59 | 8.8.8.8 | 443 | 4 |
| 111 | 2018-11-13 22:52:39 | 123.123.123.123 | 0, 21 | 8 |
| 19 | 2018-11-13 01:55:07 | 1.1.1.1 | 80 | 19 |
+--------+---------------------+-----------------+--------+------------+
I need to use GROUP_CONCAT on the column [occurences] so that it separate just like column [portno].
mysql aggregate-functions
asked Nov 24 '18 at 22:49
basyirstarbasyirstar
435
Just to clarify a litle from what I understood, you need the columnoccurencesto be a comma separated string showing the quantity of occurences for each of the ports numbers displayed on the columnportno?. Although, this may be a different approach, why not to group by tupleipandport, then you can forgot of the port concatenation.
– Shidersz
Nov 24 '18 at 23:05
@D.Smania yeah I want to display occurences for each port number, how is that tuple work? I never used it.
– basyirstar
Nov 24 '18 at 23:21
Check the answers, both are similars and will help you. It seems, I'm too slow writing still... :-)
– Shidersz
Nov 24 '18 at 23:39
add a comment |
4
4
4
I have created MySQL table like this.
CREATE TABLE `log_info` (
`log_id` int(11) NOT NULL AUTO_INCREMENT,
`log_datetime` datetime NOT NULL,
`log_attacker_ip` int(11) NOT NULL,
`log_event` varchar(250) NOT NULL,
`log_service_port` varchar(10) NOT NULL,
`log_target_ip` int(11) NOT NULL,
`log_severity` varchar(3) NOT NULL,
PRIMARY KEY (`log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=latin1
With a values of this:
INSERT INTO `log_info` (`log_id`, `log_datetime`, `log_attacker_ip`, `log_event`, `log_service_port`, `log_target_ip`, `log_severity`) VALUES
(1, '2018-11-13 00:16:45', 16843009, 'Traffic forward message', '80', 0, '5'),
(2, '2018-11-13 00:17:21', 16843009, 'Traffic forward message', '80', 0, '5'),
(3, '2018-11-13 00:17:24', 16843009, 'Traffic forward message', '80', 0, '5'),
(4, '2018-11-13 00:17:27', 16843009, 'Traffic forward message', '80', 0, '5'),
(5, '2018-11-13 00:17:30', 16843009, 'Traffic forward message', '80', 0, '5'),
(6, '2018-11-13 00:17:32', 16843009, 'Traffic forward message', '80', 0, '2'),
(7, '2018-11-13 00:17:34', 16843009, 'Traffic forward message', '80', 0, '5'),
(8, '2018-11-13 00:17:36', 16843009, 'Traffic forward message', '80', 0, '5'),
(9, '2018-11-13 00:17:39', 16843009, 'Traffic forward message', '80', 0, '1'),
(10, '2018-11-13 00:17:41', 16843009, 'Traffic forward message', '80', 0, '5'),
(11, '2018-11-13 00:17:44', 16843009, 'Traffic forward message', '80', 0, '1'),
(12, '2018-11-13 00:17:46', 16843009, 'Traffic forward message', '80', 0, '5'),
(13, '2018-11-13 00:17:48', 16843009, 'Traffic forward message', '80', 0, '4'),
(14, '2018-11-13 00:17:50', 16843009, 'Traffic forward message', '80', 0, '5'),
(15, '2018-11-13 00:17:53', 16843009, 'Traffic forward message', '80', 0, '5'),
(16, '2018-11-13 00:17:55', 16843009, 'Traffic forward message', '80', 0, '5'),
(17, '2018-11-13 00:17:57', 16843009, 'Traffic forward message', '80', 0, '5'),
(18, '2018-11-13 00:17:59', 16843009, 'ICMP', '80', 0, '3'),
(19, '2018-11-13 01:55:07', 16843009, 'ICMP', '80', 0, '5'),
(101, '2018-11-13 22:11:15', 134744072, 'bla', '443', 134744072, '4'),
(102, '2018-11-13 22:48:12', 134744072, 'bla', '443', 134744072, '4'),
(103, '2018-11-13 22:48:15', 134744072, 'bla', '443', 134744072, '4'),
(104, '2018-11-13 22:50:52', 2071690107, 'grrr', '21', 167837997, '2'),
(105, '2018-11-13 22:50:55', 2071690107, 'grrr', '21', 167837997, '2'),
(106, '2018-11-13 22:50:57', 2071690107, 'grrr', '21', 167837997, '2'),
(107, '2018-11-13 22:51:00', 2071690107, 'grrr', '21', 167837997, '2'),
(108, '2018-11-13 22:51:02', 2071690107, 'grrr', '21', 167837997, '2'),
(109, '2018-11-13 22:51:15', 2071690107, 'grrr', '21', 167903493, '2'),
(110, '2018-11-13 22:52:35', 2071690107, 'shhh', '0', 168433945, '1'),
(111, '2018-11-13 22:52:39', 2071690107, 'shhh', '0', 168433945, '1'),
(112, '2018-11-13 23:04:59', 134744072, 'bla', '443', 134744072, '4');
I having a little trouble to split COUNT(portno) for column [occurences] by using GROUP_CONCAT.
My Query:
SELECT MAX(log_id) AS 'log_id', MAX(log_datetime) AS 'recent_datetime', INET_NTOA(log_attacker_ip) AS 'attacker_IP', GROUP_CONCAT(DISTINCT log_service_port SEPARATOR ', ') AS 'portno', COUNT(*) AS 'occurences'
FROM log_info
WHERE log_datetime > NOW() - INTERVAL 30 DAY
AND log_datetime <= NOW()
GROUP BY attacker_IP
ORDER BY recent_datetime DESC
Here is my result:
+--------+---------------------+-----------------+--------+------------+
| log_id | recent_datetime | attacker_IP | portno | occurences |
+--------+---------------------+-----------------+--------+------------+
| 112 | 2018-11-13 23:04:59 | 8.8.8.8 | 443 | 4 |
| 111 | 2018-11-13 22:52:39 | 123.123.123.123 | 0, 21 | 8 |
| 19 | 2018-11-13 01:55:07 | 1.1.1.1 | 80 | 19 |
+--------+---------------------+-----------------+--------+------------+
I need to use GROUP_CONCAT on the column [occurences] so that it separate just like column [portno].
mysql aggregate-functions
asked Nov 24 '18 at 22:49
basyirstarbasyirstar
435
I have created MySQL table like this.
CREATE TABLE `log_info` (
`log_id` int(11) NOT NULL AUTO_INCREMENT,
`log_datetime` datetime NOT NULL,
`log_attacker_ip` int(11) NOT NULL,
`log_event` varchar(250) NOT NULL,
`log_service_port` varchar(10) NOT NULL,
`log_target_ip` int(11) NOT NULL,
`log_severity` varchar(3) NOT NULL,
PRIMARY KEY (`log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=latin1
With a values of this:
INSERT INTO `log_info` (`log_id`, `log_datetime`, `log_attacker_ip`, `log_event`, `log_service_port`, `log_target_ip`, `log_severity`) VALUES
(1, '2018-11-13 00:16:45', 16843009, 'Traffic forward message', '80', 0, '5'),
(2, '2018-11-13 00:17:21', 16843009, 'Traffic forward message', '80', 0, '5'),
(3, '2018-11-13 00:17:24', 16843009, 'Traffic forward message', '80', 0, '5'),
(4, '2018-11-13 00:17:27', 16843009, 'Traffic forward message', '80', 0, '5'),
(5, '2018-11-13 00:17:30', 16843009, 'Traffic forward message', '80', 0, '5'),
(6, '2018-11-13 00:17:32', 16843009, 'Traffic forward message', '80', 0, '2'),
(7, '2018-11-13 00:17:34', 16843009, 'Traffic forward message', '80', 0, '5'),
(8, '2018-11-13 00:17:36', 16843009, 'Traffic forward message', '80', 0, '5'),
(9, '2018-11-13 00:17:39', 16843009, 'Traffic forward message', '80', 0, '1'),
(10, '2018-11-13 00:17:41', 16843009, 'Traffic forward message', '80', 0, '5'),
(11, '2018-11-13 00:17:44', 16843009, 'Traffic forward message', '80', 0, '1'),
(12, '2018-11-13 00:17:46', 16843009, 'Traffic forward message', '80', 0, '5'),
(13, '2018-11-13 00:17:48', 16843009, 'Traffic forward message', '80', 0, '4'),
(14, '2018-11-13 00:17:50', 16843009, 'Traffic forward message', '80', 0, '5'),
(15, '2018-11-13 00:17:53', 16843009, 'Traffic forward message', '80', 0, '5'),
(16, '2018-11-13 00:17:55', 16843009, 'Traffic forward message', '80', 0, '5'),
(17, '2018-11-13 00:17:57', 16843009, 'Traffic forward message', '80', 0, '5'),
(18, '2018-11-13 00:17:59', 16843009, 'ICMP', '80', 0, '3'),
(19, '2018-11-13 01:55:07', 16843009, 'ICMP', '80', 0, '5'),
(101, '2018-11-13 22:11:15', 134744072, 'bla', '443', 134744072, '4'),
(102, '2018-11-13 22:48:12', 134744072, 'bla', '443', 134744072, '4'),
(103, '2018-11-13 22:48:15', 134744072, 'bla', '443', 134744072, '4'),
(104, '2018-11-13 22:50:52', 2071690107, 'grrr', '21', 167837997, '2'),
(105, '2018-11-13 22:50:55', 2071690107, 'grrr', '21', 167837997, '2'),
(106, '2018-11-13 22:50:57', 2071690107, 'grrr', '21', 167837997, '2'),
(107, '2018-11-13 22:51:00', 2071690107, 'grrr', '21', 167837997, '2'),
(108, '2018-11-13 22:51:02', 2071690107, 'grrr', '21', 167837997, '2'),
(109, '2018-11-13 22:51:15', 2071690107, 'grrr', '21', 167903493, '2'),
(110, '2018-11-13 22:52:35', 2071690107, 'shhh', '0', 168433945, '1'),
(111, '2018-11-13 22:52:39', 2071690107, 'shhh', '0', 168433945, '1'),
(112, '2018-11-13 23:04:59', 134744072, 'bla', '443', 134744072, '4');
I having a little trouble to split COUNT(portno) for column [occurences] by using GROUP_CONCAT.
My Query:
SELECT MAX(log_id) AS 'log_id', MAX(log_datetime) AS 'recent_datetime', INET_NTOA(log_attacker_ip) AS 'attacker_IP', GROUP_CONCAT(DISTINCT log_service_port SEPARATOR ', ') AS 'portno', COUNT(*) AS 'occurences'
FROM log_info
WHERE log_datetime > NOW() - INTERVAL 30 DAY
AND log_datetime <= NOW()
GROUP BY attacker_IP
ORDER BY recent_datetime DESC
Here is my result:
+--------+---------------------+-----------------+--------+------------+
| log_id | recent_datetime | attacker_IP | portno | occurences |
+--------+---------------------+-----------------+--------+------------+
| 112 | 2018-11-13 23:04:59 | 8.8.8.8 | 443 | 4 |
| 111 | 2018-11-13 22:52:39 | 123.123.123.123 | 0, 21 | 8 |
| 19 | 2018-11-13 01:55:07 | 1.1.1.1 | 80 | 19 |
+--------+---------------------+-----------------+--------+------------+
I need to use GROUP_CONCAT on the column [occurences] so that it separate just like column [portno].
mysql aggregate-functions
mysql aggregate-functions
asked Nov 24 '18 at 22:49
basyirstarbasyirstar
435
asked Nov 24 '18 at 22:49
basyirstarbasyirstar
435
asked Nov 24 '18 at 22:49
basyirstarbasyirstar
435
asked Nov 24 '18 at 22:49
basyirstarbasyirstar
435
asked Nov 24 '18 at 22:49
basyirstarbasyirstar
435
435
Just to clarify a litle from what I understood, you need the columnoccurencesto be a comma separated string showing the quantity of occurences for each of the ports numbers displayed on the columnportno?. Although, this may be a different approach, why not to group by tupleipandport, then you can forgot of the port concatenation.
– Shidersz
Nov 24 '18 at 23:05
@D.Smania yeah I want to display occurences for each port number, how is that tuple work? I never used it.
– basyirstar
Nov 24 '18 at 23:21
Check the answers, both are similars and will help you. It seems, I'm too slow writing still... :-)
– Shidersz
Nov 24 '18 at 23:39
add a comment |
Just to clarify a litle from what I understood, you need the columnoccurencesto be a comma separated string showing the quantity of occurences for each of the ports numbers displayed on the columnportno?. Although, this may be a different approach, why not to group by tupleipandport, then you can forgot of the port concatenation.
– Shidersz
Nov 24 '18 at 23:05
@D.Smania yeah I want to display occurences for each port number, how is that tuple work? I never used it.
– basyirstar
Nov 24 '18 at 23:21
Check the answers, both are similars and will help you. It seems, I'm too slow writing still... :-)
– Shidersz
Nov 24 '18 at 23:39
Just to clarify a litle from what I understood, you need the column
occurences to be a comma separated string showing the quantity of occurences for each of the ports numbers displayed on the column portno?. Although, this may be a different approach, why not to group by tuple ip and port, then you can forgot of the port concatenation.– Shidersz
Nov 24 '18 at 23:05
Just to clarify a litle from what I understood, you need the column
occurences to be a comma separated string showing the quantity of occurences for each of the ports numbers displayed on the column portno?. Although, this may be a different approach, why not to group by tuple ip and port, then you can forgot of the port concatenation.– Shidersz
Nov 24 '18 at 23:05
@D.Smania yeah I want to display occurences for each port number, how is that tuple work? I never used it.
– basyirstar
Nov 24 '18 at 23:21
@D.Smania yeah I want to display occurences for each port number, how is that tuple work? I never used it.
– basyirstar
Nov 24 '18 at 23:21
Check the answers, both are similars and will help you. It seems, I'm too slow writing still... :-)
– Shidersz
Nov 24 '18 at 23:39
Check the answers, both are similars and will help you. It seems, I'm too slow writing still... :-)
– Shidersz
Nov 24 '18 at 23:39
add a comment |
2 Answers
2
active
oldest
votes
3
I'm presuming you want to have a list of occurrences that maps to the list of ports e.g if port list is 0, 21 you want 2, 6 which is the count of occurrences for each of those ports. In that case, you can use this query. You need to use two levels of grouping, first by attacker_IP and portno and then by attacker_IP to get this data:
SELECT MAX(log_id) AS log_id
, MAX(recent_datetime) AS recent_datetime
, attacker_IP
, GROUP_CONCAT(portno) AS ports
, GROUP_CONCAT(occurrences) AS occurrences
FROM (
SELECT MAX(log_id) AS log_id
, MAX(log_datetime) AS recent_datetime
, INET_NTOA(log_attacker_ip) AS attacker_IP
, log_service_port AS portno
, COUNT(*) AS occurrences
FROM log_info
WHERE log_datetime > NOW() - INTERVAL 30 DAY
AND log_datetime <= NOW()
GROUP BY attacker_IP, portno) AS d
GROUP BY attacker_IP
ORDER BY recent_datetime DESC
Output:
log_id recent_datetime attacker_IP ports occurrences
112 2018-11-13 23:04:59 8.8.8.8 443 4
111 2018-11-13 22:52:39 123.123.123.123 21,0 6,2
19 2018-11-13 01:55:07 1.1.1.1 80 19
Demo on dbfiddle
answered Nov 24 '18 at 23:24
NickNick
34.5k132043
Many thanks... It's just what I'm looking for... and thanks for the demo. I never knew that we can demo it on dbfiddle.
– basyirstar
Nov 24 '18 at 23:43
@basyirstar no worries. dbfiddle is great and when you give full table details as you did (thanks!) it makes it a lot easier to answer the question. Please consider marking the answer accepted (the tick under the up/down vote arrows) if it did work for you. See stackoverflow.com/help/someone-answers
– Nick
Nov 24 '18 at 23:46
add a comment |
2
I suggest to first use the next query:
SELECT
MAX(log_id) AS 'log_id',
MAX(log_datetime) AS 'recent_datetime',
INET_NTOA(log_attacker_ip) AS 'attacker_IP',
log_service_port AS 'portno',
COUNT(*) AS 'occurences'
FROM
log_info
WHERE
log_datetime > NOW() - INTERVAL 30 DAY
AND
log_datetime <= NOW()
GROUP BY
attacker_IP, portno
ORDER BY
recent_datetime DESC
The previous query will displays reports for the differentes tuples of (attacker_IP, portno). Now, if you still want to concatenate ports numbers and occurences, you can query the previous one, like this:
SELECT
MAX(ip_port_logs.log_id) AS 'log_id',
MAX(ip_port_logs.recent_datetime) AS 'recent_datetime',
ip_port_logs.attacker_IP,
GROUP_CONCAT(ip_port_logs.portno SEPARATOR ', ') AS 'ports',
GROUP_CONCAT(ip_port_logs.occurences SEPARATOR ', ') AS 'ports_occurences'
FROM
( SELECT
MAX(log_id) AS 'log_id',
MAX(log_datetime) AS 'recent_datetime',
INET_NTOA(log_attacker_ip) AS 'attacker_IP',
log_service_port AS 'portno',
COUNT(*) AS 'occurences'
FROM
log_info
WHERE
log_datetime > NOW() - INTERVAL 30 DAY
AND
log_datetime <= NOW()
GROUP BY
attacker_IP, portno
ORDER BY
recent_datetime DESC ) AS ip_port_logs
GROUP BY
ip_port_logs.attacker_IP
answered Nov 24 '18 at 23:28
ShiderszShidersz
8,7502833
Thank you for this great help. :D
– basyirstar
Nov 24 '18 at 23:46
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53463034%2fmysql-count-within-group-concat%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
3
I'm presuming you want to have a list of occurrences that maps to the list of ports e.g if port list is 0, 21 you want 2, 6 which is the count of occurrences for each of those ports. In that case, you can use this query. You need to use two levels of grouping, first by attacker_IP and portno and then by attacker_IP to get this data:
SELECT MAX(log_id) AS log_id
, MAX(recent_datetime) AS recent_datetime
, attacker_IP
, GROUP_CONCAT(portno) AS ports
, GROUP_CONCAT(occurrences) AS occurrences
FROM (
SELECT MAX(log_id) AS log_id
, MAX(log_datetime) AS recent_datetime
, INET_NTOA(log_attacker_ip) AS attacker_IP
, log_service_port AS portno
, COUNT(*) AS occurrences
FROM log_info
WHERE log_datetime > NOW() - INTERVAL 30 DAY
AND log_datetime <= NOW()
GROUP BY attacker_IP, portno) AS d
GROUP BY attacker_IP
ORDER BY recent_datetime DESC
Output:
log_id recent_datetime attacker_IP ports occurrences
112 2018-11-13 23:04:59 8.8.8.8 443 4
111 2018-11-13 22:52:39 123.123.123.123 21,0 6,2
19 2018-11-13 01:55:07 1.1.1.1 80 19
Demo on dbfiddle
answered Nov 24 '18 at 23:24
NickNick
34.5k132043
Many thanks... It's just what I'm looking for... and thanks for the demo. I never knew that we can demo it on dbfiddle.
– basyirstar
Nov 24 '18 at 23:43
@basyirstar no worries. dbfiddle is great and when you give full table details as you did (thanks!) it makes it a lot easier to answer the question. Please consider marking the answer accepted (the tick under the up/down vote arrows) if it did work for you. See stackoverflow.com/help/someone-answers
– Nick
Nov 24 '18 at 23:46
add a comment |
3
I'm presuming you want to have a list of occurrences that maps to the list of ports e.g if port list is 0, 21 you want 2, 6 which is the count of occurrences for each of those ports. In that case, you can use this query. You need to use two levels of grouping, first by attacker_IP and portno and then by attacker_IP to get this data:
SELECT MAX(log_id) AS log_id
, MAX(recent_datetime) AS recent_datetime
, attacker_IP
, GROUP_CONCAT(portno) AS ports
, GROUP_CONCAT(occurrences) AS occurrences
FROM (
SELECT MAX(log_id) AS log_id
, MAX(log_datetime) AS recent_datetime
, INET_NTOA(log_attacker_ip) AS attacker_IP
, log_service_port AS portno
, COUNT(*) AS occurrences
FROM log_info
WHERE log_datetime > NOW() - INTERVAL 30 DAY
AND log_datetime <= NOW()
GROUP BY attacker_IP, portno) AS d
GROUP BY attacker_IP
ORDER BY recent_datetime DESC
Output:
log_id recent_datetime attacker_IP ports occurrences
112 2018-11-13 23:04:59 8.8.8.8 443 4
111 2018-11-13 22:52:39 123.123.123.123 21,0 6,2
19 2018-11-13 01:55:07 1.1.1.1 80 19
Demo on dbfiddle
answered Nov 24 '18 at 23:24
NickNick
34.5k132043
Many thanks... It's just what I'm looking for... and thanks for the demo. I never knew that we can demo it on dbfiddle.
– basyirstar
Nov 24 '18 at 23:43
@basyirstar no worries. dbfiddle is great and when you give full table details as you did (thanks!) it makes it a lot easier to answer the question. Please consider marking the answer accepted (the tick under the up/down vote arrows) if it did work for you. See stackoverflow.com/help/someone-answers
– Nick
Nov 24 '18 at 23:46
add a comment |
3
3
3
I'm presuming you want to have a list of occurrences that maps to the list of ports e.g if port list is 0, 21 you want 2, 6 which is the count of occurrences for each of those ports. In that case, you can use this query. You need to use two levels of grouping, first by attacker_IP and portno and then by attacker_IP to get this data:
SELECT MAX(log_id) AS log_id
, MAX(recent_datetime) AS recent_datetime
, attacker_IP
, GROUP_CONCAT(portno) AS ports
, GROUP_CONCAT(occurrences) AS occurrences
FROM (
SELECT MAX(log_id) AS log_id
, MAX(log_datetime) AS recent_datetime
, INET_NTOA(log_attacker_ip) AS attacker_IP
, log_service_port AS portno
, COUNT(*) AS occurrences
FROM log_info
WHERE log_datetime > NOW() - INTERVAL 30 DAY
AND log_datetime <= NOW()
GROUP BY attacker_IP, portno) AS d
GROUP BY attacker_IP
ORDER BY recent_datetime DESC
Output:
log_id recent_datetime attacker_IP ports occurrences
112 2018-11-13 23:04:59 8.8.8.8 443 4
111 2018-11-13 22:52:39 123.123.123.123 21,0 6,2
19 2018-11-13 01:55:07 1.1.1.1 80 19
Demo on dbfiddle
answered Nov 24 '18 at 23:24
NickNick
34.5k132043
I'm presuming you want to have a list of occurrences that maps to the list of ports e.g if port list is 0, 21 you want 2, 6 which is the count of occurrences for each of those ports. In that case, you can use this query. You need to use two levels of grouping, first by attacker_IP and portno and then by attacker_IP to get this data:
SELECT MAX(log_id) AS log_id
, MAX(recent_datetime) AS recent_datetime
, attacker_IP
, GROUP_CONCAT(portno) AS ports
, GROUP_CONCAT(occurrences) AS occurrences
FROM (
SELECT MAX(log_id) AS log_id
, MAX(log_datetime) AS recent_datetime
, INET_NTOA(log_attacker_ip) AS attacker_IP
, log_service_port AS portno
, COUNT(*) AS occurrences
FROM log_info
WHERE log_datetime > NOW() - INTERVAL 30 DAY
AND log_datetime <= NOW()
GROUP BY attacker_IP, portno) AS d
GROUP BY attacker_IP
ORDER BY recent_datetime DESC
Output:
log_id recent_datetime attacker_IP ports occurrences
112 2018-11-13 23:04:59 8.8.8.8 443 4
111 2018-11-13 22:52:39 123.123.123.123 21,0 6,2
19 2018-11-13 01:55:07 1.1.1.1 80 19
Demo on dbfiddle
answered Nov 24 '18 at 23:24
NickNick
34.5k132043
answered Nov 24 '18 at 23:24
NickNick
34.5k132043
answered Nov 24 '18 at 23:24
NickNick
34.5k132043
answered Nov 24 '18 at 23:24
NickNick
34.5k132043
34.5k132043
Many thanks... It's just what I'm looking for... and thanks for the demo. I never knew that we can demo it on dbfiddle.
– basyirstar
Nov 24 '18 at 23:43
@basyirstar no worries. dbfiddle is great and when you give full table details as you did (thanks!) it makes it a lot easier to answer the question. Please consider marking the answer accepted (the tick under the up/down vote arrows) if it did work for you. See stackoverflow.com/help/someone-answers
– Nick
Nov 24 '18 at 23:46
add a comment |
Many thanks... It's just what I'm looking for... and thanks for the demo. I never knew that we can demo it on dbfiddle.
– basyirstar
Nov 24 '18 at 23:43
@basyirstar no worries. dbfiddle is great and when you give full table details as you did (thanks!) it makes it a lot easier to answer the question. Please consider marking the answer accepted (the tick under the up/down vote arrows) if it did work for you. See stackoverflow.com/help/someone-answers
– Nick
Nov 24 '18 at 23:46
Many thanks... It's just what I'm looking for... and thanks for the demo. I never knew that we can demo it on dbfiddle.
– basyirstar
Nov 24 '18 at 23:43
Many thanks... It's just what I'm looking for... and thanks for the demo. I never knew that we can demo it on dbfiddle.
– basyirstar
Nov 24 '18 at 23:43
@basyirstar no worries. dbfiddle is great and when you give full table details as you did (thanks!) it makes it a lot easier to answer the question. Please consider marking the answer accepted (the tick under the up/down vote arrows) if it did work for you. See stackoverflow.com/help/someone-answers
– Nick
Nov 24 '18 at 23:46
@basyirstar no worries. dbfiddle is great and when you give full table details as you did (thanks!) it makes it a lot easier to answer the question. Please consider marking the answer accepted (the tick under the up/down vote arrows) if it did work for you. See stackoverflow.com/help/someone-answers
– Nick
Nov 24 '18 at 23:46
add a comment |
2
I suggest to first use the next query:
SELECT
MAX(log_id) AS 'log_id',
MAX(log_datetime) AS 'recent_datetime',
INET_NTOA(log_attacker_ip) AS 'attacker_IP',
log_service_port AS 'portno',
COUNT(*) AS 'occurences'
FROM
log_info
WHERE
log_datetime > NOW() - INTERVAL 30 DAY
AND
log_datetime <= NOW()
GROUP BY
attacker_IP, portno
ORDER BY
recent_datetime DESC
The previous query will displays reports for the differentes tuples of (attacker_IP, portno). Now, if you still want to concatenate ports numbers and occurences, you can query the previous one, like this:
SELECT
MAX(ip_port_logs.log_id) AS 'log_id',
MAX(ip_port_logs.recent_datetime) AS 'recent_datetime',
ip_port_logs.attacker_IP,
GROUP_CONCAT(ip_port_logs.portno SEPARATOR ', ') AS 'ports',
GROUP_CONCAT(ip_port_logs.occurences SEPARATOR ', ') AS 'ports_occurences'
FROM
( SELECT
MAX(log_id) AS 'log_id',
MAX(log_datetime) AS 'recent_datetime',
INET_NTOA(log_attacker_ip) AS 'attacker_IP',
log_service_port AS 'portno',
COUNT(*) AS 'occurences'
FROM
log_info
WHERE
log_datetime > NOW() - INTERVAL 30 DAY
AND
log_datetime <= NOW()
GROUP BY
attacker_IP, portno
ORDER BY
recent_datetime DESC ) AS ip_port_logs
GROUP BY
ip_port_logs.attacker_IP
answered Nov 24 '18 at 23:28
ShiderszShidersz
8,7502833
Thank you for this great help. :D
– basyirstar
Nov 24 '18 at 23:46
add a comment |
2
I suggest to first use the next query:
SELECT
MAX(log_id) AS 'log_id',
MAX(log_datetime) AS 'recent_datetime',
INET_NTOA(log_attacker_ip) AS 'attacker_IP',
log_service_port AS 'portno',
COUNT(*) AS 'occurences'
FROM
log_info
WHERE
log_datetime > NOW() - INTERVAL 30 DAY
AND
log_datetime <= NOW()
GROUP BY
attacker_IP, portno
ORDER BY
recent_datetime DESC
The previous query will displays reports for the differentes tuples of (attacker_IP, portno). Now, if you still want to concatenate ports numbers and occurences, you can query the previous one, like this:
SELECT
MAX(ip_port_logs.log_id) AS 'log_id',
MAX(ip_port_logs.recent_datetime) AS 'recent_datetime',
ip_port_logs.attacker_IP,
GROUP_CONCAT(ip_port_logs.portno SEPARATOR ', ') AS 'ports',
GROUP_CONCAT(ip_port_logs.occurences SEPARATOR ', ') AS 'ports_occurences'
FROM
( SELECT
MAX(log_id) AS 'log_id',
MAX(log_datetime) AS 'recent_datetime',
INET_NTOA(log_attacker_ip) AS 'attacker_IP',
log_service_port AS 'portno',
COUNT(*) AS 'occurences'
FROM
log_info
WHERE
log_datetime > NOW() - INTERVAL 30 DAY
AND
log_datetime <= NOW()
GROUP BY
attacker_IP, portno
ORDER BY
recent_datetime DESC ) AS ip_port_logs
GROUP BY
ip_port_logs.attacker_IP
answered Nov 24 '18 at 23:28
ShiderszShidersz
8,7502833
Thank you for this great help. :D
– basyirstar
Nov 24 '18 at 23:46
add a comment |
2
2
2
I suggest to first use the next query:
SELECT
MAX(log_id) AS 'log_id',
MAX(log_datetime) AS 'recent_datetime',
INET_NTOA(log_attacker_ip) AS 'attacker_IP',
log_service_port AS 'portno',
COUNT(*) AS 'occurences'
FROM
log_info
WHERE
log_datetime > NOW() - INTERVAL 30 DAY
AND
log_datetime <= NOW()
GROUP BY
attacker_IP, portno
ORDER BY
recent_datetime DESC
The previous query will displays reports for the differentes tuples of (attacker_IP, portno). Now, if you still want to concatenate ports numbers and occurences, you can query the previous one, like this:
SELECT
MAX(ip_port_logs.log_id) AS 'log_id',
MAX(ip_port_logs.recent_datetime) AS 'recent_datetime',
ip_port_logs.attacker_IP,
GROUP_CONCAT(ip_port_logs.portno SEPARATOR ', ') AS 'ports',
GROUP_CONCAT(ip_port_logs.occurences SEPARATOR ', ') AS 'ports_occurences'
FROM
( SELECT
MAX(log_id) AS 'log_id',
MAX(log_datetime) AS 'recent_datetime',
INET_NTOA(log_attacker_ip) AS 'attacker_IP',
log_service_port AS 'portno',
COUNT(*) AS 'occurences'
FROM
log_info
WHERE
log_datetime > NOW() - INTERVAL 30 DAY
AND
log_datetime <= NOW()
GROUP BY
attacker_IP, portno
ORDER BY
recent_datetime DESC ) AS ip_port_logs
GROUP BY
ip_port_logs.attacker_IP
answered Nov 24 '18 at 23:28
ShiderszShidersz
8,7502833
I suggest to first use the next query:
SELECT
MAX(log_id) AS 'log_id',
MAX(log_datetime) AS 'recent_datetime',
INET_NTOA(log_attacker_ip) AS 'attacker_IP',
log_service_port AS 'portno',
COUNT(*) AS 'occurences'
FROM
log_info
WHERE
log_datetime > NOW() - INTERVAL 30 DAY
AND
log_datetime <= NOW()
GROUP BY
attacker_IP, portno
ORDER BY
recent_datetime DESC
The previous query will displays reports for the differentes tuples of (attacker_IP, portno). Now, if you still want to concatenate ports numbers and occurences, you can query the previous one, like this:
SELECT
MAX(ip_port_logs.log_id) AS 'log_id',
MAX(ip_port_logs.recent_datetime) AS 'recent_datetime',
ip_port_logs.attacker_IP,
GROUP_CONCAT(ip_port_logs.portno SEPARATOR ', ') AS 'ports',
GROUP_CONCAT(ip_port_logs.occurences SEPARATOR ', ') AS 'ports_occurences'
FROM
( SELECT
MAX(log_id) AS 'log_id',
MAX(log_datetime) AS 'recent_datetime',
INET_NTOA(log_attacker_ip) AS 'attacker_IP',
log_service_port AS 'portno',
COUNT(*) AS 'occurences'
FROM
log_info
WHERE
log_datetime > NOW() - INTERVAL 30 DAY
AND
log_datetime <= NOW()
GROUP BY
attacker_IP, portno
ORDER BY
recent_datetime DESC ) AS ip_port_logs
GROUP BY
ip_port_logs.attacker_IP
answered Nov 24 '18 at 23:28
ShiderszShidersz
8,7502833
answered Nov 24 '18 at 23:28
ShiderszShidersz
8,7502833
answered Nov 24 '18 at 23:28
ShiderszShidersz
8,7502833
answered Nov 24 '18 at 23:28
ShiderszShidersz
8,7502833
8,7502833
Thank you for this great help. :D
– basyirstar
Nov 24 '18 at 23:46
add a comment |
Thank you for this great help. :D
– basyirstar
Nov 24 '18 at 23:46
Thank you for this great help. :D
– basyirstar
Nov 24 '18 at 23:46
Thank you for this great help. :D
– basyirstar
Nov 24 '18 at 23:46
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53463034%2fmysql-count-within-group-concat%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Just to clarify a litle from what I understood, you need the column
occurencesto be a comma separated string showing the quantity of occurences for each of the ports numbers displayed on the columnportno?. Although, this may be a different approach, why not to group by tupleipandport, then you can forgot of the port concatenation.– Shidersz
Nov 24 '18 at 23:05
@D.Smania yeah I want to display occurences for each port number, how is that tuple work? I never used it.
– basyirstar
Nov 24 '18 at 23:21
Check the answers, both are similars and will help you. It seems, I'm too slow writing still... :-)
– Shidersz
Nov 24 '18 at 23:39