1 Reply Latest reply on Aug 28, 2014 9:00 AM by Julien Devienne

    Query to find devices sharing the same Host ID

    Eduardo Chapa
      Share This:



      I'm finding a lot of devices that share the same Host ID (Serial Number), meaning they were probably formatted and then had the agent reinstalled, or just renamed then the agent reinstalled.


      Is there a way to create a "Free Query" that brings up all devices that have the same Host ID (or any other machine-unique ID)? In oder words, all the Host IDs that are shared with more than one Device.


      I found about this first by creating a report grouping by Host ID and making it the header, that brought several devices with the same Host ID, obviously one (or more) of them no longer reporting to the system, just one active.


      We then decided to delete the old duplicate devices but since we have so many it was very slow going back and forth between the html/csv report and the console, then finding the device in its device group (search won't let you delete - why?) and finally deleting from there.


      Then I decided to write the following free query:


      SELECT AssetTag, COUNT(AssetTag) as Duplicates FROM Devices GROUP BY Devices.AssetTag ORDER BY Duplicates DESC


      ...but when verifying the SQL syntax, the console wouldn't allow me to use COUNT in the SELECT (even if this works when executed directly on the DB) giving me the following error:

      The SELECT of the query may not contain operators like AVG, COUNT, MAX, MIN and SUM or sql commands like UNION, INTERSECT, EXCEPT, MINUS...


      So I had no choice but to pull all the Host IDs with more than one device directly from the DB using the previous query, paste the results to Excel, then use nested formulas to create a working free query that selected devices where AssetTag was equal to each and all of those (separated by "OR"). From there, I created a device group out of the query, and I could delete the duplicate old devices.


      This last approach is working, although the free sql query seems to be limited to a certain length because it wouldn't let me paste the whole batch of Host IDs, so I had to split it in two.


      If someone has a better and easier way to find devices that share the same Host ID (and delete the old duplicates), please let me know!