Software Inventory by Domain

[smozie]

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.

[Esben_D]

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]

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.