How can I filter and order data extracted by JQ?

Question

I have input data with data on a list of JSON certificates; my goal is to determine which of them have been expired for I'm currently converting this into a list of strings stored in a shell variable and then trying to loop over those strings, but it isn't working correctly:

jsoninput='
[
 {"notafter":"1 May 2024 14:21:51 GMT", "subject":"CN=Valid Certificate B"},
 {"notafter":"2 Jan 2000 00:00:00 GMT", "subject":"CN=Expired Certificate B"},
 {"notafter":"30 Apr 2024 14:21:51 GMT", "subject":"CN=Valid Certificate A"},
 {"notafter":"1 Jan 2000 00:00:00 GMT", "subject":"CN=Expired Certificate A"}
]
'
jsondata=$(jq --raw-output 'keys[] as $i | "Certificate \(.[$i].subject): expiryDate: \(.[$i].notafter | strptime("%d %b %Y %H:%M:%S GMT") | mktime )"' <<<"$jsoninput")

nowDate=$(date +%s --date='30 days ago')

# this part doesn't work right
for i in $myjsondata; do
   if (( $i > $nowDate ));
        then echo "Certs are expired!" $i;
        else echo "Certs are good" $i;
fi
done

When the above is run, echo "$jsondata" looks like:

Certificate CN=Valid Certificate B: expiryDate: 1714576911
Certificate CN=Expired Certificate B: expiryDate: 946771200
Certificate CN=Valid Certificate A: expiryDate: 1714490511
Certificate CN=Expired Certificate A: expiryDate: 946684800

...so each certificate has its own line for the for loop to iterate over.

Obviously, what I want to do is have $i > $nowDate compare only the expireyDate field, but then to be able to print the full string describing the certificate depending on how that comparison goes; but I don't know how to make bash look at only the expireyDate.

With JQ I can parse out only the expiryDate and that works just fine, however the Output I get is Certs are expired! 1542649223 -- there's no listing of which certificate was expired, only of its expiration date.

