Software install, just how many computers it is installed on

[Tholmes]

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

[RCorbeil]

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]

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]

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]

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]

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.

[Tholmes]

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]

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.