Report name: sql-universal-report

This report will report an entire database table/view or data based on a custom SQL query.

Only a user with superadmin permission can execute this report

You have three options for running this report:

• Table/view name - reports all data from a table/view as it is in the database
• SQL query - reports data based on a custom SQL query
• Code list - reports data based on a custom SQL query prepared and stored in code list

You need to create new code list with any code eg.: SQL-queries. In code list create new attribute with name code, type Text and facetype TextArea.

Create new item in code list. Code of item is identifier you fill in report, in attribute Code is prepared SQL for report.

Example of report configuration with code list

You need to configure an additional application.properties file. This report is using a separate datasource for safety reasons.

For example read only user for whole IdM database:

Turn off IdM and then execute:
CREATE USER czechidm_report PASSWORD 'keepass';
GRANT CONNECT ON DATABASE czechidm TO czechidm_report;
\c czechidm;
REVOKE ALL ON SCHEMA public FROM public;
GRANT USAGE ON SCHEMA public TO public;
GRANT ALL on SCHEMA public to czechidm;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO czechidm_report;

The properties you need to configure are specified below.

# name of the database, update this
spring.reports-datasource.jdbcUrl=jdbc:postgresql://localhost:5432/hr
# name of the user, update this
spring.reports-datasource.username=czechidm_report
# password for the user, update this
spring.reports-datasource.password=keepass
spring.reports-datasource.driver-class-name=org.postgresql.Driver
spring.reports-datasource.testOnBorrow=true
spring.reports-datasource.validationQuery=SELECT 1
spring.reports-datasource.maximum-pool-size=5
spring.reports-datasource.read-only=true

If you have IdM installed as IAM appliance, you have to follow the steps below in order to install and use the Reports module containing this report. Note that required version of CzechIdM container is at least 12.2.

Turn off IdM (this is neccessary because we will change DB permissions in the next steps):

systemctl stop iam-czechidm

Add the reports module to additional modules for your IdM installation:

cp idm-reports-3.0.0.jar /data/volumes/czechidm/modules/

Go to the internal database (you have to go inside the docker container and run psql inside) and create a separate user for running the SQL Universal report. For this report, always create a new user with only select permissions for the tables and views you need. Never use the same user you use for the standard IdM database. For example, a read only user for whole IdM database can be created as follows:

docker exec -it czechidm-db /bin/bash
su postgres
psql
CREATE USER czechidm_report PASSWORD '***some secure password***';
GRANT CONNECT ON DATABASE czechidm TO czechidm_report;
\c czechidm;
REVOKE ALL ON SCHEMA public FROM public;
GRANT USAGE ON SCHEMA public TO public;
GRANT ALL on SCHEMA public to czechidm;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO czechidm_report;
\q
exit
exit

Back on the server (outside of the docker container), save the password of the user you just created to a separate file:

echo "***some secure password***"> /data/volumes/czechidm/secrets/idm_db_report.pwfile

Finally, update the definition of the iam-czechidm service in the file /data/registry/node-active-config/docker-compose-czechidm.yml:

# uncomment (i.e. remove hash from) the following lines in the "environment" section:
      - CZECHIDM_REPORTS_DB_URL=jdbc:postgresql://czechidm-db:5432/czechidm
      - CZECHIDM_REPORTS_DB_USER=czechidm_report
      - CZECHIDM_REPORTS_DB_PASSFILE=/run/secrets/db_report.pwfile
      - CZECHIDM_REPORTS_DB_DRIVERCLASS=org.postgresql.Driver

# add following new lines in the "volumes" section:

      - type: bind
        source: /data/volumes/czechidm/secrets/idm_db_report.pwfile
        target: /run/secrets/db_report.pwfile
        read_only: true

Start the IdM service:

systemctl start iam-czechidm

When IdM starts up (it will take several minutes), login as admin and go to the Settings → Modules. Find the line with the Reports module and click on the Activate button.

