→ 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: 
chosmer
Engaged Sweeper
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!!
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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.

View solution in original post

3 REPLIES 3
RCorbeil
Honored Sweeper II
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
Engaged Sweeper
Thanks! I'll give it a try and look up the info about identifying IE9+.
RCorbeil
Honored Sweeper II
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.