You could try this query. Use a left join on v_Add_Remove_Programs and limit
what you are looking for there. If it doesn't have the one of the .NETs you
are looking for, it will return a null value.
SELECT sys.Name0, sf.FileName, LEFT(sf.FileVersion,2) AS 'IE Version',
arp.DisplayName0, os.Caption0
FROM dbo.v_R_System sys
INNER JOIN dbo.v_GS_SoftwareFile sf ON sys.ResourceID = sf.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM os ON sys.ResourceID = os.ResourceID
LEFT JOIN dbo.v_Add_Remove_Programs arp ON sys.ResourceID =
arp.ResourceID AND arp.DisplayName0 IN ('Microsoft .NET Framework
4.5','Microsoft .NET Framework 4.5.1','Microsoft .NET Framework 4.5.2')
WHERE sf.FileName = 'iexplore.exe' AND sf.FilePath LIKE 'C:\Program
Files\Internet Explorer%'
From: [email protected] [mailto:[email protected]] On
Behalf Of Atkinson, Matt
Sent: Thursday, July 31, 2014 6:17 PM
To: [email protected]
Subject: [mssms] RE: Reporting question
That is good to know, thankfully I don't have any Window 8/8.1 clients at this
time that would throw off my query.
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Garth Jones
Sent: Thursday, July 31, 2014 2:49 PM
To: [email protected]<mailto:[email protected]>
Subject: [mssms] RE: Reporting question
Not all computer will have an Entry for .Net 4.5 for example any Win8 PC
already has it installed.
http://en.wikipedia.org/wiki/.NET_Framework_version_history, however if I
remember right Sherry has .Net MOF edit but I can't seem to find the one that
includes 4.5 version.
http://myitforum.com/myitforumwp/2011/10/04/hardware-inventory-mof-edit-for-net-frameworks-updated-with-v1/
Hopefully she will chime in as to where it is stored.
Garth Jones
Chief Architect
Tel: 613-627-4801 x168
Fax: 613-627-4802
www.Enhansoft.com<http://www.enhansoft.com/>
[Description: Description:
cid:[email protected]]<http://www.enhansoft.com/>
Enhancing Your Business
[Description: Description: Description: Description: Description: Description:
Description:
cid:[email protected]]<http://be.enhansoft.com/>[Description:
Description: Description: Description: Description: Description: Description:
cid:[email protected]]<https://twitter.com/enhansoft>[Description:
Description: Description: Description: Description: Description: Description:
cid:[email protected]]<http://www.facebook.com/EnhansoftInc>[Description:
Description: Description: Description: Description: Description: Description:
Description: Description: Description: Description: Description: Description:
Description: Description: Enhansoft's YouTube
Page]<http://www.youtube.com/user/Enhansoft/videos>[Description: Description:
Description: Description: Description: Description: Description:
cid:[email protected]]<http://myitforum.com/myitforumwp/community/groups/enhansoft/>
[cid:[email protected]]<http://mms.mnscug.org/>
Ask Me About Our Free SSRS
Reports<http://www.enhansoft.com/pages/Free-SSRS-Reports.aspx>
From: [email protected]<mailto:[email protected]>
[mailto:[email protected]] On Behalf Of Atkinson, Matt
Sent: Thursday, July 31, 2014 5:23 PM
To: [email protected]<mailto:[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.
________________________________
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.
**************************************************************************************************
Note:
The information contained in this message may be privileged and confidential
and
protected from disclosure. If the reader of this message is not the intended
recipient, or an employee or agent responsible for delivering this message to
the intended recipient, you are hereby notified that any dissemination,
distribution or copying of this communication is strictly prohibited. If you
have received this communication in error, please notify us immediately by
replying to the message and deleting it from your computer.
**************************************************************************************************
