modify Software: All Microsoft software report

[jschlepp]

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.

[jschlepp]

RC62N,
Thank you for the quick reply. I will give it a go and let you know how it turns out.

[RCorbeil · Accepted solution]

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.