====== SQL Universal report ====== This report is a part of the reports module. **Since 3.0.0 version**. **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 {{ .:sql_report.png?nolink&600x514 }} 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 have to use only one method. Table/view OR an SQL query or Code list ===== Configuration for 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. {{ .:code_list_item_sql.png?nolink&800x354 }} Example of report configuration with code list {{ .:sql_report_codelist.png?nolink&800x681 }} ===== Mandatory config for classic installation ===== You need to configure an additional application.properties file. This report is using a separate datasource for safety reasons. For this report, 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 read only user for whole IdM database: If there are other database users than users from the standard installation (postgres, czechidm), you may need to set additional access privileges to them after running the following queries! Check the existence of the users by running e.g. \du, \dn+ or \z in psql client 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 ===== Config for installation on IAM appliance ===== If you have IdM installed as [[https://doc.czechidm.com|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: If there are other database users than users from the standard installation (postgres, czechidm), you may need to set additional access privileges to them after running the following queries! Check the existence of the users by running e.g. \du, \dn+ or \z in psql client if you are not sure. 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. ====== Example SQL queries ====== A simple list of collected SQL queries over the new IdM database. FIXME : The following chapters are in Czech for now, translation is in progress. ===== Most frequently used tables ===== ^Table^Data| |idm_identity|Identities (users)| |idm_identity_contract|Contracts of the identities| |idm_contract_guarantee|Managers of contracts of the identities| |idm_identity_role|Roles assigned on the contracts of the identities| |idm_role|Roles| |idm_tree_node|Organizational structure (for all structure types)| |idm_tree_type|Organizational structure types| |idm_form_definition|Form definitions| |idm_form_attribute|Form attributes - definition of all EAVs for all entity types| |idm_identity_form_value, idm_i_contract_form_value, idm_role_form_value, …|Values of EAVs for the given entity types| ===== Vyhledání objektu dle UUID ===== 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; ===== Dotazy nad synchronizací ===== 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%'); ==== Vytáhnutí neexistujících entit ==== 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; ===== Dotazy nad identitami a účty ===== 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))); ===== Dotazy nad rolemi a atributy ===== 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 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%' ==== Role a oprávnění ==== 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 ===== Dotazy nad virtuálními systémy ===== Úč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)); ===== Dotazy nad událostmi ===== 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' ===== Dotazy nad organizacemi ===== 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' ===== Dotazy nad kontrakty ===== 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; ===== Dotazy ohledně transformačních skriptů ===== 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 ===== Dotazy/reporty nad audity ===== 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 ===== Dotazy TOKEN pro identity ===== 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; ===== Dotazy nad activity (workflow) tabulkami ===== ==== Informace o deployovaných workflow ==== 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_;