A simple list of collected SQL queries over the new IdM database.

: The following chapters are in Czech for now, translation is in progress.

Hledáme objekt (zde událost) dle UUID, které se v IdM zobrazuje 02fdad6a-1113-4102-bc7d-90fe64c2ed16.

PostgreSQL:

SELECT * FROM idm_entity_event WHERE ID = uuid_send('02fdad6a-1113-4102-bc7d-90fe64c2ed16'::uuid);

MS SQL Server. Do příkazu se dá UUID upravené - odstraní se pomlčky, přidá se na začátek "0x":

SELECT * FROM bcv_idm_storage.idm_entity_event WHERE ID = CONVERT(VARBINARY(MAX), '0x02fdad6a11134102bc7d90fe64c2ed16', 1)

Zobrazení ID ve formátu, který je vidět v GUI:

SELECT CAST(ENCODE(id, 'hex') AS UUID) FROM idm_entity_event;

Vytáhnutí seznamu identifikátorů pro daný běh synchronizace.

-- system_mapping_id vložím UUID mapovaní pro synchronizaci co mě zajímá- Zobrazení creatora je zde kvůli tomu, že jsem idetifikoval konkrétní běh podle toho kdo to pustil, nahradtě creator jiným sloupcem případně přidejte do where další podmínku tak aby jste identifikovali konkrétní běh synchronizace
SELECT encode(transaction_id, 'hex')::uuid, creator FROM sys_sync_log WHERE synchronization_config_id = (SELECT sys_sync_config.id FROM sys_sync_config WHERE system_mapping_id = uuid_send('afb0af24-964f-406b-a5cf-e1fccf6c0911'::uuid))
 
-- Transaction ID z dotazu váše použiju zde. tím dostanu položky pro ten jeden běh synchronizace. Vytažení položek ve stavu UNLINKED nebo UNKNOWN je přes like dotaz, možná bude existovat jiná/lepší cesta. Toto vrátí tedy všechny identifikátopry položek které jsou ve stavu unlinked nebo unknown
SELECT identification FROM sys_sync_item_log WHERE transaction_id = uuid_send('f47cfe6a-aa03-4a9c-9271-057bc1e173de'::uuid) AND (message LIKE '%UNLINKED%' OR message LIKE '%UNKNOWN%');

Report slouží pro přehled všech účtů na napojených systémech, které nejsou spravovány v IdM.

Report vypisuje všechny záznamy, které nejsou starší než týden. V IdM často některé synchronizace probíhají pravidelně, proto jsem do exportu přidal sloupec s názvem synchronizace podle, kterého můžete v SQL dále filtrovat. Například vypsat pouze synchronizace, které začínají na "Kontrolní synchronizace" atd.

SELECT  ssil.display_name,ssil.identification, ssal.sync_action,  CAST(SUBSTRING(CAST(ss.id AS text) FROM 3) AS uuid) AS system_id, ss."name" AS system_name,ssal.created AS DATE,ssc."name" AS synchronization_name FROM sys_sync_item_log ssil
LEFT JOIN sys_sync_log sync ON ssil.transaction_id = sync.transaction_id
LEFT JOIN sys_sync_action_log ssal ON ssil.sync_action_log_id  = ssal.id
LEFT JOIN sys_sync_config ssc  ON sync.synchronization_config_id  = ssc.id
LEFT JOIN sys_system_mapping ssm ON  ssc.system_mapping_id  = ssm.id
LEFT JOIN sys_schema_obj_class ssoc ON  ssm.object_class_id  = ssoc.id
LEFT JOIN sys_system ss  ON  ssoc.system_id  = ss.id
WHERE ssal.sync_action = 'MISSING_ENTITY' AND
EXTRACT(WEEK FROM ssal.created) = EXTRACT(WEEK FROM CURRENT_DATE)
AND EXTRACT(YEAR FROM ssal.created) = EXTRACT(YEAR FROM CURRENT_DATE)
ORDER BY ssil.display_name;

