Notification

Icon
Error

Software install, just how many computers it is installed on - We need a report to show us how many computers have our software

Posted: Thursday, November 12, 2020 10:32:34 PM(UTC)
TimHolmes1973

TimHolmes1973

Member Original PosterPosts: 20
0
Like
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
Brandon
#1Brandon Member Posts: 57  
posted: 11/13/2020 4:01:15 PM(UTC)
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
#2Brandon Member Posts: 57  
posted: 11/13/2020 4:07:29 PM(UTC)
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.
TimHolmes1973
#3TimHolmes1973 Member Original PosterPosts: 20  
posted: 11/13/2020 5:07:48 PM(UTC)
Originally Posted by: Brandon Go to Quoted Post
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.
RC62N
#4RC62N Member Posts: 488  
posted: 11/13/2020 5:42:51 PM(UTC)
You'll want to start by cleaning up your WHERE clause.
Code:
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
     )

Code:
  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.
TimHolmes1973
#5TimHolmes1973 Member Original PosterPosts: 20  
posted: 11/13/2020 6:44:29 PM(UTC)
Originally Posted by: RC62N Go to Quoted Post
You'll want to start by cleaning up your WHERE clause.
Code:
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
     )

Code:
  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.

RC62N
#6RC62N Member Posts: 488  
posted: 11/13/2020 7:20:26 PM(UTC)
From the example code you offered, it looked like you were wanting to pull details to test your logic before refining it. Try this.
Code:
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).
TimHolmes1973
#7TimHolmes1973 Member Original PosterPosts: 20  
posted: 11/13/2020 10:17:10 PM(UTC)
Originally Posted by: RC62N Go to Quoted Post
From the example code you offered, it looked like you were wanting to pull details to test your logic before refining it. Try this.
Code:
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

Active Discussions

Report Center Default Browser and Version
by  RC62N   Go to last post Go to first unread
Last post: Yesterday at 8:00:54 PM(UTC)
Lansweeper Trying to make a custom report that sorts assets by state
by  mzipperer  
Go to last post Go to first unread
Last post: 11/25/2020 5:36:52 PM(UTC)
Lansweeper Report - All Assets with Specfic Default Gateway
by  RC62N   Go to last post Go to first unread
Last post: 11/25/2020 4:18:08 PM(UTC)
Report Center Windows update report
by  ToeJoe  
Go to last post Go to first unread
Last post: 11/24/2020 3:39:00 PM(UTC)
Lansweeper Report - assets
by  Andy.S   Go to last post Go to first unread
Last post: 11/23/2020 2:42:01 PM(UTC)
Lansweeper Run Report on PCs from a list
by  RC62N  
Go to last post Go to first unread
Last post: 11/16/2020 4:23:12 PM(UTC)