Software Report by IP Location

[Srikanth08]

Hi,

I need Report query for software list based on IP location..

Please help

[Hemoco]

Sample report:

Select Top 1000000 tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tsysIPLocations.IPLocation,
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
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssetCustom.State = 1
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tsysIPLocations.IPLocation
Order By Software,
Version,
tsysIPLocations.IPLocation

[Srikanth08]

Yep..like for example lets take dept MAT where what are the software installed and count of that

[Hemoco]

Could you clarify what you mean by "group based on IP location". Are you interested in counting your software installations per IP location?

[Srikanth08]

Hi Thanks,

this report is very huge in sizw...softwares are listed by individual assets..is it possible to group based on ip location

[Hemoco]

Sample report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
tblSoftwareUni.SoftwarePublisher As publisher,
tsysOS.Image As icon,
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 tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where tblAssetCustom.State = 1
Order By tsysIPLocations.IPLocation,
tblAssets.AssetName,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion