→ 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: 
jschlepp
Engaged Sweeper
This may not be possible but the goal is to show how much of all Microsoft titles are used by location. Below would be a sample of what I am trying to achieve.

Microsoft Office Professional 2013 NorthAmerica 400
Microsoft Office Professional 2013 Africa 50
Microsoft Office Professional 2007 Europe 200
Microsoft Office Professional 2010 Asia 425

I have read through Adding IP locations to a report and attempted to implement what is suggested in the post without success.

Any advice/suggestions are welcomed and appreciated.
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
Using the two resources you refer to, I come up with:
Select Top 1000000
tblSoftwareUni.softwareName As Software,
tsysIPLocations.IPLocation,
Count(tblSoftware.SoftwareID) 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 JOIN tsysIPLocations ON tsysIPLocations.EndIP >= tblAssets.IPNumeric AND tsysIPLocations.StartIP <= tblAssets.IPNumeric
WHERE
tblSoftwareUni.SoftwarePublisher Like N'%microsoft%'
And tblAssetCustom.State = 1
GROUP BY
tblSoftwareUni.softwareName,
tsysIPLocations.IPLocation
ORDER BY
Software,
Total Desc

You'll have to add filters if you don't want every single piece of Microsoft software, of course.

View solution in original post

2 REPLIES 2
jschlepp
Engaged Sweeper
RC62N,
Thank you for the quick reply. I will give it a go and let you know how it turns out.
RCorbeil
Honored Sweeper II
Using the two resources you refer to, I come up with:
Select Top 1000000
tblSoftwareUni.softwareName As Software,
tsysIPLocations.IPLocation,
Count(tblSoftware.SoftwareID) 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 JOIN tsysIPLocations ON tsysIPLocations.EndIP >= tblAssets.IPNumeric AND tsysIPLocations.StartIP <= tblAssets.IPNumeric
WHERE
tblSoftwareUni.SoftwarePublisher Like N'%microsoft%'
And tblAssetCustom.State = 1
GROUP BY
tblSoftwareUni.softwareName,
tsysIPLocations.IPLocation
ORDER BY
Software,
Total Desc

You'll have to add filters if you don't want every single piece of Microsoft software, of course.