cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
smozie
Engaged Sweeper
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.
2 REPLIES 2
Esben_D
Lansweeper Employee
Lansweeper Employee
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
smozie
Engaged Sweeper
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.