SQL Universal report
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
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.
Example of report configuration with code list
===== Mandatory config for classic installation =====
You need to configure an additional application.properties file. This report is using a separate datasource for safety reasons.
<note warning>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!</note>
For example read only user for whole IdM database:
<note warning>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</note>
<code>
Turn off IdM and then execute:
CREATE USER czechidmreport PASSWORD 'keepass';
GRANT CONNECT ON DATABASE czechidm TO czechidmreport;
\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;
</code>
The properties you need to configure are specified below.
<code>
# 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
</code>
===== Config for installation on IAM appliance =====
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):
<code>
systemctl stop iam-czechidm
</code>
Add the reports module to additional modules for your IdM installation:
<code>
cp idm-reports-3.0.0.jar /data/volumes/czechidm/modules/
</code>
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:
<note warning>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.</note>
<code>
docker exec -it czechidm-db /bin/bash
su postgres
psql
CREATE USER czechidmreport PASSWORD 'some secure password*';
GRANT CONNECT ON DATABASE czechidm TO czechidmreport;
\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 czechidmreport;
\q
exit
exit
</code>
Back on the server (outside of the docker container), save the password of the user you just created to a separate file:
<code>
echo "some secure password*"> /data/volumes/czechidm/secrets/idmdbreport.pwfile
</code>
Finally, update the definition of the iam-czechidm service in the file /data/registry/node-active-config/docker-compose-czechidm.yml:
<code>
# uncomment (i.e. remove hash from) the following lines in the "environment" section:
- CZECHIDMREPORTSDBURL=jdbc:postgresql:czechidm-db:5432/czechidm
- CZECHIDMREPORTSDBUSER=czechidmreport
- CZECHIDMREPORTSDBPASSFILE=/run/secrets/dbreport.pwfile
- CZECHIDMREPORTSDB_DRIVERCLASS=org.postgresql.Driver
# add following new lines in the "volumes" section:
- type: bind
source: /data/volumes/czechidm/secrets/idmdbreport.pwfile
target: /run/secrets/dbreport.pwfile
readonly: true
</code>
Start the IdM service:
<code>
systemctl start iam-czechidm
</code>
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.
: The following chapters are in Czech for now, translation is in progress.
===== Most frequently used tables =====
^Table^Data|
|idmidentity|Identities (users)|
|idmidentitycontract|Contracts of the identities|
|idmcontractguarantee|Managers of contracts of the identities|
|idmidentityrole|Roles assigned on the contracts of the identities|
|idmrole|Roles|
|idmtreenode|Organizational structure (for all structure types)|
|idmtreetype|Organizational structure types|
|idmformdefinition|Form definitions|
|idmformattribute|Form attributes - definition of all EAVs for all entity types|
|idmidentityformvalue, idmicontractformvalue, idmroleformvalue, …|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:
<code sql>
SELECT * FROM idmentityevent WHERE ID = uuid_send('02fdad6a-1113-4102-bc7d-90fe64c2ed16'::uuid);
</code>
MS SQL Server. Do příkazu se dá UUID upravené - odstraní se pomlčky, přidá se na začátek "0x":
<code sql>
SELECT * FROM bcvidmstorage.idmentityevent WHERE ID = CONVERT(VARBINARY(MAX), '0x02fdad6a11134102bc7d90fe64c2ed16', 1)
</code>
Zobrazení ID ve formátu, který je vidět v GUI:
<code sql>
SELECT CAST(ENCODE(id, 'hex') AS UUID) FROM idmentityevent;
</code>
===== Dotazy nad synchronizací =====
Vytáhnutí seznamu identifikátorů pro daný běh synchronizace.
<code sql>
– systemmappingid 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(transactionid, 'hex')::uuid, creator FROM syssynclog WHERE synchronizationconfigid = (SELECT syssyncconfig.id FROM syssyncconfig WHERE systemmappingid = uuidsend('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 syssyncitemlog WHERE transactionid = uuid_send('f47cfe6a-aa03-4a9c-9271-057bc1e173de'::uuid) AND (message LIKE '%UNLINKED%' OR message LIKE '%UNKNOWN%');
</code>
==== 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.
<code sql>
SELECT ssil.displayname,ssil.identification, ssal.syncaction, CAST(SUBSTRING(CAST(ss.id AS text) FROM 3) AS uuid) AS systemid, ss."name" AS systemname,ssal.created AS DATE,ssc."name" AS synchronizationname FROM syssyncitemlog ssil
LEFT JOIN syssynclog sync ON ssil.transactionid = sync.transactionid
LEFT JOIN syssyncactionlog ssal ON ssil.syncactionlogid = ssal.id
LEFT JOIN syssyncconfig ssc ON sync.synchronizationconfigid = ssc.id
LEFT JOIN syssystemmapping ssm ON ssc.systemmappingid = ssm.id
LEFT JOIN sysschemaobjclass ssoc ON ssm.objectclassid = ssoc.id
LEFT JOIN syssystem ss ON ssoc.systemid = ss.id
WHERE ssal.syncaction = 'MISSINGENTITY' AND
EXTRACT(WEEK FROM ssal.created) = EXTRACT(WEEK FROM CURRENTDATE)
AND EXTRACT(YEAR FROM ssal.created) = EXTRACT(YEAR FROM CURRENTDATE)
ORDER BY ssil.displayname;
</code>
===== Dotazy nad identitami a účty =====
Získání EAV atributů:
<code sql>
– identity s EAV atributem vpnExpiration a sshExpiration typu Date
SELECT ii.username, ii.disabled, ifa.name, iifv.datevalue FROM idmidentityformvalue iifv JOIN idmidentity ii ON iifv.ownerid=ii.id JOIN idmformattribute ifa ON iifv.attributeid = ifa.id AND ifa.name IN ('vpnExpiration','sshExpiration') ORDER BY iifv.datevalue
– identity s EAV atributem mailType typu ShortText a hodnotou Exchange
SELECT ii.username, ii.disabled, ifa.name, iifv.shorttextvalue FROM idmidentityformvalue iifv JOIN idmidentity ii ON iifv.ownerid=ii.id JOIN idmformattribute ifa ON iifv.attributeid = ifa.id AND ifa.name IN ('mailType') WHERE shorttextvalue='Exchange';
– zmeny EAV atributu pro kontrakty konkretni identity
SELECT created, shorttextvalue FROM idmicontractformvaluea WHERE ownerid IN (SELECT iic.id FROM idmidentitycontract iic JOIN idmidentity ii ON iic.identityid=ii.id WHERE ii.username='joe.doe');
</code>
Seznam účtů identity:
<code sql>
SELECT i.username, aca.created, aca.accountid, aa.id, aa.uid FROM idmidentity i LEFT JOIN accidentityaccount aca ON i.id=aca.identityid LEFT JOIN accaccount aa ON aca.account_id=aa.id WHERE i.username='loginidentity';
</code>
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ří:
<code sql>
SELECT ii.username, sse.uid FROM syssystementity sse JOIN accaccount aa ON aa.systementityid=sse.id JOIN accidentityaccount aia ON aia.accountid=aa.id JOIN idmidentity ii ON aia.identityid=ii.id WHERE sse.system_id='\x9e73326d94ca4f1ba115c8b85900b5bb' AND sse.created > '2020-01-27 00:00:00.000'
</code>
Seznam účtů v provisioning frontě (aktivní operace):
<code sql>
SELECT spo.created, spo.resultstate, sse.uid FROM sysprovisioningoperation spo JOIN syssystementity sse ON spo.systementity_id=sse.id LIMIT 100;
</code>
Seznam identit, které mají víc než jeden platný kontrakt:
<code sql>
SELECT i.username FROM idmidentity i JOIN idmidentitycontract ic ON ic.identityid=i.id AND (ic.validtill IS NULL OR ic.validtill > '2020-04-21' ) GROUP BY i.username HAVING COUNT(*) > 1;
</code>
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é)
<code sql>
SELECT aa.inprotection, ss.name, aa.uid FROM accidentityaccount aia JOIN accaccount aa ON aia.accountid=aa.id JOIN syssystem ss ON aa.systemid=ss.id WHERE ss.name NOT LIKE 'Vema%' AND identityroleid IS NULL AND aa.inprotection = FALSE;
</code>
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)
<code sql>
SELECT i.username, r.name, aca.identityroleid, aca.accountid, ss.name AS systemname FROM idmidentity i
JOIN idmidentitycontract ic ON ic.identityid=i.id
JOIN idmidentityrole ir ON ir.identitycontractid=ic.id
JOIN idmrole r ON ir.roleid=r.id
LEFT JOIN sysrolesystem srs ON srs.roleid=r.id
LEFT JOIN syssystem ss ON srs.systemid=ss.id
LEFT JOIN accidentityaccount aca ON i.id=aca.identityid AND aca.identityroleid=ir.id
WHERE aca.identityroleid IS NULL AND srs.roleid IS NOT NULL
ORDER BY i.username
</code>
Zjištění, jaké identity se týká chyba při přepočtu dirty flagů pro časové řezy:
<code sql>
SELECT username FROM idmidentity WHERE id IN (SELECT identityid FROM idmcontractslice WHERE id IN (SELECT ownerid FROM idmentitystate WHERE id = uuidsend('0c44b1b4-1277-4473-a57a-bd6538e5a0f1'::uuid)));
</code>
===== Dotazy nad rolemi a atributy =====
Přidělené role a identity:
<code sql>
SELECT i.username, r.name, ir.validtill FROM idmidentity i
JOIN idmidentitycontract ic ON ic.identityid=i.id
JOIN idmidentityrole ir ON ir.identitycontractid=ic.id
JOIN idmrole r ON ir.role_id=r.id
ORDER BY r.name, i.username
</code>
Seznam identit, které nemají přidělenou konkrétní roli (v selectu: "Název role")
<code sql>
SELECT i.username FROM idmidentity i
JOIN idmidentitycontract ic ON ic.identityid=i.id
LEFT JOIN idmidentityrole ir ON ir.identitycontractid=ic.id AND ir.roleid IN (SELECT id FROM idmrole WHERE name ='Název role')
WHERE ir.id IS NULL
</code>
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í.
<code sql>
SELECT i.username, i.email, i.state, ic.validfrom, ic.validtill, itn.code, itn.name FROM idmidentity i
JOIN idmidentitycontract ic ON ic.identityid=i.id
LEFT JOIN idmtreenode itn ON ic.workpositionid=itn.id
LEFT JOIN idmidentityrole ir ON ir.identitycontractid=ic.id AND ir.roleid IN (SELECT id FROM idmrole WHERE code ='Kód NEpřidělené role')
JOIN idmidentityrole ir2 ON ir2.identitycontractid=ic.id AND ir2.roleid IN (SELECT id FROM idmrole WHERE code ='Kód přidělené role')
WHERE ir.id IS NULL AND ir2.id IS NOT NULL
</code>
Přidělené role včetně informace, pokud je přidělena jako automatická na organizaci:
<code sql>
SELECT i.username, i.firstname, i.lastname, r.name nazevrole, ar.name AS automatickarole FROM idmidentity i
JOIN idmidentitycontract ic ON ic.identityid=i.id
JOIN idmidentityrole ir ON ir.identitycontractid=ic.id
JOIN idmrole r ON ir.roleid=r.id
LEFT JOIN idmautorole ar ON ir.automaticroleid=ar.id
ORDER BY i.username, r.name
</code>
Hodnoty parametrů rolí s kódem "amis_text" z definice "roleParameters":
<code sql>
SELECT * FROM idmidentityroleformvalue WHERE attributeid IN (SELECT id FROM idmformattribute WHERE code='amistext' AND definitionid IN (SELECT id FROM idmform_definition WHERE code='roleParameters')) ;
</code>
"Duplicitně" ručně přiřazené role skrze všechny role uživatele. Nejedná se o klasické duplicity na jednou úvazku.
<code sql>
SELECT
i.username,
r.code
FROM
idmidentityrole ir
JOIN idmidentitycontract ic ON
ic.id = ir.identitycontractid
JOIN idmidentity i ON
i.id = ic.identityid
JOIN idmrole r ON
r.id = ir.roleid
WHERE
1 < (
SELECT
COUNT(*)
FROM
idmidentityrole ir2
JOIN idmidentitycontract ic2 ON
ic2.id = ir2.identitycontractid
JOIN idmidentity i2 ON
i2.id = ic2.identityid
WHERE
ir2.roleid = ir.roleid
AND i2.id = i.id)
AND ir.automaticroleid IS NULL
AND ir.rolecompositionid IS NULL;
</code>
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.
<code sql>
SELECT
FROM
idmidentityrole ir
WHERE
(
SELECT COUNT()
FROM
idmidentityrole ir2
WHERE
ir.identitycontractid = ir2.identitycontractid
AND ir2.automaticroleid = ir.automaticroleid) > 1;
</code>
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)
<code sql>
SELECT ir.code, ir.name, iar.name, iarar.attributename, iarar.type, iarar.value FROM idmautorole iar JOIN idmrole ir ON iar.roleid=ir.id JOIN idmautoroleattribute iara ON iar.id=iara.id JOIN idmautoroleattrule iarar ON iara.id=iarar.autoroleatt_id ORDER BY ir.code
</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):
<code sql>
SELECT tn.code, r.code FROM idmroletreenode rtn JOIN idmautorole ar ON rtn.id=ar.id
JOIN idmtreenode tn ON rtn.treenodeid=tn.id
JOIN idmrole r ON ar.role_id=r.id
</code>
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):
<code sql>
SELECT itn.code kdodedi, itnparents.code odkuddedi, ir.code, irtn.recursiontype FROM idmtreenode itn
JOIN idmforestindex ifi ON ifi.contentid = itn.id
JOIN idmforestindex ifiparents ON ifi.lft> ifiparents.lft AND ifi.rgt <ifiparents.rgt AND ifi.foresttreetype=ifiparents.foresttreetype
JOIN idmtreenode itnparents ON ifiparents.contentid=itnparents.id
LEFT JOIN idmroletreenode irtn ON irtn.treenodeid=itnparents.id AND irtn.recursiontype='DOWN'
LEFT JOIN idmautorole iar ON irtn.id=iar.id
LEFT JOIN idmrole 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';
</code>
Vyhledání všech organizací, které jsou listy (nemají už žádné podřazené organizace) a zároveň nemají žádnou automatickou roli:
<code sql>
SELECT tn.id, tn.code, tn.name FROM idmtreenode tn WHERE tn.id NOT IN (SELECT treenodeid FROM idmroletreenode) AND tn.id NOT IN (SELECT parentid FROM idmtreenode WHERE parent_id IS NOT NULL) ORDER BY tn.code
</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é.
<code sql>
SELECT i.username, i.firstname, i.lastname, itn.code, ic.state, itn.name, ar.name nazevrole, ar.name AS automatickarole FROM idmidentity i
JOIN idmidentitycontract ic ON ic.identityid=i.id AND (ic.validtill IS NULL OR ic.validtill > now()) AND (ic.state IS NULL OR ic.state != 'DISABLED')
JOIN idmtreenode itn ON itn.id=ic.workpositionid
JOIN idmroletreenode irtn ON irtn.treenodeid=itn.id
JOIN idmautorole ar ON irtn.id=ar.id LEFT JOIN idmidentityrole ir ON ir.automaticrole_id=irtn.id WHERE ir.id IS NULL;
</code>
Vytažení hodnot a strategie, které plní role pro konkrétní atribut na konkrétním systému:
<code sql>
SELECT ir.code, ss.name, srsa.name, transformscript, strategytype FROM sysrolesystemattribute srsa JOIN sysrolesystem srs ON srsa.rolesystemid=srs.id JOIN syssystem ss ON srs.systemid=ss.id JOIN idmrole 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;
</code>
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
<code sql>
SELECT r.name AS rolename, rs.forwardacmenabled, rs.createaccbydefault, rsa.skipvalueifexcluded, rsa.transformscript
FROM idmrole r
JOIN sysrolesystem rs ON r.id=rs.roleid
JOIN sysrolesystemattribute rsa ON rs.id=rsa.rolesystemid AND rsa.name='ldapGroups'
WHERE rsa.transformscript LIKE '%OU=Mail%'
</code>
==== 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")
<code sql>
SELECT ir.code, authorizabletype, basepermissions, evaluatortype, grouppermission FROM idmauthorizationpolicy iap JOIN idmrole ir ON iap.roleid=ir.id ORDER BY ir.code, iap.authorizable_type
</code>
===== Dotazy nad virtuálními systémy =====
Účty a jejich atributy ve virtuálním systému AMIS:
<code sql>
SELECT vs.uid, ifa.code, vsfv.stringvalue FROM vsaccountformvalue vsfv JOIN idmformattribute ifa ON vsfv.attributeid=ifa.id JOIN idmformdefinition ifd ON ifa.definitionid=ifd.id AND ifd.name LIKE 'Virtual system for %AMIS%' JOIN vsaccount vs ON vs.id=vsfv.ownerid ORDER BY vs.uid, ifa.code;
</code>
Zda je nastaveno schvalování požadavků realizátorem na virtuálních systémech:
<code sql>
SELECT ss.name, ifd.code, ssfv.booleanvalue FROM syssystemformvalue ssfv JOIN syssystem ss ON ssfv.ownerid=ss.id
JOIN idmformattribute ifa ON ssfv.attributeid = ifa.id AND ifa.code IN ('requiredConfirmation')
JOIN idmformdefinition ifd ON ifa.definitionid=ifd.id
ORDER BY ss.name, ifd.code
</code>
Účty a jejich atributy exportované v jednom řádku pohromadě:
<code>
copy (select login, stringagg(hodnota, ';') from (SELECT vs.uid as login, ifa.code, stringagg(vsfv.stringvalue, ',') as hodnota FROM vsaccountformvalue vsfv JOIN idmformattribute ifa ON vsfv.attributeid=ifa.id JOIN idmformdefinition ifd ON ifa.definitionid=ifd.id AND ifd.name LIKE 'Virtual system for %HP%' JOIN vsaccount vs ON vs.id=vsfv.ownerid group by vs.uid, ifa.code order by vs.uid,ifa.code) as test group by login) TO '/tmp/hpuxlepsiexport_2019-09-13.csv' CSV HEADER DELIMITER ';';
</code>
Účty z virtuálu, které nejsou spárovány s žádnou identitou:
<code sql>
SELECT uid FROM vsaccount va JOIN syssystem ss ON va.systemid=ss.id WHERE ss.name='Exchange' AND va.uid NOT IN (SELECT uid FROM accaccount aa WHERE systemid IN (SELECT id FROM syssystem WHERE name='Exchange') AND aa.id IN (SELECT accountid FROM accidentity_account));
</code>
===== Dotazy nad událostmi =====
Události jsou ve dvou tabulkách: idmentityevent a idmentitystate. V tabulce idmentitystate jsou pro událost záznam, pokud už se začala provádět; do té doby ne. V tabulce idmentitystate 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:
<code sql>
SELECT i.username, r.name, ee.* FROM idmentityevent ee
LEFT JOIN idmidentityrole ir ON ee.ownerid=ir.id
LEFT JOIN idmrole r ON ir.roleid=r.id
LEFT JOIN idmidentitycontract ic ON ir.identitycontractid=ic.id
LEFT JOIN idmidentity i ON ic.identityid=i.id
WHERE ee.resultstate='CREATED' AND ee.owner_type='eu.bcvsolutions.idm.core.model.entity.IdmIdentityRole'
</code>
===== Dotazy nad organizacemi =====
Seznam dalších pozic (other positions) identity včetně typu organizační struktury:
<code sql>
SELECT ii.username, itn.code, itn.name, itt.code AS typstruktury, icp.position FROM idmcontractposition icp
JOIN idmidentitycontract iic ON icp.identitycontractid=iic.id
JOIN idmidentity ii ON iic.identityid=ii.id
JOIN idmtreenode itn ON icp.workpositionid=itn.id
JOIN idmtreetype itt ON itn.treetypeid=itt.id
WHERE ii.username='loginuzivatele'
</code>
===== Dotazy nad kontrakty =====
Seznam kontraktů uživatelů, které jsou duplicitní (mají žádnou nebo stejnou vazbu do organizační struktury, stejný stav, stejnou platnost do)
<code sql>
SELECT i.username, ic.identityid, ic.workpositionid, tn.code, ic.state, ic.validtill, COUNT() numduplicatecontracts, MIN(encode(ic.id, 'hex')) contracttosave
FROM idmidentitycontract ic
JOIN idmidentity i ON ic.identityid=i.id
LEFT JOIN idmtreenode tn ON ic.workpositionid=tn.id
GROUP BY i.username, ic.identityid, ic.workpositionid, tn.code, ic.state, ic.validtill
HAVING COUNT()> 1;
</code>
===== 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:
<code sql>
SELECT transformfromresscript, transformtoresscript FROM syssystemattributemapping WHERE transformfromresscript IS NOT NULL OR transformtoresscript IS NOT NULL;
– Vcetne identifikace systemu a nazvu atributu
SELECT ss.name, ssam.name,transformfromresscript, transformtoresscript FROM syssystemattributemapping ssam JOIN syssystemmapping AS ssm ON ssam.systemmappingid=ssm.id JOIN sysschemaobjclass ssoc ON ssm.objectclassid=ssoc.id JOIN syssystem AS ss ON ssoc.systemid=ss.id WHERE transformfromresscript IS NOT NULL OR transformtores_script IS NOT NULL ORDER BY ss.name, ssam.name ;
</code>
Transformační skripty použité v mapování atributů na systému - ty, které jsou v knihovně skriptů:
<code sql>
SELECT name, SUBSTRING (transformfromresscript FROM 'setScriptCode[^)]') skriptz, SUBSTRING (transformtoresscript FROM 'setScriptCode[^)]*') skriptdo FROM syssystemattributemapping WHERE transformfromresscript LIKE '%setScriptCode%' OR transformtoresscript LIKE '%setScriptCode%' ORDER BY skriptz, skriptdo;
</code>
Transformační skripty použité v rolích:
<code sql>
SELECT transformscript FROM sysrolesystemattribute;
</code>
Transformační skripty použité v rolích včetně informace o roli:
<code sql>
SELECT ir.code, transformscript FROM sysrolesystemattribute srsa JOIN sysrolesystem srs ON srsa.rolesystemid=srs.id JOIN idmrole ir ON ir.id=srs.roleid ORDER BY ir.code
</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ů):
<code sql>
SELECT alli.datumzmenystavu, alli.typzmeny, i.username, i.firstname, i.lastname, i.state, ic.validfrom, ic.validtill, ic.position, iguar.username AS nadrizeny, tn.code AS kodpozice, tn.name AS nazevpozice, tnparent.code AS departmentcode, tnparent.name AS departmentname, icfv.shorttextvalue AS mistovykonuprace
FROM (
SELECT i.id, totimestamp(MAX(a.timestamp/1000)) AS datumzmenystavu, 'Nastup' AS typzmeny
FROM idmaudit a
JOIN idmidentity i ON a.entityid=i.id
WHERE a.changedattributes LIKE '%state%' AND i.state IN ('FUTURECONTRACT', 'VALID') AND totimestamp(a.timestamp/1000)> NOW() - INTERVAL '30 days'
GROUP BY i.id
UNION
SELECT i.id, totimestamp(MAX(a.timestamp/1000)) AS datumzmenystavu, 'Odchod' AS typzmeny
FROM idmaudit a
JOIN idmidentity i ON a.entityid=i.id
WHERE a.changedattributes LIKE '%state%' AND i.state IN ('LEFT') AND totimestamp(a.timestamp/1000)> NOW() - INTERVAL '30 days'
GROUP BY i.id
) AS alli
JOIN idmidentity i ON alli.id=i.id
JOIN idmidentitycontract ic ON alli.id=ic.identityid
AND 1) AND ic.state IS NULL)
OR
(alli.typzmeny IN ('Odchod') AND (ic.validtill <now() OR ic.state = 'DISABLED')))
LEFT JOIN idmtreenode tn ON ic.workpositionid=tn.id
LEFT JOIN idmtreenode tnparent ON tn.parentid=tnparent.id
LEFT JOIN idmicontractformvalue icfv ON icfv.ownerid=ic.id AND icfv.attributeid IN (SELECT id FROM idmformattribute WHERE code='mistovykonuprace')
LEFT JOIN idmcontractguarantee cg ON cg.identitycontractid=ic.id
LEFT JOIN idmidentity iguar ON iguar.id=cg.guarantee_id
ORDER BY i.username
</code>
===== Dotazy TOKEN pro identity =====
Výhodný dotaz pokud chcete zjistit kdo je přihlášen.
<code sql>
SELECT
t.id,
t.created,
t.expiration,
t.issuedat,
i.username
FROM
idmtoken t
LEFT JOIN idmidentity i ON
i.id = ownerid
WHERE
t.tokentype = 'CIDMST'
AND t.ownertype = 'eu.bcvsolutions.idm.core.model.entity.IdmIdentity'
ORDER BY
created DESC;
</code>
===== 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.
<code sql>
SELECT * FROM actredeployment ORDER BY deploytime;
SELECT id, name, rev, key, version, resourcename_ FROM actreprocdef ORDER BY key_;
</code>