‎11-12-2020 10:32 PM
‎11-13-2020 07:20 PM
SELECT
tblSoftwareUni.SoftwareName,
Count(*) AS Installations
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
INNER JOIN tblAssetCustom ON tblAssetCustom.AssetID = tblSoftware.AssetID
WHERE
tblAssetCustom.State = 1
AND ( tblSoftwareUni.softwareName Like '%Autodesk Revit 2016%'
OR tblSoftwareUni.softwareName Like '%AutoCAD 2018 - English%'
OR tblSoftwareUni.softwareName Like '%AutoCAD 2016 - English%')
GROUP BY
tblSoftwareUni.SoftwareName
ORDER BY
tblSoftwareUni.SoftwareName
‎11-13-2020 10:17 PM
RC62N wrote:
From the example code you offered, it looked like you were wanting to pull details to test your logic before refining it. Try this.SELECT
tblSoftwareUni.SoftwareName,
Count(*) AS Installations
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
INNER JOIN tblAssetCustom ON tblAssetCustom.AssetID = tblSoftware.AssetID
WHERE
tblAssetCustom.State = 1
AND ( tblSoftwareUni.softwareName Like '%Autodesk Revit 2016%'
OR tblSoftwareUni.softwareName Like '%AutoCAD 2018 - English%'
OR tblSoftwareUni.softwareName Like '%AutoCAD 2016 - English%')
GROUP BY
tblSoftwareUni.SoftwareName
ORDER BY
tblSoftwareUni.SoftwareName
If you don't want to ignore non-active (or any other state) computers, you can remove the JOIN to tblAssetCustom and the check for State=1 (active).
‎11-13-2020 05:42 PM
Where
( ( tblSoftwareUni.softwareName Like '%Autodesk Revit 2016%'
Or tblSoftwareUni.softwareName Like '%AutoCAD 2018 - English%'
)
)
Or ( tblSoftwareUni.softwareName Like '%AutoCAD 2016 - English%'
And tblAssetCustom.State = 1
)
tblAssetCustom.State = 1
AND ( tblSoftwareUni.softwareName Like '%Autodesk Revit 2016%'
Or tblSoftwareUni.softwareName Like '%AutoCAD 2018 - English%'
Or tblSoftwareUni.softwareName Like '%AutoCAD 2016 - English%'
)
‎11-13-2020 06:44 PM
RC62N wrote:
You'll want to start by cleaning up your WHERE clause.Where
( ( tblSoftwareUni.softwareName Like '%Autodesk Revit 2016%'
Or tblSoftwareUni.softwareName Like '%AutoCAD 2018 - English%'
)
)
Or ( tblSoftwareUni.softwareName Like '%AutoCAD 2016 - English%'
And tblAssetCustom.State = 1
)tblAssetCustom.State = 1
AND ( tblSoftwareUni.softwareName Like '%Autodesk Revit 2016%'
Or tblSoftwareUni.softwareName Like '%AutoCAD 2018 - English%'
Or tblSoftwareUni.softwareName Like '%AutoCAD 2016 - English%'
)
See if that gets you closer to your goal.
‎11-13-2020 04:07 PM
‎11-13-2020 05:07 PM
Brandon wrote:
If you hover over Software at the top of the webpage, then click on Windows Software, and you will see a list of all the software that Lansweeper has found, and how many computers it is installed on. I know you don't need this info, but you can click on the software title and see which computers that software is installed on.
‎11-13-2020 04:01 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now