Yeah, wasn't by default. I had to enable it for this report project that I've been working on. Turned out to be useful for a different request that I had as well.
From: [email protected] [mailto:[email protected]] On Behalf Of Marcum, John Sent: Wednesday, May 06, 2015 1:27 PM To: [email protected] Subject: [mssms] RE: Getting the most recent day that a patch was installed Was that in HINV for you guys? I had to enable it. From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Marcum, John Sent: Wednesday, May 6, 2015 3:25 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Getting the most recent day that a patch was installed Interesting.... v_GS_QUICK_FIX_ENGINEERING is blank for me From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Atkinson, Matt T Sent: Wednesday, May 6, 2015 3:16 PM To: [email protected]<mailto:[email protected]> Subject: [mssms] RE: Getting the most recent day that a patch was installed 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]> [mailto:[email protected]] On Behalf Of Daniel Ratliff Sent: Tuesday, May 05, 2015 1:13 PM To: [email protected]<mailto:[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. ________________________________ Confidentiality Notice: This e-mail is from a law firm and may be protected by the attorney-client or work product privileges. If you have received this message in error, please notify the sender by replying to this e-mail and then delete it from your computer. ________________________________ 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.
