→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
IT-riha
Engaged Sweeper III
Hi,
i need a Report with the Information how many Office 2000 - 2003 (all Versions) Installations are installed on the Clients.
The Report must contain the Location of the PC and the Version.
But it will not work. 😞

Can you help me?

Select Top 1000000 tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
Count(tblSoftware.SoftwareID) As Total,
tsysIPLocations.IPLocation,
tblAssets.IPAddress,
tsysIPLocations.StartIP,
tsysIPLocations.EndIP
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 tsysIPLocations On tsysIPLocations.StartIP = tblAssets.IPAddress
And tsysIPLocations.EndIP = tblAssets.IPAddress
Where tblSoftwareUni.SoftwarePublisher Like '%microsoft%' And
tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion
Having tblSoftwareUni.softwareName Like '%Office%2003%'
Order By Total Desc,
Software

1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
Your join to tsysIPlocations was not quite correct. You can find an explanation of how to include the IP location in a report here. Also you need to ensure that all columns which you list in your report either have grouping enabled or are used by an aggregate function. Please find a modified report below.

Select Top 1000000 tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
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 tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Where (tblSoftwareUni.softwareName Like 'Microsoft office % 2003%' Or
tblSoftwareUni.softwareName Like 'Microsoft office % 2000%') And
tblSoftwareUni.softwareName Not Like '%viewer%' And
tblSoftwareUni.softwareName Not Like '%tools%' And
tblSoftwareUni.softwareName Not Like '%designer%' And tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tsysIPLocations.IPLocation
Order By Total Desc,
Software

View solution in original post

1 REPLY 1
Daniel_B
Lansweeper Alumni
Your join to tsysIPlocations was not quite correct. You can find an explanation of how to include the IP location in a report here. Also you need to ensure that all columns which you list in your report either have grouping enabled or are used by an aggregate function. Please find a modified report below.

Select Top 1000000 tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
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 tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Where (tblSoftwareUni.softwareName Like 'Microsoft office % 2003%' Or
tblSoftwareUni.softwareName Like 'Microsoft office % 2000%') And
tblSoftwareUni.softwareName Not Like '%viewer%' And
tblSoftwareUni.softwareName Not Like '%tools%' And
tblSoftwareUni.softwareName Not Like '%designer%' And tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tsysIPLocations.IPLocation
Order By Total Desc,
Software