Získání EAV atributů:

-- identity s EAV atributem vpnExpiration a sshExpiration typu Date
SELECT ii.username, ii.disabled, ifa.name, iifv.date_value FROM idm_identity_form_value iifv  JOIN idm_identity ii ON iifv.owner_id=ii.id JOIN idm_form_attribute ifa ON iifv.attribute_id = ifa.id AND ifa.name IN  ('vpnExpiration','sshExpiration') ORDER BY iifv.date_value
 
-- identity s EAV atributem mailType typu ShortText a hodnotou Exchange
SELECT ii.username, ii.disabled, ifa.name, iifv.short_text_value FROM idm_identity_form_value iifv  JOIN idm_identity ii ON iifv.owner_id=ii.id JOIN idm_form_attribute ifa ON iifv.attribute_id = ifa.id AND ifa.name IN  ('mailType') WHERE short_text_value='Exchange';
 
-- zmeny EAV atributu pro kontrakty konkretni identity
SELECT created, short_text_value FROM idm_i_contract_form_value_a WHERE owner_id IN (SELECT iic.id FROM idm_identity_contract iic JOIN idm_identity ii ON iic.identity_id=ii.id WHERE ii.username='joe.doe');

Seznam účtů identity:

SELECT i.username, aca.created, aca.account_id, aa.id, aa.uid FROM idm_identity i LEFT JOIN acc_identity_account aca ON i.id=aca.identity_id LEFT JOIN acc_account aa ON aca.account_id=aa.id WHERE i.username='loginidentity';

Seznam systémových identifikátorů účtů (entit na systému) na konkrétním systému vytvořených od určité doby a login identity, kterým patří:

SELECT ii.username, sse.uid FROM sys_system_entity sse JOIN acc_account aa ON aa.system_entity_id=sse.id JOIN acc_identity_account aia ON aia.account_id=aa.id JOIN idm_identity ii ON aia.identity_id=ii.id WHERE sse.system_id='\x9e73326d94ca4f1ba115c8b85900b5bb' AND sse.created > '2020-01-27 00:00:00.000'

Seznam účtů v provisioning frontě (aktivní operace):

SELECT spo.created, spo.result_state, sse.uid FROM sys_provisioning_operation spo JOIN sys_system_entity sse ON spo.system_entity_id=sse.id LIMIT 100;

Seznam identit, které mají víc než jeden platný kontrakt:

SELECT i.username FROM idm_identity i JOIN idm_identity_contract ic ON ic.identity_id=i.id AND (ic.valid_till IS NULL OR ic.valid_till > '2020-04-21' ) GROUP BY i.username HAVING COUNT(*) > 1;

Vyhledání účtů, které nemají vazbu přes roli, nejsou v protected režimu a nejde o účet na systému Vema: (ideálně by mělo být prázdné)

SELECT aa.in_protection, ss.name, aa.uid FROM acc_identity_account aia JOIN acc_account aa ON aia.account_id=aa.id JOIN sys_system ss ON aa.system_id=ss.id WHERE ss.name NOT LIKE 'Vema%' AND identity_role_id IS NULL AND aa.in_protection = FALSE;

Kontrola konzistence účtů identit - jestli je pro každou přidělenou roli vytvořena vazba na účet (prázdný výsledek = vše OK)

SELECT i.username, r.name, aca.identity_role_id, aca.account_id, ss.name AS systemname FROM idm_identity i
JOIN idm_identity_contract ic ON ic.identity_id=i.id
JOIN idm_identity_role ir ON ir.identity_contract_id=ic.id
JOIN idm_role r ON ir.role_id=r.id
LEFT JOIN sys_role_system srs ON srs.role_id=r.id
LEFT JOIN sys_system ss ON srs.system_id=ss.id
LEFT JOIN acc_identity_account aca ON i.id=aca.identity_id AND aca.identity_role_id=ir.id
WHERE aca.identity_role_id IS NULL AND srs.role_id IS NOT NULL
ORDER BY i.username

