192.168.1.1 21
192.168.1.1 23
192.168.1.1 80
192.168.1.2 21
192.168.1.2 80
192.168.1.3 23
IP 21 23 80
192.168.1.1 1 1 1
192.168.1.2 1 0 1
192.168.1.3 0 1 0
select ip,sum(if(portid='21',1,0)) AS ftp, sum(if(portid='23',1,0)) as telnet, sum(if(portid='80',1,0)) as http, sum(if(portid='443',1,0)) as https, sum(if(portid='25',1,0)) as smtp, sum(if(portid='1521',1,0)) as oracle, sum(if(portid='445' or portid ='139',1,0)) as windows from fulltcpnmapports group by ip order by inet_aton(ip);