I don't have it by collection, but I do by machine name.
select netbios_name0 [WKID], qfe.HotFixID0 [ArticleID], QFE.Description0
[Classification], qfe.InstalledBy0 [Installed By], usr.Full_User_Name0
[Friendly Name], qfe.InstalledOn0 [Install Date]
from v_GS_QUICK_FIX_ENGINEERING QFE left join
v_r_system SYS on QFE.resourceid = SYS.resourceid left join
v_r_user USR ON qfe.installedby0 = usr.Unique_User_Name0
where Netbios_Name0 = @MACHINE
order by convert(datetime, InstalledOn0) DESC
Daniel Ratliff
From: [email protected] [mailto:[email protected]] On
Behalf Of Atkinson, Matt T
Sent: Tuesday, May 05, 2015 3:52 PM
To: [email protected]
Subject: [mssms] Getting the most recent day that a patch was installed
I've been asked to add the most recent day that a patch was installed on a
collection of machines. I figured it would be fairly easy to accomplish with
the data in the Quick Fix Engineering table, but the InstalledOn date is
actually in a nvarchar format, so I can't use the max() function to figure it
out. Seems that some of the dates are Windows datetime strings, some of them
are just plain empty.
I played around with converting the data on the fly, and I've gotten close but
not far enough. Here is what I've come up with so far (check to see if the
nvarchar data is a date, convert it to a date time object if true):
SELECT TOP (100) PERCENT dbo.v_R_System.Name0, CASE
(ISDate(dbo.v_GS_QUICK_FIX_ENGINEERING.InstalledOn0)) WHEN '1' THEN
CONVERT(date,
dbo.v_GS_QUICK_FIX_ENGINEERING.InstalledOn0, 101) ELSE
NULL END AS Date
FROM dbo.v_GS_QUICK_FIX_ENGINEERING LEFT OUTER JOIN
dbo.v_R_System ON
dbo.v_GS_QUICK_FIX_ENGINEERING.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
dbo.v_FullCollectionMembership AS fcm ON
fcm.ResourceID = dbo.v_R_System.ResourceID
WHERE (fcm.CollectionID = @COLLID)
GROUP BY dbo.v_R_System.Name0, dbo.v_GS_QUICK_FIX_ENGINEERING.InstalledOn0
ORDER BY dbo.v_R_System.Name0
Problem starts when I try to add the max function, it just errors out. Anyone
else out there done something similar and get it to work?
________________________________
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.
The information transmitted is intended only for the person or entity to which
it is addressed
and may contain CONFIDENTIAL material. If you receive this
material/information in error,
please contact the sender and delete or destroy the material/information.