Zjištění, jaké identity se týká chyba při přepočtu dirty flagů pro časové řezy:

SELECT username FROM idm_identity WHERE id IN (SELECT identity_id FROM idm_contract_slice WHERE id IN (SELECT owner_id FROM idm_entity_state WHERE id = uuid_send('0c44b1b4-1277-4473-a57a-bd6538e5a0f1'::uuid)));

Přidělené role a identity:

SELECT i.username, r.name, ir.valid_till FROM idm_identity i
JOIN idm_identity_contract ic ON ic.identity_id=i.id
JOIN idm_identity_role ir ON ir.identity_contract_id=ic.id
JOIN idm_role r ON ir.role_id=r.id
ORDER BY r.name, i.username

Seznam identit, které nemají přidělenou konkrétní roli (v selectu: "Název role")

SELECT i.username FROM idm_identity i
JOIN idm_identity_contract ic ON ic.identity_id=i.id
LEFT JOIN idm_identity_role ir ON ir.identity_contract_id=ic.id AND ir.role_id IN (SELECT id FROM idm_role WHERE name ='Název role')
WHERE ir.id IS NULL

Seznam identit, které mají přidělenou konkrétní roli (v selectu: "Kód přidělené role"), ale nemají jinou roli (v selectu: "Kód NEpřidělené role"), spolu s informací o kontraktu a organizačním zařazení.

SELECT i.username, i.email, i.state, ic.valid_from, ic.valid_till, itn.code, itn.name FROM idm_identity i
JOIN idm_identity_contract ic ON ic.identity_id=i.id
LEFT JOIN idm_tree_node itn ON ic.work_position_id=itn.id
LEFT JOIN idm_identity_role ir ON ir.identity_contract_id=ic.id AND ir.role_id IN (SELECT id FROM idm_role WHERE code ='Kód NEpřidělené role')
JOIN idm_identity_role ir2 ON ir2.identity_contract_id=ic.id AND ir2.role_id IN (SELECT id FROM idm_role WHERE code ='Kód přidělené role')
WHERE ir.id IS NULL AND ir2.id IS NOT NULL

Přidělené role včetně informace, pokud je přidělena jako automatická na organizaci:

SELECT i.username, i.first_name, i.last_name, r.name nazevrole, ar.name AS automatickarole FROM idm_identity i
JOIN idm_identity_contract ic ON ic.identity_id=i.id
JOIN idm_identity_role ir ON ir.identity_contract_id=ic.id
JOIN idm_role r ON ir.role_id=r.id
LEFT JOIN idm_auto_role ar ON ir.automatic_role_id=ar.id
ORDER BY i.username, r.name

Hodnoty parametrů rolí s kódem "amis_text" z definice "roleParameters":

SELECT * FROM idm_identity_role_form_value WHERE attribute_id IN (SELECT id FROM idm_form_attribute WHERE code='amis_text' AND definition_id IN (SELECT id FROM idm_form_definition WHERE code='roleParameters')) ;

"Duplicitně" ručně přiřazené role skrze všechny role uživatele. Nejedná se o klasické duplicity na jednou úvazku.

SELECT
    i.username,
    r.code
FROM
    idm_identity_role ir
JOIN idm_identity_contract ic ON
    ic.id = ir.identity_contract_id
JOIN idm_identity i ON
    i.id = ic.identity_id
JOIN idm_role r ON
    r.id = ir.role_id
WHERE
    1 < (
    SELECT
        COUNT(*)
    FROM
        idm_identity_role ir2
    JOIN idm_identity_contract ic2 ON
        ic2.id = ir2.identity_contract_id
    JOIN idm_identity i2 ON
        i2.id = ic2.identity_id
    WHERE
        ir2.role_id = ir.role_id
        AND i2.id = i.id)
    AND ir.automatic_role_id IS NULL
    AND ir.role_composition_id IS NULL;

