none
QUERY for last patch date RRS feed

  • Question

  • I have been going bonkers trying to figure this out and figured Id post here as a last resort.  I am trying to find out the last time a PC was patched and create a QUERY around it.  I am only looking to display a compute name and date.  I saw the QUICK FIX ENGINEERING attribute, but I cannot figure out how to display the most recent result only.  When I run this query, it will list every update.  I only need the most recent date.

    Another issue I have in running a query is QUICK FIX ENGINEERING -> INSTALLED ON attribute.  When I try to choose a value for the date/time, it gives a sixteen alphanumeric date/time (ex: 01d271fb9e8de495).  Would anyone know how I can convert this to see what date/time it actually represents?

    Any information is appreciated!
    Wednesday, January 22, 2020 4:25 PM

All replies

  • So what AV are you using as this will affect the results?

    Also understand that NOT all SU get listed within QFE. 

    Are you trying to create a report or an SCCM Collection query? 


    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleashed

    Wednesday, January 22, 2020 5:09 PM
    Moderator
  • If you are using an in-console query, this is not possible.

    For SQL, the MAX function will get you  the maximum value of a column: https://docs.microsoft.com/en-us/sql/t-sql/functions/max-transact-sql?view=sql-server-ver15

    As for the date/time, it's stored as a WMI datetime. What does "when I try to choose a value for date/time" mean then in technical terms? DOes that mean when you add it to your SQL SELECT statement?


    Jason | https://home.configmgrftw.com | @jasonsandys

    Wednesday, January 22, 2020 5:25 PM
  • Cisco Amp and Windows Defender

    I am not doing this an a SQL Collection, but just as a QUERY (MONITORING -> QUERIES).  Trying to create an SCCM query.
    • Edited by Steve Zeke Wednesday, January 22, 2020 5:59 PM
    Wednesday, January 22, 2020 5:56 PM
  • Thanks for the reply.  I have found some SQL queries to run, but trying to do this as an SCCM query, so the language doesnt take if I copy/paste it in. 

    I am doing the query within SCCM (Im far from a SQL guru).  When I choose the QUICK FIX ENGINEERING -> INSTALLED ON attribute within SCCM, it gives a number (example: 01d271fb9e8de495) instead of an actual date/time.  Just trying to see how to convert it.  
    Wednesday, January 22, 2020 5:58 PM
  • I have found some SQL queries to run, but trying to do this as an SCCM query, so the language doesnt take if I copy/paste it in. 

    As noted, it's not possible with an in-console query which uses WQL which is a very limited subset of SQL.

    As for the Install On attribute, there's no way to convert this using an in-console query.


    Jason | https://home.configmgrftw.com | @jasonsandys

    Wednesday, January 22, 2020 6:11 PM
  • Is there any way to convert it just so I can see what the actual date is?  
    Wednesday, January 22, 2020 6:13 PM
  • Not with an in-console query.

    In SQL, the format should be straight-forward.

    Heed Garth's warning as well that every update always registers in Win32_QuickFixEngineering.


    Jason | https://home.configmgrftw.com | @jasonsandys

    Wednesday, January 22, 2020 6:43 PM
  • Not with an in-console query.

    In SQL, the format should be straight-forward.

    Heed Garth's warning as well that every update always registers in Win32_QuickFixEngineering.


    Jason | https://home.configmgrftw.com | @jasonsandys

    So how, for example, do I find out what date/time 01d271fb9e8de495 is? 
    Wednesday, January 22, 2020 6:46 PM
  • Not with an in-console query.

    In SQL, the format should be straight-forward.

    Heed Garth's warning as well that every update always registers in Win32_QuickFixEngineering.


    Jason | https://home.configmgrftw.com | @jasonsandys

    So how, for example, do I find out what date/time 01d271fb9e8de495 is? 
    I just looked and I'm not seeing that for the data, instead I'm see a real date. So, I would look at the under laying SQL data. 

    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleashed


    Wednesday, January 22, 2020 7:06 PM
    Moderator
  • Exactly, The value you are seeing is the ConfigMgr's console poor attempt at converting the underlying value which is a DMTF datetime value. If you view the value directly in SQL, you should see the raw value. There are fairly simple ways to convert this value using .NET (and thus PowerShell) but nothing in the console itself as the console simply doesn't allow this.

    Jason | https://home.configmgrftw.com | @jasonsandys

    Wednesday, January 22, 2020 7:11 PM
  • Exactly, The value you are seeing is the ConfigMgr's console poor attempt at converting the underlying value which is a DMTF datetime value. If you view the value directly in SQL, you should see the raw value. There are fairly simple ways to convert this value using .NET (and thus PowerShell) but nothing in the console itself as the console simply doesn't allow this.

    Jason | https://home.configmgrftw.com | @jasonsandys

    Is there anything online that I can put in value and get the conversion?  I havent found one yet.  I see a bunch of alphanumeric values and just want to see what the date/time is.....
    • Edited by Steve Zeke Wednesday, January 22, 2020 7:28 PM
    Wednesday, January 22, 2020 7:28 PM
  • Exactly, The value you are seeing is the ConfigMgr's console poor attempt at converting the underlying value which is a DMTF datetime value. If you view the value directly in SQL, you should see the raw value. There are fairly simple ways to convert this value using .NET (and thus PowerShell) but nothing in the console itself as the console simply doesn't allow this.


    Jason | https://home.configmgrftw.com | @jasonsandys

    Is there anything online that I can put in value and get the conversion?  I havent found one yet.  I see a bunch of alphanumeric values and just want to see what the date/time is.....

    Exactly what does your query look like? I'm see a real date listed in my console. 


    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleashed

    Wednesday, January 22, 2020 7:33 PM
    Moderator
  • Sorry for the late response, but I am unable to post images until my acct is verified and was hoping they'd verify me today so I could post an image.  So here is my query info:

    CRITERION TYPE: Simple value
    WHERE: Quick Fix Engineering - Installed on
    OPERATOR: is equal to
    VALUE: (when I click value, this is where I see nothing but alphanumeric values, no actual dates)

    I might be able to work with this query if I can find a way to translate the alphanumeric values to a date/time that I can understand.

    • Edited by Steve Zeke Thursday, January 23, 2020 6:35 PM
    Thursday, January 23, 2020 6:32 PM
  • I'm not sure if you did this or not but here is how to get verified. 

    https://www.enhansoft.com/problems-posting-pictures-and-images-or-urls-to-the-technet-forums/

    I did those steps and I'm seeing ready dates. So Have you looked at the under laying data in SQL? What Date format are you using aka YYYY-MM-DD,  MM-DD-YY, etc.


    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleashed

    Thursday, January 23, 2020 8:28 PM
    Moderator
  • I havent, but good to know.  Thanks.  Mine doesnt display dates....
    Friday, January 24, 2020 1:31 PM
  • I havent, but good to know.  Thanks.  Mine doesnt display dates....

    Have you looked at the underlying data in the db? Is it listed as date or not? 

    What date format are you using?


    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleashed

    Friday, January 24, 2020 2:25 PM
    Moderator
  • I know very little of SQL and the SCCM server is not mine to handle, Im just trying to run reports on it.  The values that the SCCM console gives me when I run a query are alphanumeric, not the date/time that you saw on his.  
    • Edited by Steve Zeke Friday, January 24, 2020 5:11 PM
    Friday, January 24, 2020 4:42 PM