SQL Server Reports

[Chad]

After many failed results/reports I have tried running to get this to work, I'm reaching out to everyone to see if they have had any luck with this. What I am trying to do is to get a report created that would list all devices running Microsoft SQL Server 2005 and below, as well as listing version (Express, Stanard, etc). I have had very little success as I usually get results that will also include Management Studio ONLY installs, which make the report inaccurate. Curious if anyone else has had luck running this type of report or has an example they don't mind posting.

[Chad]

Excellent, I guess I just didn't know what to properly set for my LIKE and OR fields properly. I'm not great at reports so this will help me with many things in the future. Thank you for your help and responses! 🙂

[Susan_A · Accepted solution]

If you want to list the edition (Express, Standard...) as well, you'll need to run a report based on the tblSqlServers database table, e.g. the built-in "Software: All SQL server editions" report found in the Reports tab. The regular software reports mimic Add/Remove Programs on the client machines, and SQL editions are usually not listed in Add/Remove Programs.

The aforementioned SQL report won't list components like Management Studio, but keep in mind that it requires the appropriate WMI provider to be installed on your machines. See this and this link for more information.

[RCorbeil]

If you know keywords in the software name that you do/don't want, you could set up something like this:

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName
FROM
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
WHERE
  tblAssetCustom.State = 1
  AND tblSoftwareUni.softwareName LIKE 'Microsoft SQL Server%'
  AND (    tblSoftwareUni.softwareName LIKE '%2000%'
        OR tblSoftwareUni.softwareName LIKE '%2005%'
        OR tblSoftwareUni.softwareName LIKE '%Compact%'
      )
  AND tblSoftwareUni.softwareName NOT LIKE '%Backward compatibility'
  AND tblSoftwareUni.softwareName NOT LIKE '%Books Online%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Query Tools%'
ORDER BY
  tblSoftwareUni.softwareName,
  tblAssets.AssetName

That should produce a list of all Microsoft SQL Server 2000, 2005 or Compact editions while excluding references to Backward compatibility, Books Online or Query Tools (all results that showed up in my inventory when I was trying this out).

If you're less certain of what's in your inventory, you might prefer to just build an exclusion list, though it can get a little cumbersome. e.g.

WHERE
  tblAssetCustom.State = 1
  AND tblSoftwareUni.softwareName LIKE 'Microsoft SQL Server%'
  AND tblSoftwareUni.softwareName NOT LIKE '%App Framework'
  AND tblSoftwareUni.softwareName NOT LIKE '%Backward compatibility'
  AND tblSoftwareUni.softwareName NOT LIKE '%Books Online%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Command Line Utilities'
  AND tblSoftwareUni.softwareName NOT LIKE '%Compiler Service'
  AND tblSoftwareUni.softwareName NOT LIKE '%Data Tools%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Language Service'
  AND tblSoftwareUni.softwareName NOT LIKE '%Management Objects%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Management Studio%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Native Client'
  AND tblSoftwareUni.softwareName NOT LIKE '%Policies'
  AND tblSoftwareUni.softwareName NOT LIKE '%Query Tools%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Report Builder%'
  AND tblSoftwareUni.softwareName NOT LIKE '%ScriptDom'
  AND tblSoftwareUni.softwareName NOT LIKE '%Server Browser'
  AND tblSoftwareUni.softwareName NOT LIKE '%Setup%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Support Files'
  AND tblSoftwareUni.softwareName NOT LIKE '%System CLR Types%'
  AND tblSoftwareUni.softwareName NOT LIKE '%Upgrade Advisor'
  AND tblSoftwareUni.softwareName NOT LIKE '%VSS Writer'
  AND tblSoftwareUni.softwareName NOT LIKE 'Hotfix%'
  AND tblSoftwareUni.softwareName NOT LIKE '%2008%'
  AND tblSoftwareUni.softwareName NOT LIKE '%2012%'