/build/static/layout/Breadcrumb_cap_w.png

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:
Posted by: aragorn.2003 7 years ago
Red Belt
2

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
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