SQL Report - Help Finding Duplicate IPs
04/17/2017 1102 views
I am having some issues writing a report for KACE to show me all of the computers with duplicate IP addresses in the inventory. I am wanting it to show the last sync date, system name, and IP address for BOTH records. Currently I have played with it a bit and I can see that without the last line (and the count) I can see both records, along with all other entries. I currently have some basic SQL where I can view one of each duplicate entry, however it does not display both records with the duplicate IPs. I am sure it is something wrong with my group statement, or maybe there is a better approach to getting it to filter them out. I will post what I have below so you may help with recommendations. I am not very experienced in SQL so any help is appreciated.
SELECT NAME "System Name",LAST_SYNC "Last Sync",IP "IP", count(IP)
GROUP BY IP HAVING (count(IP)>1)
Answer Chosen by the Author
Please log in to answer
I think there´s a better SQL than mine, but it works for the moment.
SELECT m.NAME as 'System Name', m.LAST_SYNC as 'Last Sync', m.IP
FROM MACHINE as m, (SELECT m1.IP, COUNT(m1.IP) FROM MACHINE as m1 GROUP BY m1.IP HAVING (COUNT(m1.IP)>1)) as sub
WHERE m.IP = sub.IPORDER BY IP
Answered 04/18/2017 by: aragorn.2003