Extending Hardware Inventory for System Center Configuration Manager

Copper Contributor

Nov 10, 2020

Well, it was all going great - until I found that the powershell script writes the NotBefore, NotAfter and ScriptLastRan0 values into WMI as CimType 'String' rather than 'DateTime'. The result from our environment was a variety of US/UK date formats and 12hr/24hr times stored as text.

[BrandonWilson- no slight intended, this was still a great post and has already saved my bacon; just needed a little refinement for our use.]

Anyway, I'm working on an update to the powershell to rewrite the dates as CimType 'DateTime'; in the meantime the dates can be 'standardised' in the report SQL instead, for example:

SELECT
  v_GS_CM_CERTINFO.ExpiresinDays0
  ,v_R_System.Netbios_Name0
  ,v_R_System.Full_Domain_Name0
  ,v_GS_CM_CERTINFO.Issuer0
  ,v_GS_CM_CERTINFO.Subject0
  ,v_GS_CM_CERTINFO.FriendlyName0
  , case  right(v_GS_CM_CERTINFO.NotAfter0, 2) 
        when 'AM' then CONVERT(nvarchar, CONVERT(DATETIME, v_GS_CM_CERTINFO.NotAfter0, 101), 120)
        when 'PM' then CONVERT(nvarchar, CONVERT(DATETIME, v_GS_CM_CERTINFO.NotAfter0, 101), 120)
    else (substring(v_GS_CM_CERTINFO.NotAfter0, 7,4) + '-' + substring(v_GS_CM_CERTINFO.NotAfter0, 4,2) + '-' + left(v_GS_CM_CERTINFO.NotAfter0, 2)) + ' ' + right(v_GS_CM_CERTINFO.NotAfter0, 8) end as NotAfter0
  , case  right(v_GS_CM_CERTINFO.NotBefore0, 2)   
        when 'AM' then CONVERT(nvarchar, CONVERT(DATETIME, v_GS_CM_CERTINFO.NotBefore0, 101), 120)
        when 'PM' then CONVERT(nvarchar, CONVERT(DATETIME, v_GS_CM_CERTINFO.NotBefore0, 101), 120)
    else (substring(v_GS_CM_CERTINFO.NotBefore0, 7,4) + '-' + substring(v_GS_CM_CERTINFO.NotBefore0, 4,2) + '-' + left(v_GS_CM_CERTINFO.NotBefore0, 2)) + ' ' + right(v_GS_CM_CERTINFO.NotBefore0, 8) end as NotBefore0
  , case  right(v_GS_CM_CERTINFO.ScriptLastRan0, 2) 
        when 'AM' then CONVERT(nvarchar, CONVERT(DATETIME, v_GS_CM_CERTINFO.ScriptLastRan0, 101), 120)
        when 'PM' then CONVERT(nvarchar, CONVERT(DATETIME, v_GS_CM_CERTINFO.ScriptLastRan0, 101), 120)
    else (substring(v_GS_CM_CERTINFO.ScriptLastRan0, 7,4) + '-' + substring(v_GS_CM_CERTINFO.ScriptLastRan0, 4,2) + '-' + left(v_GS_CM_CERTINFO.ScriptLastRan0, 2)) + ' ' + right(v_GS_CM_CERTINFO.ScriptLastRan0, 8) end as ScriptLastRan0

FROM
  v_GS_CM_CERTINFO
  INNER JOIN v_R_System ON v_GS_CM_CERTINFO.ResourceID = v_R_System.ResourceID

This will output the dates as YYYY-MM-DD hh:mm:ss , for example 2020-02-20 12:34:56 (which is Excel-friendly, so will format nicely after exporting)

Hope this helps!

Blog Post

Extending Hardware Inventory for System Center Configuration Manager