Dotaz pro zjištění duplicit v přiřazených automatických rolí při špatném vyvolání save může dojít ke dvou přepočtům pro jednu identitu a oba přiřadí stejnou roli. Pozor pokud budete tyto identity role mazat, smažte i další vazby jako sub role, případně účty.

SELECT
    *
FROM
    idm_identity_role ir
WHERE
    (
        SELECT COUNT(*)
    FROM
        idm_identity_role ir2
    WHERE
        ir.identity_contract_id = ir2.identity_contract_id
        AND ir2.automatic_role_id = ir.automatic_role_id) > 1;

Vypsání všech automatických rolí dle atributů a pravidel, na jaké atributy jsou nastaveny: (ḱód role, název role, název automatické role, název atributu v pravidle automatické role, typ atributu, hodnota v pravidle automatické role)

SELECT ir.code, ir.name, iar.name, iarar.attribute_name, iarar.type, iarar.value FROM idm_auto_role iar JOIN idm_role ir ON iar.role_id=ir.id JOIN idm_auto_role_attribute iara ON iar.id=iara.id JOIN idm_auto_role_att_rule iarar ON iara.id=iarar.auto_role_att_id ORDER BY ir.code

Vypsání všech automatických rolí dle organizace (pozor, pouze přímo nastavené automatické role na dané organizaci, nebere v úvahu dědičnost):

SELECT tn.code, r.code FROM idm_role_tree_node rtn JOIN idm_auto_role ar ON rtn.id=ar.id
JOIN idm_tree_node tn ON rtn.tree_node_id=tn.id
JOIN idm_role r ON ar.role_id=r.id

Vypsání všech automatických rolí pro organizaci, které zdědila z nadřazených organizací (pouze zděděné role z rodičů - přímo nastavené role viz dotaz výše):

SELECT itn.code kdo_dedi, itnparents.code odkud_dedi, ir.code, irtn.recursion_type FROM idm_tree_node itn
JOIN idm_forest_index ifi ON ifi.content_id = itn.id
JOIN idm_forest_index ifiparents ON ifi.lft> ifiparents.lft AND ifi.rgt <ifiparents.rgt AND ifi.forest_tree_type=ifiparents.forest_tree_type
JOIN idm_tree_node itnparents ON ifiparents.content_id=itnparents.id
LEFT JOIN idm_role_tree_node irtn ON irtn.tree_node_id=itnparents.id AND irtn.recursion_type='DOWN'
LEFT JOIN idm_auto_role iar ON irtn.id=iar.id
LEFT JOIN idm_role ir ON iar.role_id=ir.id
WHERE ir.code IS NOT NULL AND itn.code='kód organizace, pro kterou mě zajímají zděděné role';

Vyhledání všech organizací, které jsou listy (nemají už žádné podřazené organizace) a zároveň nemají žádnou automatickou roli:

SELECT tn.id, tn.code, tn.name FROM idm_tree_node tn WHERE tn.id NOT IN (SELECT tree_node_id FROM idm_role_tree_node) AND tn.id NOT IN (SELECT parent_id FROM idm_tree_node WHERE parent_id IS NOT NULL) ORDER BY tn.code

Vyhledání, zda chybí nějaké přidělení automatické role dle organizací (funguje jen na automatické role se strategií Bez šíření). Ideálně by mělo být prázdné.

SELECT i.username, i.first_name, i.last_name, itn.code, ic.state, itn.name, ar.name nazevrole, ar.name AS automatickarole FROM idm_identity i
JOIN idm_identity_contract ic ON ic.identity_id=i.id AND (ic.valid_till IS NULL OR ic.valid_till > now()) AND (ic.state IS NULL OR ic.state != 'DISABLED')
JOIN idm_tree_node itn ON itn.id=ic.work_position_id
JOIN idm_role_tree_node irtn ON irtn.tree_node_id=itn.id
JOIN idm_auto_role ar ON irtn.id=ar.id LEFT JOIN idm_identity_role ir ON ir.automatic_role_id=irtn.id WHERE ir.id IS NULL;

