Relevance help. Date math

[Samd]

Hello Bigfix enthusiasts,

So I am using the Certificate Probe published by @strawgate long back. It’s producing great results using the periodic actions we have in place. It’s time now to publish it in the SSA and I am stuck on the relevance to perform the required date math.

My goal is to provide a certificate expiry date to the users for the longest valid certificate out of so many certificates provisioned for either user or computer. Thus need some date math in the below relevance to find the longest valid one.

((value “Not After Date” of it) of key whose ((it > 0 * day and it > 30 * day) of (value “Not After Date” of it as string as date & (value “Not After Time” of it as string as time_of_day & local time zone) - now)) of keys((it as string & “\My”) of (keys whose (exists (values “subject” of it) of keys whose (exists value “Subject” whose (it as string starts with “E=” & (name of logged on user as string)& "@example.com")of it) of keys((it as string & “\My”) of(keys of keys “HKEY_LOCAL_MACHINE\SOFTWARE\USER-CERT-INFO\Certificate-Store” of native registry)) of native registry and (exists keys whose (value “Usage” of it as string contains “Client Authentication”) of keys((it as string & “\My”) of(keys of keys “HKEY_LOCAL_MACHINE\SOFTWARE\USER-CERT-INFO\Certificate-Store” of native registry)) of native registry)) of keys “HKEY_LOCAL_MACHINE\SOFTWARE\USER-CERT-INFO\Certificate-Store” of native registry) ) of native registry) as string

Appreciate your help

[JasonWalker]

I’m not using that probe so I’m not sure how the data is stored in the registry. Can you provide some sample data?

To compare the dates, though, you’ll need to find a way to cast the registry value to a date or time type. You’ll also need to subtract either “current date” or “now” from it to get a time interval if you want to compare a number of days. It would be something like

Values "Not after date" whose (it as string as time - now > 30 * day) of keys ....

[strawgate]

Getting the biggest/smallest/longest/shortest is one of the most annoying things to do in Relevance. Something like this should get you started:

value "Subject" of item 0 of it whose ((value "Not After Date" of it as string as date & (value "Not After Time" of it as string as time_of_day & local time zone)) of item 0 of it = item 1 of it) of ((keys of keys "HKEY_LOCAL_MACHINE\Software\C3 Inventory\Certificate Store\LocalMachine\Root" of native registry), it) of (maximum of (value "Not After Date" of it as string as date & (value "Not After Time" of it as string as time_of_day & local time zone)) of keys of keys "HKEY_LOCAL_MACHINE\Software\C3 Inventory\Certificate Store\LocalMachine\Root" of native registry)

This will pull the root cert with the longest expiration date, you should be able to change this to cover other cases. Just swap LocalMachine\Root with the type of cert you’re checking and if you want to do filtering on the certs to consider for the comparison then you’d add that towards the end like this:

 keys whose (value "subject" of it as string starts with "CN=Bill") of keys "HKEY_LOCAL_MACHINE\Software\C3 Inventory\Certificate Store\LocalMachine\Root" of native registry