| LinuxRSA |
05-06-2022 03:31 AM |
Script to check User Expiry from a Database using a Container ID
Hi All
I need to create & write a script that displays users about to expire.The expire amount of days is 90 days.
This is how i get the container ID:
Quote:
linux@server-app-04:~$ sudo docker ps -a | grep -i "postgresql" | awk '{print $1}'
[sudo] password for linux:
24acc5d1ec34
linux@server-app-04~$
|
The idea is to take the container ID 24acc5d1ec34 and then use it in the query to check the database for the user age.
This will in turn provide a list of all users and how old they are in days value, see below.
Quote:
linux@server-app-04:~$ sudo docker exec -i 24acc5d1ec34 psql -h localhost -p 5432 -U dbuser -c "select u.email, now() - to_timestamp(c.created_date/1000)::date password_age from credential c join user_entity u on u.id=c.user_id where now() - to_timestamp(c.created_date/1000)::date > interval '0 days' and c.type='password'" | grep -v "-" | grep -A 5000 -v "email" | grep -v "rows"
support@linuxrsa.ai | 08:18:42.680956
admin@linuxrsa.ai | 113 days 08:18:42.680956
david.goliath@linuxrsa.ai | 77 days 08:18:42.680956
shaun.michaels@linuxrsa.ai | 14 days 08:18:42.680956
|
How do I write one script to perform both tasks
Not sure if this is right
Quote:
#!/bin/bash
CONTAINER_ID=$(docker ps -a | grep -i "postgresql" | awk '{print $1}')
if [ "$CONTAINER_ID" ]
then
sudo docker exec -i $CONTAINER_ID psql -h localhost -p 5432 -U dbsuer -c "select u.email, now() - to_timestamp(c.created_date/1000)::date password_age from credential c join user_entity u on u.id=c.user_id where now() - to_timestamp(c.created_date/1000)::date > interval '0 days' and c.type='password'" | grep -v "-" | grep -A 5000 -v "email" | grep -v "rows"
|
Thanks |