Vytažení hodnot a strategie, které plní role pro konkrétní atribut na konkrétním systému:

SELECT ir.code, ss.name, srsa.name, transform_script, strategy_type FROM sys_role_system_attribute srsa JOIN sys_role_system srs ON srsa.role_system_id=srs.id JOIN sys_system ss ON srs.system_id=ss.id JOIN idm_role ir ON ir.id=srs.role_id WHERE srsa.name = 'profiles' AND ss.name = 'Golem GCD Test Koop - operátoři' ORDER BY ir.code,ss.name, srsa.name;

Vypsání příznaků "Dopředná správa účtu", "Automaticky vytvářet účty" a "Přeskočit hodnotu, pokud je kontrakt vyňat" u rolí, které odpovídají AD skupinám zařazeným v OU=Mail

SELECT r.name AS role_name, rs.forward_acm_enabled, rs.create_acc_by_default, rsa.skip_value_if_excluded, rsa.transform_script
FROM idm_role r
JOIN sys_role_system rs ON r.id=rs.role_id
JOIN sys_role_system_attribute rsa ON rs.id=rsa.role_system_id AND rsa.name='ldapGroups'
WHERE rsa.transform_script LIKE '%OU=Mail%'

Sezname rolí s informací, jaké přidělují oprávnění: (ale pozor, neukazuje to obsah "Configuration" = evaluator_properties, protože to je binární, ale může obsahovat také důležité nastavení, např. u IdentityContractByIdentityEvaluator volba "Use permissions")

SELECT ir.code, authorizable_type, base_permissions, evaluator_type, group_permission FROM idm_authorization_policy iap JOIN idm_role ir ON iap.role_id=ir.id ORDER BY ir.code, iap.authorizable_type

Účty a jejich atributy ve virtuálním systému AMIS:

SELECT vs.uid, ifa.code, vsfv.string_value FROM vs_account_form_value vsfv JOIN idm_form_attribute ifa ON vsfv.attribute_id=ifa.id JOIN idm_form_definition ifd ON ifa.definition_id=ifd.id AND ifd.name LIKE 'Virtual system for %AMIS%' JOIN vs_account vs ON vs.id=vsfv.owner_id ORDER BY vs.uid, ifa.code;

Zda je nastaveno schvalování požadavků realizátorem na virtuálních systémech:

SELECT ss.name, ifd.code, ssfv.boolean_value FROM sys_system_form_value ssfv  JOIN sys_system ss ON ssfv.owner_id=ss.id
JOIN idm_form_attribute ifa ON ssfv.attribute_id = ifa.id AND ifa.code IN  ('requiredConfirmation')
JOIN idm_form_definition ifd ON ifa.definition_id=ifd.id
ORDER BY ss.name, ifd.code

Účty a jejich atributy exportované v jednom řádku pohromadě:

copy (select login, string_agg(hodnota, ';') from (SELECT vs.uid as login, ifa.code, string_agg(vsfv.string_value, ',') as hodnota FROM vs_account_form_value vsfv JOIN idm_form_attribute ifa ON vsfv.attribute_id=ifa.id JOIN idm_form_definition ifd ON ifa.definition_id=ifd.id AND ifd.name LIKE 'Virtual system for %HP%' JOIN vs_account vs ON vs.id=vsfv.owner_id group by vs.uid, ifa.code order by vs.uid,ifa.code) as test group by login) TO '/tmp/hpux_lepsi_export_2019-09-13.csv' CSV HEADER DELIMITER ';';

Účty z virtuálu, které nejsou spárovány s žádnou identitou:

SELECT uid FROM vs_account va JOIN sys_system ss ON va.system_id=ss.id WHERE ss.name='Exchange' AND va.uid NOT IN (SELECT uid FROM acc_account aa WHERE system_id IN (SELECT id FROM sys_system WHERE name='Exchange') AND aa.id IN (SELECT account_id FROM acc_identity_account));

