Software counts/audit

[francisswest]

Looking to make a report that will compile the following:

Name of software | Total Count of Install locations | list of computers/users it is installed for | In a certain IP Location

Any thoughts?

[Henrik_Hansen]

Hi Tom.P


Thanks a lot that what just what I needed 🙂



Best regards
Henrik Hansen

[Tom_P]

When working with a union between two queries, make sure the amount of columns for each query is the same. As this is not the case with the second query, the report builder is not be able to save the changes to the existing query. We have corrected the query so the additional 'tsysIPLocation' information is now correctly added to all 3 queries. In addition, we added the field to the 'Group by' settings as this is needed for the SQL statement to be fully correct.

When the report is added to the Lansweeper database and it is run, clicking on the software name will automatically open a detailed report showing you the assets that have the selected software installed.

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,
	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, tsysIPLocations.IPLocation

Order By Total Desc, Software

[Henrik_Hansen]

I´m looking for the same, the report for counting the software globally works fine:

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
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
Where tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tsysAssetTypes.AssetTypename
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
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
Where tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
tblLinuxSoftware.Version,
tblSoftwareUni.SoftwarePublisher,
tsysAssetTypes.AssetTypename
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
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
Where tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
tblMacApplications.Version,
tblSoftwareUni.SoftwarePublisher,
tsysAssetTypes.AssetTypename
Order By Total Desc,
Software


But when I add "tsysIPLocation" it gets removed again when I run the report:

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,
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 And tsysIPLocations.IPLocation Like '%EBJ%'
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tsysAssetTypes.AssetTypename
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
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
Where tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
tblLinuxSoftware.Version,
tblSoftwareUni.SoftwarePublisher,
tsysAssetTypes.AssetTypename
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
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
Where tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
tblMacApplications.Version,
tblSoftwareUni.SoftwarePublisher,
tsysAssetTypes.AssetTypename
Order By Total Desc,
Software



Any ideas 🙂