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)

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

2
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
Red Belt

  • 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.
    • yes, i´ve changed my statement again cause the code element in the editor produced this.
Please log in to comment

Answers

Answer this question or Comment on this question for clarity