Události jsou ve dvou tabulkách: idm_entity_event a idm_entity_state. V tabulce idm_entity_state jsou pro událost záznam, pokud už se začala provádět; do té doby ne. V tabulce idm_entity_state však jsou i další věci, které se netýkají jen událostí (např. dirty flagy ze synchronizace časových řezů), takže opatrně při mazání.

Výpis čekajících událostí způsobených přidělením role - identita a role:

SELECT i.username, r.name, ee.* FROM idm_entity_event ee
LEFT JOIN idm_identity_role ir ON ee.owner_id=ir.id
LEFT JOIN idm_role r ON ir.role_id=r.id
LEFT JOIN idm_identity_contract ic ON ir.identity_contract_id=ic.id
LEFT JOIN idm_identity i ON ic.identity_id=i.id
WHERE ee.result_state='CREATED' AND ee.owner_type='eu.bcvsolutions.idm.core.model.entity.IdmIdentityRole'

Seznam dalších pozic (other positions) identity včetně typu organizační struktury:

SELECT ii.username, itn.code, itn.name, itt.code AS typstruktury, icp.position FROM idm_contract_position icp
JOIN idm_identity_contract iic ON icp.identity_contract_id=iic.id
JOIN idm_identity ii ON iic.identity_id=ii.id
JOIN idm_tree_node itn ON icp.work_position_id=itn.id
JOIN idm_tree_type itt ON itn.tree_type_id=itt.id
WHERE ii.username='loginuzivatele'

Seznam kontraktů uživatelů, které jsou duplicitní (mají žádnou nebo stejnou vazbu do organizační struktury, stejný stav, stejnou platnost do)

SELECT i.username, ic.identity_id, ic.work_position_id, tn.code, ic.state, ic.valid_till, COUNT(*) num_duplicate_contracts, MIN(encode(ic.id, 'hex')) contract_to_save
FROM idm_identity_contract ic
JOIN idm_identity i ON ic.identity_id=i.id
LEFT JOIN idm_tree_node tn ON ic.work_position_id=tn.id
GROUP BY i.username, ic.identity_id, ic.work_position_id, tn.code, ic.state, ic.valid_till
HAVING COUNT(*)> 1;

Když potřebuju zjistit, jestli je Groovy skript použit v nějaké transformaci do/ze systému, není to vidět v agendě "Použití script", protože v transformacích se skripty identifikují jen svými kódy.

Transformační skripty použité v mapování atributů na systému:

SELECT transform_from_res_script, transform_to_res_script FROM sys_system_attribute_mapping WHERE transform_from_res_script IS NOT NULL OR transform_to_res_script IS NOT NULL;
-- Vcetne identifikace systemu a nazvu atributu
SELECT ss.name, ssam.name,transform_from_res_script, transform_to_res_script FROM sys_system_attribute_mapping ssam JOIN sys_system_mapping AS ssm ON ssam.system_mapping_id=ssm.id JOIN sys_schema_obj_class ssoc ON ssm.object_class_id=ssoc.id JOIN sys_system AS ss ON ssoc.system_id=ss.id WHERE transform_from_res_script IS NOT NULL OR transform_to_res_script IS NOT NULL ORDER BY ss.name, ssam.name ;

Transformační skripty použité v mapování atributů na systému - ty, které jsou v knihovně skriptů:

SELECT name, SUBSTRING (transform_from_res_script FROM 'setScriptCode[^)]*') skriptz, SUBSTRING (transform_to_res_script FROM 'setScriptCode[^)]*') skriptdo FROM sys_system_attribute_mapping WHERE transform_from_res_script LIKE '%setScriptCode%' OR transform_to_res_script LIKE '%setScriptCode%' ORDER BY skriptz, skriptdo;

Transformační skripty použité v rolích:

