Here is what I came up with:
Select sys.Host
, sf.[IE Version]
, IsNull(arp.DotNet, ' ') As '.Net'
, IsNull(arp.Version, ' ') As '.Net Version'
, os.Caption0
, os.CSDVersion0
>From (
Select ResourceID
, Name0 As Host
From dbo.v_R_System
Where Client0 = 1
And Active0 = 1
And Obsolete0 = 0
) As sys
Join (
Select Distinct
ResourceID
, FileName
, Left(FileVersion,CharIndex('.',FileVersion)) As 'IE Version'
, FileVersion
From dbo.v_GS_SoftwareFile
Where FileName = 'iexplore.exe'
And FilePath Like 'C:\Program Files\Internet Explorer%'
) As sf
On sys.ResourceID = sf.ResourceID
Left Join
v_GS_Operating_System As os
On sys.ResourceID = os.ResourceID
Left Join
(
Select ResourceID
, DisplayName0 As 'DotNet'
, Version0 As 'Version'
From dbo.v_Add_Remove_Programs
Where DisplayName0 In
(
'Microsoft .NET Framework 4.5',
'Microsoft .NET Framework 4.5.1',
'Microsoft .NET Framework 4.5.2'
)
) As arp
On sys.ResourceID = arp.ResourceID
Order By
Host
...Rob Carlson
This email is UNCLASSIFIED.
From: [email protected] [mailto:[email protected]] On
Behalf Of Atkinson, Matt
Sent: Thursday, July 31, 2014 5:23 PM
To: [email protected]
Subject: [mssms] Reporting question
I've been asked to create a report listing the OS, IE version and whether or
not .net 4.5 is installed on a huge list of machines in our environments.
I've so far been fumbling through creating the report and have come up with
this SQL query:
SELECT Distinct dbo.v_R_System.Name0, dbo.v_GS_SoftwareFile.FileName,
LEFT(dbo.v_GS_SoftwareFile.FileVersion, 2) AS 'IE Version',
dbo.v_Add_Remove_Programs.DisplayName0,
dbo.v_GS_OPERATING_SYSTEM.Caption0
FROM dbo.v_R_System INNER JOIN
dbo.v_GS_SoftwareFile ON dbo.v_R_System.ResourceID =
dbo.v_GS_SoftwareFile.ResourceID INNER JOIN
dbo.v_Add_Remove_Programs ON
dbo.v_GS_SoftwareFile.ResourceID = dbo.v_Add_Remove_Programs.ResourceID INNER
JOIN
dbo.v_GS_OPERATING_SYSTEM ON
dbo.v_Add_Remove_Programs.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID
WHERE (dbo.v_GS_SoftwareFile.FileName = 'iexplore.exe') AND
(dbo.v_GS_SoftwareFile.FilePath LIKE '%C:\Program Files\Internet Explorer%') AND
(dbo.v_Add_Remove_Programs.DisplayName0 LIKE
'Microsoft .NET Framework 4.5' OR
dbo.v_Add_Remove_Programs.DisplayName0 LIKE 'Microsoft
.NET Framework 4.5.1' OR
dbo.v_Add_Remove_Programs.DisplayName0 LIKE 'Microsoft
.NET Framework 4.5.2')
The problem that I'm running in to now is that when I run the report I only get
machines that have .net 4.5 installed. Is there a way to get the rest of the
machines to either show up with a blank for the .net version column, or with
just their newest installed version? I tried replacing all of the .net
framework filters with just a single one saying LIKE 'Microsoft .NET
Framework%', but then I get computers repeating in the report for every version
of .net that they have installed.
I could probably figure out the sub-select syntax to get all of the machines
not in the report as it is now, but would they have to go on 2 different
reports? I'd like to have it all in one if possible. I'll also need to get this
to prompt for a collection name when ran, I'll put all the machines that I need
to check in a single collection.
-Matt
________________________________
This message is intended for the sole use of the addressee, and may contain
information that is privileged, confidential and exempt from disclosure under
applicable law. If you are not the addressee you are hereby notified that you
may not use, copy, disclose, or distribute to anyone the message or any
information contained in the message. If you have received this message in
error, please immediately advise the sender by reply email and delete this
message.
