Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
apiVersion: batch/v1beta1
kind: CronJob
metadata:
labels:
app: nextcloud
component: count-active-users
name: ${FQDN}-count
namespace: ${NS}
spec:
concurrencyPolicy: Forbid
failedJobsHistoryLimit: 1
schedule: '15 15 * * *'
jobTemplate:
spec:
template:
metadata:
labels:
app: nextcloud
component: count-active-users
spec:
restartPolicy: Never
containers:
- name: psql
image: postgres:12-alpine
command:
- sh
- -c
- >
echo {\"30days\":`psql -t -c "select count(*) from preferences where configkey = 'lastLogin' and to_timestamp("configvalue"::integer) > ('now'::timestamp - '1 month'::interval);" | xargs`,\"90days\":`psql -t -c "select count(*) from preferences where configkey = 'lastLogin' and to_timestamp("configvalue"::integer) > ('now'::timestamp - '3 month'::interval);" | xargs`}
env:
- name: PGHOST
value: pg-${NS}
- name: PGDATABASE
value: nextcloud
- name: PGUSER
value: postgres
- name: PGPASSWORD
valueFrom:
secretKeyRef:
key: password
name: postgres.pg-${PG_DOMAIN}.credentials
#psql -h pg-desami-es -U postgres nextcloud -t -c "select count(*) from preferences where configkey = 'lastLogin' and to_timestamp("configvalue"::integer) > ('now'::timestamp - '1 month'::interval);"
#psql -t -c "select count(*) from preferences where configkey = 'lastLogin' and to_timestamp("configvalue"::integer) > ('now'::timestamp - '1 month'::interval);"