Home > Enterprise >  How can I filter and order data extracted by JQ?
How can I filter and order data extracted by JQ?

Time:10-31

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).

CodePudding user response:

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).

  • Related