That class is not enabled by default. Cesar A. Meaning is NOT in words, but inside people! Dr. Myles Munroe.
> On May 6, 2015, at 3:02 PM, Atkinson, Matt T <[email protected]> > wrote: > > 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]] > On Behalf Of Marcum, John > Sent: Wednesday, May 6, 2015 3:25 PM > To: [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]] > On Behalf Of Atkinson, Matt T > Sent: Wednesday, May 6, 2015 3:16 PM > To: [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]] > 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]] > 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. > > > > 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. >
