This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
Community FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Software Inventory by Domain

smozie
Engaged Sweeper

‎10-18-2018 11:42 PM

I am trying to get a count of software on all computers by domain. We have a few different domains and I can only seem to get the IP Location to work, here is my code.


  Select Top 1000000 tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tsysAssetTypes.AssetTypename As [Asset Type],
  Count(tblSoftware.AssetID) As Total,
  tblAssets.Domain,
  tsysIPLocations.IPLocation
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 tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
Where tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblSoftwareUni.SoftwarePublisher,
  tsysAssetTypes.AssetTypename,
  tsysIPLocations.IPLocation
Union
Select Top 1000000 tblSoftwareUni.softwareName As Software,
  tblLinuxSoftware.Version As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tsysAssetTypes.AssetTypename As [Asset Type],
  Count(tblAssets.AssetID) As Total,
  tsysIPLocations.IPLocation
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblLinuxSoftware On tblAssets.AssetID = tblLinuxSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
    tblLinuxSoftware.SoftwareUniID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
Where tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
  tblLinuxSoftware.Version,
  tblSoftwareUni.SoftwarePublisher,
  tsysAssetTypes.AssetTypename,
  tsysIPLocations.IPLocation
Union
Select Top 1000000 tblSoftwareUni.softwareName As Software,
  tblMacApplications.Version As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tsysAssetTypes.AssetTypename As [Asset Type],
  Count(tblAssets.AssetID) As Total,
  tsysIPLocations.IPLocation
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblMacApplications
    On tblAssets.AssetID = tblMacApplications.AssetID
  Inner Join tblSoftwareUni On tblMacApplications.softid = tblSoftwareUni.SoftID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
Where tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
  tblMacApplications.Version,
  tblSoftwareUni.SoftwarePublisher,
  tsysAssetTypes.AssetTypename,
  tblAssets.Domain,
  tsysIPLocations.IPLocation,
Order By Total Desc,
  Software


If I could get the domain to report, that would make this report a lot more usable because we have tons of different IP locations in our offices.
Labels:
0 Kudos
2 REPLIES 2
Esben_D
Lansweeper Employee
Lansweeper Employee

‎10-23-2018 05:19 PM

From your explanation, I gather that you simply want the name of the asset and a count of the total number of software on the machine.
Having data like software name and version showing conflicts with this because then you're counting individual named or versioned software (which will most likely just be one). Additionally, you're best off doing a count of the ID of the software installations (not the asset ID).

See if this is more what you were aiming for:

Select Top 1000000 tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As [Asset Type],
  Count(tblSoftware.SoftwareID) As Total,
  tsysIPLocations.IPLocation,
  tblAssets.Domain
From tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
Where tblAssetCustom.State = 1
Group By tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysIPLocations.IPLocation,
  tblAssets.Domain
Union
Select Top 1000000 tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As [Asset Type],
  Count(tblLinuxSoftware.SoftwareID) As Total,
  tsysIPLocations.IPLocation,
  tblAssets.Domain
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblLinuxSoftware On tblAssets.AssetID = tblLinuxSoftware.AssetID
  Inner Join tblSoftwareUni On
    tblSoftwareUni.SoftID = tblLinuxSoftware.SoftwareUniID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
Where tblAssetCustom.State = 1
Group By tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysIPLocations.IPLocation,
  tblAssets.Domain,
  tblSoftwareUni.softwareName,
  tblLinuxSoftware.Version,
  tblSoftwareUni.SoftwarePublisher
Union
Select Top 1000000 tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As [Asset Type],
  Count(tblMacApplications.ApplicationID) As Total,
  tsysIPLocations.IPLocation,
  tblAssets.Domain
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblMacApplications On
    tblAssets.AssetID = tblMacApplications.AssetID
  Inner Join tblSoftwareUni On tblMacApplications.softid = tblSoftwareUni.SoftID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
Where tblAssetCustom.State = 1
Group By tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysIPLocations.IPLocation,
  tblAssets.Domain,
  tblSoftwareUni.softwareName,
  tblMacApplications.Version,
  tblSoftwareUni.SoftwarePublisher
Order By Domain
0 Kudos
smozie
Engaged Sweeper
In response to Esben_D

‎10-23-2018 07:23 PM

Charles.X wrote:
From your explanation, I gather that you simply want the name of the asset and a count of the total number of software on the machine.
Having data like software name and version showing conflicts with this because then you're counting individual named or versioned software (which will most likely just be one). Additionally, you're best off doing a count of the ID of the software installations (not the asset ID).

See if this is more what you were aiming for:

Select Top 1000000 tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As [Asset Type],
  Count(tblSoftware.SoftwareID) As Total,
  tsysIPLocations.IPLocation,
  tblAssets.Domain
From tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
Where tblAssetCustom.State = 1
Group By tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysIPLocations.IPLocation,
  tblAssets.Domain
Union
Select Top 1000000 tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As [Asset Type],
  Count(tblLinuxSoftware.SoftwareID) As Total,
  tsysIPLocations.IPLocation,
  tblAssets.Domain
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblLinuxSoftware On tblAssets.AssetID = tblLinuxSoftware.AssetID
  Inner Join tblSoftwareUni On
    tblSoftwareUni.SoftID = tblLinuxSoftware.SoftwareUniID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
Where tblAssetCustom.State = 1
Group By tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysIPLocations.IPLocation,
  tblAssets.Domain,
  tblSoftwareUni.softwareName,
  tblLinuxSoftware.Version,
  tblSoftwareUni.SoftwarePublisher
Union
Select Top 1000000 tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As [Asset Type],
  Count(tblMacApplications.ApplicationID) As Total,
  tsysIPLocations.IPLocation,
  tblAssets.Domain
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblMacApplications On
    tblAssets.AssetID = tblMacApplications.AssetID
  Inner Join tblSoftwareUni On tblMacApplications.softid = tblSoftwareUni.SoftID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
Where tblAssetCustom.State = 1
Group By tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysIPLocations.IPLocation,
  tblAssets.Domain,
  tblSoftwareUni.softwareName,
  tblMacApplications.Version,
  tblSoftwareUni.SoftwarePublisher
Order By Domain


Not exactly. I am trying to find all the software, their versions, and the count within one of our domains. The asset inventory is not important, I am just trying to find the software and its count by domain instead of IP location because we have tons of IP locations per domain.

0 Kudos

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now