How can I separate into valid and expired certificates? (As a stretch goal, I'd like to sort the expired certificates to print in output first).

What exactly do you expect the `for` loop to do? What it _actually_ does here is first set `i='Certificate:'`, then `i='CN=mycert,'`, then `i='C=US,'`, and so forth; presumably you want something different, but I have no idea what that is. — Charles Duffy, Oct 28 '22 at 20:11
...well, that's what it would do _if_ you had performed the assignment to `myjsondata` correctly, which you haven't. `A='one' 'two' 'three'` runs the command `two` with the argument `three`, and the transient environment variable assignment `A=one`. — Charles Duffy, Oct 28 '22 at 20:12
BTW, why is it called "myjsondata" when it isn't in JSON format at all? — Charles Duffy, Oct 28 '22 at 20:13
...why not show us your jq code, instead of just asserting that there exists some we aren't being shown? If your real code is `myjsondata=$(jq ...)` instead of `myjsondata="string one" "string two"` then you're showing us code with bugs your _real_ code doesn't actually have. — Charles Duffy, Oct 28 '22 at 20:13
Note too that it's perfectly possible to have jq itself be responsible for filtering the dates, so bash wouldn't need to do it at all. — Charles Duffy, Oct 28 '22 at 20:15
I linked to a question describing how to extract a desired value from a string with bash's built-in regex support, but I don't advise that you actually use that technique. Instead, pass the current date into jq with `--arg` and have jq itself do the filtering. — Charles Duffy, Oct 28 '22 at 20:21
Added two examples of the actual api call and using jq..hopefully this helps — user327344212, Oct 28 '22 at 20:51
Okay. One initial note: `echo $jsoncertdata` breaks your data up and then rejoins it so it's no longer on separate lines. _Always_ use quotes: `echo "$jsoncertdata"` doesn't have that issue. — Charles Duffy, Oct 28 '22 at 20:52
It's hard to build a _tested_ solution without having an example of the JSON emitted by your API (thus, the input this code needs to handle); but as an untested mockup that should give you an idea -- `jq --arg now_epoch "$(date +%s)" '($now_epoch | strptime("%s")) as $now | .[] | (.notafter | strptime("%d %b %Y %H:%M:%S GMT")) as $expire_time | if $expire_time > now then "Expired cert from \(.issuer)" else "Valid cert from \(.issuer)" end'` — Charles Duffy, Oct 28 '22 at 21:00
wow @CharlesDuffy So first thank you on the tip of "" when using echo...I had no idea! second thank you on this jq logic! I'm going to have to digest this a bit. I tested it and it works however it shows all certs as expired. So seems like something is just a bit off....The json format looks like this...and sorry I should have included it initially. I will post json sample after this (not enough characters for json sample) I cannot believe how robust jq is...just amazing... — user327344212, Oct 29 '22 at 00:53
{ "issuer": "CN=Microsoft Azure Federated SSO Certificate", "keysize": 2048, "label": "validation-16142342222949", "notafter": "30 Apr 2024 14:21:51 GMT", "notbefore": "30 Apr 2021 14:21:51 GMT", "serial_number": "522224445542119687161325", "signature_algorithm": "SHA256withRSA", "subject": "CN=Microsoft Azure Federated SSO Certificate", "version": 3 }, — user327344212, Oct 29 '22 at 00:54
Go ahead and [edit] the sample data into the question -- that way you can include enough that people can run it and get the same output shown. — Charles Duffy, Oct 29 '22 at 01:28
...it looks like I just got the comparison backwards -- wrote `if $expire_time > now` when I should have written `if $now > $expire_time` -- but I can't actually _test_ it without a sample that's supposed to be valid in addition to the example that's supposed to be expired — Charles Duffy, Oct 29 '22 at 01:37
Thanks @CharlesDuffy I added a bit of logic to the $now date and reversed corrected the logic. Works great!! TY! JQ is seriously amazing! one line jq removed all the unnecessary code, and made this super portable. — user327344212, Oct 29 '22 at 12:24
one more question you might know...if I wanted to sort by Expired then Valid...is there a way to define those two? if $now > $expire_time then "Expired cert from \(.issuer) on \(.notafter)" else "Valid cert from \(.issuer) until \(.notafter)" end') For example, I could do | sort_by() the expired .notafter date I did try sort_by but get errors because its not json at this point...which is fine, if not no biggie. Trying to make it visually nice — user327344212, Oct 29 '22 at 13:03
Reopened this since with the extra information added in comments it's no longer a string-extraction question; that way the answer can be removed from the question and added with the "Add an Answer" button. Feel free to do that yourself if you like -- I may get to adding my own eventually if there's anything it doesn't cover, but I'm a little unwell right now and don't know when I'll be feeling better and out of bed. — Charles Duffy, Oct 29 '22 at 14:16
BTW, are you sure you want to find certificates that expired at least a month ago, instead of certificates that already expired _or will expire in the coming month_? — Charles Duffy, Oct 29 '22 at 15:23
yeah looking to find expired certs and certs that will be expiring in the next 30 days — user327344212, Oct 31 '22 at 14:49

Charles Duffy · Accepted Answer · 2022-10-29T15:34:03.993

Consider doing the filtering inside of jq itself:

 jq \
  --arg now_epoch "$(date +%s --date='30 days ago')" '
  ($now_epoch | strptime("%s")) as $now
| .[]
| (.notafter | strptime("%d %b %Y %H:%M:%S GMT")) as $expire_time
| if $now > $expire_time
  then "Expired cert for \(.subject)"
  else "Valid cert for \(.subject)"
  end'

Output as

"Valid cert for CN=Valid Certificate B"
"Expired cert for CN=Expired Certificate B"
"Valid cert for CN=Valid Certificate A"
"Expired cert for CN=Expired Certificate A"

To add the sorting, one might change it to:

jq -n   --arg now_epoch "$(date +%s --date='30 days ago')" '
  ($now_epoch | strptime("%s")) as $now
| [ inputs[]
    | (.notafter | strptime("%d %b %Y %H:%M:%S GMT")) as $expire_time
    | [ $expire_time,
         if $now > $expire_time
         then "Expired cert for \(.subject)"
         else "Valid cert for \(.subject)"
         end
       ]
  ]
| sort[]
| .[1]
'

(this is certainly more complicated than it needs to be; if I don't get the time to simplify it myself, I'm sure one of the jq-tag elders will come by and show how to achieve this end more simply).

@Charles_DUffy - I'd skip the not-so-portable use of (GNU) date i.f.o. `(now - 30*24*60*60) as $recent`; and maybe `sort_by(.[0])`. — peak, Oct 29 '22 at 18:27

How can I filter and order data extracted by JQ?

1 Answers1