Report for OS, Office Version and IE version

[chosmer]

Pretty new to trying to use SQL coding to create reports. I normally would just div in and do a bunch of studying. However, boss is wanting a report asap that will include Computer name, OS, Office version and IE version. I've been just running separate reports for each version of office that happens to have been installed on a domain computer and doing multiple tabs on my spreadsheet. Wasn't sure if someone had a faster way, or if I need to keep on with my current method.

Have a fantastic weekend and thanks for any help!!

[RCorbeil]

See here. You can also search the forum for messages containing "Internet Explorer 10". (Curiously, though several of the messages should also be found using "Internet Explorer 9", I see no results for that.)

[chosmer]

Thanks! I'll give it a try and look up the info about identifying IE9+.

[RCorbeil · Accepted solution]

SELECT TOP 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tsysOS.OSname,
  tblAssets.SP,
  CASE
    WHEN tblComputersystem.SystemType LIKE 'X86%' THEN '32-bit'
    WHEN tblComputersystem.SystemType LIKE 'x64%' THEN '64-bit'
    ELSE ''
  END AS Bitness,
  tblAssets.Domain,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.IPAddress,
  tblSoftwareUni.softwareName,
  tbldomainroles.Domainrolename
FROM
  tblAssets
  INNER JOIN tblAssetCustom    ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes    ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tsysOS            ON tblAssets.OScode = tsysOS.OScode
  INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
  INNER JOIN tblSoftware       ON tblAssets.AssetID = tblSoftware.AssetID
  INNER JOIN tblSoftwareUni    ON tblSoftwareUni.SoftID = tblSoftware.softID
  INNER JOIN tbldomainroles    ON tbldomainroles.Domainrole = tblComputersystem.Domainrole
WHERE
  (tblSoftwareUni.softwareName LIKE 'Windows Internet Explorer%'
   OR (tblSoftwareUni.SoftwarePublisher LIKE 'Microsoft%'
       AND tblSoftwareUni.softwareName     LIKE 'Microsoft Office%'
       AND tblSoftwareUni.softwareName NOT LIKE '%click-to-run%'
       AND tblSoftwareUni.softwareName NOT LIKE '%starter%'
       AND tblSoftwareUni.softwareName NOT LIKE '%viewer%'
       AND tblSoftwareUni.softwareName NOT LIKE '%meeting%'
       AND tblSoftwareUni.softwareName NOT LIKE '%add-in%'
       AND tblSoftwareUni.softwareName NOT LIKE '%web components%'
       AND tblSoftwareUni.softwareName NOT LIKE '%Visio%'
       AND tblSoftwareUni.softwareName NOT LIKE '%Project%'))
  AND tblAssetCustom.State = 1
ORDER BY
  tblAssets.AssetName,
  tblSoftwareUni.SoftwarePublisher,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion

You may need to add further exclusions to the list of softwhere with names that start with "Microsoft Office" but aren't Microsoft Office. The list above is just stuff that I've detected at my site.

Note that MSIE 9 and later are not recorded by Microsoft as installed applications the way that most everything else is. Detecting/identifying MSIE 9+ has been discussed previously in the forums.