Search code examples
sqlreportspiceworks

How to filter out the duplicates?

I'm an I.T. co-op student on my first co-op. I haven't yet learned SQL in class but I've been asked to set up a report with SQL by my I.T. manager. I've never written anything in SQL before.

I need to list all the devices in the office, their users, and list a set of specific software that each device has installed on it. I managed to google most of the answer but now I need to filter out the duplicates. I am using SpiceWorks to make this report.

I'm receiving duplicates because most of these softwares have multiple version numbers. Here is my code:

SELECT devices.NAME
    ,devices.primary_owner_name
    ,software.NAME AS installed_applications
    ,software_installations.version
FROM devices
INNER JOIN software_installations ON (devices.id = software_installations.computer_id)
INNER JOIN software ON (software.id = software_installations.software_id)
WHERE (
        devices.device_type IN (
            'Server'
            ,'Desktop'
            ,'Laptop'
            )
        AND software.NAME = 'AutoCAD LT 2011 - English'
        OR software.NAME = 'AutoCAD LT 2012 - English'
        OR software.NAME = 'AutoCAD LT 2013 - English'
        OR software.NAME = 'AutoCAD LT 2014 - English'
        OR software.NAME = 'AutoCAD LT 2015 - English'
        OR software.NAME = 'AutoCAD LT 2016 - English'
        OR software.NAME = 'BillQuick'
        OR software.NAME LIKE '%AutoDESK DWG TrueView%'
        OR software.NAME LIKE '%AutoDesk BDS%'
        OR software.NAME LIKE '%BillQuick Agent%'
        OR software.NAME = 'Microsoft Office 2010 Project'
        OR software.NAME = 'Microsoft Office 2010 Professional Plus'
        OR software.NAME = 'Microsoft Project Standard'
        OR software.NAME LIKE '%Vehicle Tracking%'
        OR software.NAME LIKE '%Autodesk Vehicle Tracking 2015 on AutoCAD 2015%'
        OR software.NAME LIKE '%AutoTURN%'
        OR software.NAME = 'Microsoft Office 2000'
        OR software.NAME = 'Microsoft Office 2007'
        OR software.NAME = 'Microsoft Office 2007 Professional Hybrid'
        OR software.NAME = 'Microsoft Office 2007 Project Standard'
        OR software.NAME = 'Microsoft Office 2010 Professional'
        OR software.NAME = 'Microsoft Office 2010 Professional Plus'
        OR software.NAME = 'Microsoft Office 2010 Project'
        OR software.NAME = 'Microsoft Office 2010 Project Professional'
        OR software.NAME = 'Microsoft Office 2010 Project Standard'
        OR software.NAME = 'Microsoft Office 365 - en-us'
        OR software.NAME = 'Microsoft Office 365 ProPlus - en-us'
        OR software.NAME = 'Microsoft Office Home and Business 2013 - en-us'
        OR software.NAME = 'Microsoft Office Professional 2013 - en-us'
        OR software.NAME = 'Microsoft Project Professional'
        OR software.NAME = 'Microsoft Project Standard'
        OR software.NAME = 'Microsoft Project Standard 2013 - en-us'
        )
ORDER BY devices.NAME
    ,software.NAME

A sample of what I'm getting is this:

mcgi00080 Santosh Bhattarai BillQuick 14.0.18.1

mcgi00080 Santosh Bhattarai BillQuick 16.0.12.1

mcgi00080 Santosh Bhattarai BillQuick 16.0.16.0

mcgi00080 Santosh Bhattarai BillQuick 16.0.17.25

mcgi00080 Santosh Bhattarai BillQuick 16.0.15.6

mcgi00080 Santosh Bhattarai BillQuick 16.0.18.2

mcgi00088 Christine Chin BillQuick 14.0.16.4

mcgi00088 Christine Chin BillQuick 14.0.16.6

mcgi00088 Christine Chin BillQuick 14.0.17.20

mcgi00088 Christine Chin BillQuick 15.0.12.10

mcgi00088 Christine Chin BillQuick 14.0.18.1

mcgi00088 Christine Chin BillQuick 16.0.12.1

mcgi00088 Christine Chin BillQuick 16.0.16.9

mcgi00088 Christine Chin BillQuick 16.0.15.6

mcgi00088 Christine Chin BillQuick 16.0.17.25

mcgi00190 administrator AutoCAD LT 2011 - English 18.1.49.0

mcgi00190 administrator AutoCAD LT 2011 - English 2.1

mcgi00190 administrator AutoCAD LT 2011 - English 18.1.116.0

mcgi00198 administrator AutoCAD LT 2012 - English 18.2.51.0

mcgi00080 Santosh Bhattarai AutoCAD LT 2013 - English 19.0.55.0

mcgi00080 Santosh Bhattarai AutoCAD LT 2013 - English 19.0.204.0

How can I make the report only include the latest version of each software, but for each person separately? So that if Santosh's latest version is 16.0.18.2 but Jeff's latest version is 16.0.20.0 that it will show Santosh only one version of BillQuick 16.0.18.2 and for Jeff only one version of BillQuick: 16.0.20.0 Like this:

mcg-file Admin BillQuick 14.0.16.4

mcgi00080 Santosh Bhattarai BillQuick 16.0.18.2

mcgi00088 Christine Chin BillQuick 16.0.17.25

mcgi00089 Dawn Myles BillQuick 16.0.18.2

Any suggestions or solutions will be greatly appreciated.

Solution

  • I exported the report to excel, deleted the duplicates and then saved it as a .pdf. Took about 10 extra minutes but it was easier than trying to teach myself SQL in 2 days.