Examples of custom reports

List of records archived

List of records archived more than 30 days ago with the date of their archival.

This report could be used on the folder level because of its reference to record_id. This report also supports the search facility because of the where clause for :search placeholder.

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select
    r.id as id,
    r.name as name,
    r.recordType.name as recordType,
    r.archived as archived,
    r.updateTime as updateTime,
    r.id as record_id
from
    Record r
where
    r.archived is 'true'
    and (r.updateTime is null or 
         (year(current_timestamp)-2017)*365+(month(current_timestamp)-1)*12+day(current_timestamp) 
             - ( (year(r.updateTime)-2017)*365+(month(r.updateTime)-1)*12+day(r.updateTime) ) > 30
    )
and r.name like :search

List all Windows Hosts

List all Windows Hosts with the last job executed more than 60 days ago.

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select
    r.name as name,
    r.index1 as hostIP,
    r.recordType.name as recordType,
    m.lastRotate as Rotated
from
    PasswordManager m join m.record r
where
    r.recordType.name like 'Windows Host%'
    and (m.lastRotate is null or 
         (year(current_timestamp)-2017)*365+(month(current_timestamp)-1)*12+day(current_timestamp) 
             - ( (year(m.lastRotate)-2017)*365+(month(m.lastRotate)-1)*12+day(m.lastRotate) ) > 60
    )
    and r.name like :search
order by
   m.lastRotate desc

List Unix hosts

List Unix hosts with last password rotation job execute more than 60 days ago.

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
select
    max(r.name) as name,
    max(r.id) as id,
    max(r.index1) as hostIP,
    max(r.indexa) as User,
    max(q.task.script.name) as task,
    max(q.last) as time
from
         PasswordResetQueue q
    left outer join q.record r
where
    r.recordType.name like 'Unix Host%'
    and q.task.script.name like 'Password Reset Remote%'
    and q.state = 6
    and r.indexa <> '$login'
    and r.name like :search
    and (q.last is null or
     (year(current_timestamp)-2017)*365+(month(current_timestamp)-1)*12+day(current_timestamp) 
         - ( (year(q.last)-2017)*365+(month(q.last)-1)*12+day(q.last) ) > 60
     )
group by
   r.id

Record count by record type

Copy

1
2
3
4
5
6
select
    r.recordType.name as name,
    count(*) as count
from
    Record r
group by r.recordType.name

Query to select record types

Query to select record types not used anywhere.

Copy

1
2
3
4
5
6
select
    t.name as name
from
    RecordType t
where
    t not in (select recordType from Record)

Query records

Query records with failed or not verified credentials.

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
select
    m.record as record,
    m.lastReset as lastReset,
    m.lastAttemptAt as lastAttemptAt,
    m.lastConnect as lastConnect,
    m.lastRotate as lastRotate,
    m.record.id as record_id
from
    PasswordManager m
where
    (m.lastRotate is null or m.lastAttemptAt > m.lastRotate)
and m.record.recordType.name like 'Windows%'
and m.lastAttemptAt is not null

Selects records

Selects records and their session managers if any.

Copy

1
2
3
4
5
6
7
8
9
10
11
select
    r.id as id,
    r.name as name,
    r.recordType.name as recordType,
    r.author.name as author,
    s.name as sm,
    r.created as created
from
    Record r left outer join r.recordType t left outer join t.sessionManager s
order by
    r.name

All records with last task failed (error)

The query returns all records that have the last task failed (error) indicating both record and the task that failed.

Copy

1
2
3
4
5
6
7
8
9
10
11
12
select
    m.scheduled as scheduled,
    m.record.name as Record,
    m.host as host,
    m.task.script.name as Task,
    m.type as type,
    m.record.id as record_id
from
    PasswordResetQueue m
where
    m.id in (select max(q.id) from PasswordResetQueue q where q.record=m.record)
and m.state=5

All records with task failed or scheduled jobs

The query is small refinement over the previous one that returns all records that have the last task failed (error) but could also have some scheduled jobs in the future (not completed of failed yet).

Copy

1
2
3
4
5
6
7
8
9
10
11
12
select
    m.scheduled as scheduled,
    m.record.name as Record,
    m.host as host,
    m.task.script.name as Task,
    m.type as type,
    m.record.id as record_id
from
    PasswordResetQueue m
where
    m.id in (select max(q.id) from PasswordResetQueue q where q.record=m.record and q.state>4)
and m.state=5

All jobs

The query returns all jobs that happened before or scheduled after the last failed job sorted by records and tasks.

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
    m.scheduled as scheduled,
    m.record.name as Record,
    m.host as host,
    m.task.script.name as Task,
    m.type as type,
    m.record.id as record_id
from
    PasswordResetQueue m
where
    m.record.id in (select p.record.id from PasswordResetQueue p where
        p.id in (select max(q.id) from PasswordResetQueue q where q.record=p.record and q.state>4)
        and p.state=5
)
order by
    m.record.id,
    m.task.id