SELECT transform_script FROM sys_role_system_attribute;

Transformační skripty použité v rolích včetně informace o roli:

SELECT ir.code, transform_script FROM sys_role_system_attribute srsa JOIN sys_role_system srs ON srsa.role_system_id=srs.id JOIN idm_role ir ON ir.id=srs.role_id ORDER BY ir.code

Report nástupů a odchodů identit za posledních 30 dní - tj. změny stavu identity v posledních 30 dnech (a pozor, dotaz teoreticky může obsahovat záznamy navíc, pokud se na dané identitě v tom období provedlo několik nástupů a odchodů):

SELECT all_i.datum_zmeny_stavu, all_i.typ_zmeny, i.username, i.first_name, i.last_name, i.state, ic.valid_from, ic.valid_till, ic.position, iguar.username AS nadrizeny, tn.code AS kod_pozice, tn.name AS nazev_pozice, tnparent.code AS department_code, tnparent.name AS department_name, icfv.short_text_value AS misto_vykonu_prace
FROM  (
    SELECT i.id, to_timestamp(MAX(a.timestamp/1000)) AS datum_zmeny_stavu, 'Nastup' AS typ_zmeny
    FROM idm_audit a
    JOIN idm_identity i ON a.entity_id=i.id
    WHERE a.changed_attributes LIKE '%state%' AND i.state IN ('FUTURE_CONTRACT', 'VALID') AND to_timestamp(a.timestamp/1000)> NOW() - INTERVAL '30 days'
    GROUP BY i.id
    UNION
    SELECT i.id, to_timestamp(MAX(a.timestamp/1000)) AS datum_zmeny_stavu, 'Odchod' AS typ_zmeny
    FROM idm_audit a
    JOIN idm_identity i ON a.entity_id=i.id
    WHERE a.changed_attributes LIKE '%state%' AND i.state IN ('LEFT') AND to_timestamp(a.timestamp/1000)> NOW() - INTERVAL '30 days'
    GROUP BY i.id
) AS all_i
JOIN idm_identity i ON all_i.id=i.id
JOIN idm_identity_contract ic ON all_i.id=ic.identity_id
    AND ((all_i.typ_zmeny='Nastup' AND (ic.valid_till IS NULL OR ic.valid_till> now()) AND ic.state IS NULL)
        OR
        (all_i.typ_zmeny IN ('Odchod') AND (ic.valid_till <now() OR ic.state = 'DISABLED')))
LEFT JOIN idm_tree_node tn ON ic.work_position_id=tn.id
LEFT JOIN idm_tree_node tnparent ON tn.parent_id=tnparent.id
LEFT JOIN idm_i_contract_form_value icfv ON icfv.owner_id=ic.id AND icfv.attribute_id IN (SELECT id FROM idm_form_attribute WHERE code='misto_vykonu_prace')
LEFT JOIN idm_contract_guarantee cg ON cg.identity_contract_id=ic.id
LEFT JOIN idm_identity iguar ON iguar.id=cg.guarantee_id
ORDER BY i.username

Výhodný dotaz pokud chcete zjistit kdo je přihlášen.

SELECT
    t.id,
    t.created,
    t.expiration,
    t.issued_at,
    i.username
FROM
    idm_token t
LEFT JOIN idm_identity i ON
    i.id = owner_id
WHERE
    t.token_type = 'CIDMST'
    AND t.owner_type = 'eu.bcvsolutions.idm.core.model.entity.IdmIdentity'
ORDER BY
    created DESC;

První příkaz obsahuje záznam o každém nahrání nové verze workflow (automaticky i manuálně). Druhý příkaz přehled verzí. U různých workflow se zjevně různě tvoří ID, což je zvláštní, ale na funkci to asi nemá vliv.

SELECT * FROM act_re_deployment ORDER BY deploy_time_;
SELECT id_, name_, rev_, key_, version_, resource_name_ FROM act_re_procdef ORDER BY key_;