Report Browser versions

[Alulux]

hello, I would like to have a report that shows me the different versions of Internet Explorer 6-10. Is this possible?

Thanks...

[Hemoco]

Internet Explorer file scanning instructions and a sample report can also be found here.

[RCorbeil · Accepted solution]

Alulux wrote:
hello, I would like to have a report that shows me the different versions of Internet Explorer 6-10. Is this possible?

Thanks...

You would think it would be easy, wouldn't you? IE 6-8 can be found in the software scan, but for IE 9 and 10 you'll need to set up a custom file scan. See this form thread as that part of the problem has already been covered.

Once you've got that returning results, you could set up something like this:

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Description,
  tblAssets.Lastseen,
  tsysOS.Image As icon,
  tblSoftwareUni.SoftwarePublisher,
  tblSoftware.softwareVersion,
  tblSoftwareUni.softwareName,
  tblSoftware.Installdate
From
  tblAssets
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblSoftware On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where
  tblAssetCustom.State = 1
  And tblComputersystem.Domainrole < 2
  And tblSoftwareUni.SoftwarePublisher Like 'Microsoft%'
  And tblSoftwareUni.softwareName Like '%Internet Explorer%'

Union All

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Description,
  tblAssets.Lastseen,
  tsysOS.Image As icon,
  'Microsoft Corporation' As Publisher,
  tblFileVersions.FileVersion,
  'Windows Internet Explorer ' + Left(tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion) - 1) As SoftwareName,
  tblFileVersions.CreationDate As InstallDate
From
  tblAssets
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblFileVersions On tblFileVersions.AssetID = tblAssets.AssetID
Where
  tblAssetCustom.State = 1
  And tblComputersystem.Domainrole < 2
  And tblFileVersions.FilePathfull Like '%iexplore.exe'
  And (tblFileVersions.FileVersion Like '9.%'
       Or tblFileVersions.FileVersion Like '10.%')

Personally, in an effort to keep the complexity of the report down, I've set up a separate view to link against.

CREATE VIEW vMSIEVersions AS

Select Top 1000000
  tblSoftware.AssetID,
  tblSoftware.SoftwareVersion,
  tblSoftware.InstallDate,
  tblSoftwareUni.SoftwareName,
  tblSoftwareUni.SoftwarePublisher,
  tblSoftwareUni.Approved
FROM
  tblSoftware
  INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
WHERE
  tblSoftwareUni.softwareName Like '%Internet Explorer%'
  And tblSoftwareUni.SoftwarePublisher Like 'Microsoft%'

Union All

Select Top 1000000
  tblFileVersions.AssetID,
  tblFileVersions.FileVersion,
  tblfileVersions.CreationDate AS InstallDate,
  'Windows Internet Explorer ' + Left(tblFileVersions.FileVersion, CharIndex('.', tblFileVersions.FileVersion)-1) AS SoftwareName,
  'Microsoft Corporation' AS Publisher,
  1 AS Approved
FROM
  tblFileVersions
WHERE
  tblFileVersions.FilePathfull Like '%iexplore.exe'
  And ( tblFileVersions.FileVersion Like '9.%'
        Or tblFileVersions.FileVersion Like '10.%' )

With that in place, the main report simplifies to something like this:

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Description,
  tblAssets.Lastseen,
  tsysOS.Image As icon,
  vMSIEVersions.SoftwareName
From
  tblAssets
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join vMSIEVersions On vMSIEVersions.AssetID = tblAssets.AssetID
Where
  tblAssetCustom.State = 1
  And tblComputersystem.Domainrole < 2
Order By
  tblAssets.AssetName