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: 487  
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: 487  
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

Lansweeper Microsoft EDGE browser support
by  Vapoured   Go to last post Go to first unread
Last post: Today at 4:54:30 AM(UTC)
Lansweeper Asset Management - Unattached assets
by  MrDiamond  
Go to last post Go to first unread
Last post: Yesterday at 8:15:16 PM(UTC)
Lansweeper Help Desk not disabling for regular users?
by  Maveritsch   Go to last post Go to first unread
Last post: Yesterday at 3:22:03 PM(UTC)
Lansweeper Lansweeper Fresh Install Hanging, High CPU, Unusable
by  ebars  
Go to last post Go to first unread
Last post: 11/24/2020 7:53:38 PM(UTC)
Lansweeper Forwarding tickets to regular email recipents
by  ChristerT   Go to last post Go to first unread
Last post: 11/24/2020 9:48:15 AM(UTC)
Lansweeper Outlook task and calendar
by  Squack the Duck  
Go to last post Go to first unread
Last post: 11/24/2020 12:40:37 AM(UTC)
Lansweeper Cap on number of routers?
by  MarkPayton   Go to last post Go to first unread
Last post: 11/23/2020 10:04:12 PM(UTC)
Lansweeper Authenticate from CLI
by  blunderdog  
Go to last post Go to first unread
Last post: 11/23/2020 9:57:04 PM(UTC)