cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Tholmes
Engaged Sweeper III
Installed, and we dont care about the version, we need to know if it's installed, we know what licences are needed / used, so that is fine.

I thought this might be a starting point, but not the correct layout for us, need a little more complexity.
We want a list of the software, not which comoputers, just how many computers it is installed on and that is it, I have about 30 installations we need to check, don't care about the version either.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Username,
tblADusers.Displayname As [User],
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
tblSoftwareUni.SoftwarePublisher As publisher,
tsysOS.Image As icon
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
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)
Order By tblAssets.AssetName,
software,
version

I understand I will have to manually sift through and 'not like' the similar sounding ones, which I am fine with.

Thanks again in advance
Regards
Tim
7 REPLIES 7
RCorbeil
Honored Sweeper II
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).
Tholmes
Engaged Sweeper III
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).


So, thanks for this, we have narrowed it down and refined to how we want and it looks awesome, is there a way to show who or what it is installed on, say, if we click on the software installation, (kind of hyperlinked to the software search) Adobe Creative Cloud, then it would list all the computers that it is listed on. I know this is a big ask, but wondering if it can be done.
Thanks as always in advance
Tim
RCorbeil
Honored Sweeper II
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.
Tholmes
Engaged Sweeper III
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.



I copied code from an existing report, 'Chart: Windows operating systems'

and edited it, almost there I think

Select Top 1000000 tblSoftwareUni.softwareName As software,
Count(tblSoftwareUni.softwareName) As Total
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
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
Group By tblSoftwareUni.softwareName
Order By Total Desc

Thanks for the guidance, not sure if I got what you were trying to say, but here is what I am using.

brandon_jones
Champion Sweeper III
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.
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.


Thanks for the direction, but we want a list of about 10-15 installations, so rather than having the whole list, or exporting it to XL and then me deleting 2340 lines.
Hope that makes sense.
brandon_jones
Champion Sweeper III
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.