SQL Report - Help Finding Duplicate IPs
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)
FROM MACHINE
GROUP BY IP HAVING (count(IP)>1)
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
aragorn.2003
7 years ago
Top 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
Comments:
-
This worked! Thank you very much, I now understand how I was approaching this wrong. Just a small edit for a space between "sub.IP ORDER" is the only error I noticed as written. - Pandas Eat 7 years ago
-
yes, i´ve changed my statement again cause the code element in the editor produced this. - aragorn.2003 7 years ago
