Thanks, that got me a little closer, I wound up having to validate that the
value in the InstalledOn field was actually a date, and strangely enough
someone somewhere installed updates using a 3rd party update pack that set the
InstalledOn date to 1.5.2, which SQL evaluated as a valid date, but then
errored out on the conversion to the date style I wanted. Had to put in an
exclusion for that one. Also set the grouping so I only got the most recent
patch installation. This is what I wound up with, seems to get me what I want:
SELECT TOP 100 percent dbo.v_R_System.Name0, max(convert
(datetime,dbo.v_GS_QUICK_FIX_ENGINEERING.InstalledOn0,101)) AS [Last Patch]
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 LEFT
Outer JOIN
dbo.v_FullCollectionMembership AS fcm ON
fcm.ResourceID = dbo.v_R_System.ResourceID
WHERE (fcm.CollectionID = '1IS0000E') AND
(isdate(dbo.v_GS_QUICK_FIX_ENGINEERING.InstalledOn0) = 1) AND
(dbo.v_GS_QUICK_FIX_ENGINEERING.InstalledBy0 <> 'OnePiece Update Pack')
group by dbo.v_R_System.Name0
order by dbo.v_R_System.Name0
From: [email protected] [mailto:[email protected]] On
Behalf Of Daniel Ratliff
Sent: Tuesday, May 05, 2015 1:13 PM
To: [email protected]
Subject: [mssms] RE: Getting the most recent day that a patch was installed
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]>
[mailto:[email protected]] On Behalf Of Atkinson, Matt T
Sent: Tuesday, May 05, 2015 3:52 PM
To: [email protected]<mailto:[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.
________________________________
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.
