Custom Internet Explorer version

[servicedesk]

Hi team,

I need a variation of the default report "Files: Internet explorer version", tried to do by myself but nothing good happened 😞

The information I need:

Assetname, OS,OSbitness, last user logged on, if IE is version 8.

Thats the default SQL:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblFileVersions.Found,
tblFileVersions.FilePathfull,
tblFileVersions.FileVersion,
tblFileVersions.CompanyName,
tblFileVersions.Filesize,
tblFileVersions.Lastchanged
From tblAssets
Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
Where tblFileVersions.FilePathfull Like '%iexplore.exe' And
tblAssets.Assettype = -1
Order By tblAssets.AssetName

[servicedesk]

Very nice!

thank you

[Daniel_B · Accepted solution]

Likely there are several versions of iexplore.exe on your computers (one 32-bit and one 64-bit). If you would like to list each computer only once, you'll need to use a subquery which searches for assets with iexplore.exe version 8.X, like the example below:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  Case
    When tblComputersystem.SystemType Like 'x64-based PC' Then
    tblOperatingsystem.Caption + ' (x64) SP' + Cast(tblAssets.SP As nvarchar)
    Else tblOperatingsystem.Caption + ' SP' + Cast(tblAssets.SP As nvarchar)
  End As FullOSname,
  tblComputersystem.SystemType,
  tblAssets.Username As [Last user logged on]
From tblAssets
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
Where  

  tblAssets.AssetID In (Select tblFileVersions.AssetID From tblFileVersions
  Where tblFileVersions.FilePathfull Like '%iexplore.exe' And
    tblFileVersions.FileVersion Like '8.%') 
  
  And tblAssets.Assettype = -1
  And tblOperatingsystem.Caption Like '%Windows 7 Enterprise%'
  And tblAssets.IPAddress Like '%10.162.%'
Order By tblAssets.AssetName

[servicedesk]

Hello,

Thanks is a very good report but I would like something more specific because a want to be able run deployments based on that report.

I updated a bit the code and now is showing:

specific OS "Microsoft Windows 7 Enterprise"
specific IE version "8"
specific IP range

But I don't know why is duplicating machines, can you check why ?

Thanks

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
Case
When tblComputersystem.SystemType Like 'x64-based PC' Then
tblOperatingsystem.Caption + ' (x64) SP' + Cast(tblAssets.SP As nvarchar)
Else tblOperatingsystem.Caption + ' SP' + Cast(tblAssets.SP As nvarchar)
End As FullOSname,
tblAssets.Username As [Last user logged on],
tblFileVersions.Found [IE installed],
Case
When CharIndex('.', tblFileVersions.FileVersion) >
0 Then SubString(tblFileVersions.FileVersion, 1, CharIndex('.',
tblFileVersions.FileVersion) - 1) Else 'unknown' End As [IE Version],
tblFileVersions.Lastchanged,
tblAssets.IPAddress
From tblAssets
Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where Case
When tblComputersystem.SystemType Like 'x64-based PC' Then
tblOperatingsystem.Caption + ' (x64) SP' + Cast(tblAssets.SP As nvarchar)
Else tblOperatingsystem.Caption + ' SP' + Cast(tblAssets.SP As nvarchar)
End Like 'Microsoft Windows 7 Enterprise%' And Case
When CharIndex('.', tblFileVersions.FileVersion) >
0 Then SubString(tblFileVersions.FileVersion, 1, CharIndex('.',
tblFileVersions.FileVersion) - 1) Else 'unknown'
End Like 8 And tblAssets.IPAddress Like '%10.162.%' And
tblFileVersions.FilePathfull Like '%iexplore.exe' And tblAssets.Assettype = -1
Order By tblAssets.AssetName

[Daniel_B]

Could you give the following report a try:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  Case
    When tblComputersystem.SystemType Like 'x64-based PC' Then
    tblOperatingsystem.Caption + ' (x64) SP' + Cast(tblAssets.SP As nvarchar)
    Else tblOperatingsystem.Caption + ' SP' + Cast(tblAssets.SP As nvarchar)
  End As FullOSname,
  tblAssets.Username As [Last user logged on],
  tblFileVersions.Found [IE installed],
  Case
    When CharIndex('.', tblFileVersions.FileVersion) >
    0 Then SubString(tblFileVersions.FileVersion, 1, CharIndex('.',
    tblFileVersions.FileVersion) - 1) Else 'unknown' End As [IE Version],
  tblFileVersions.Lastchanged
From tblAssets
  Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblFileVersions.FilePathfull Like '%iexplore.exe' And
  tblAssets.Assettype = -1
Order By tblAssets.AssetName