Service Desk Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Easyway to document Roles, DB & UI Rules doing SQL Queries

SOLVED
Go to solution
Highlighted
Rory Emmans
Member

Easyway to document Roles, DB & UI Rules doing SQL Queries

Does anybody know or have the SQL commands to extract Roles which will access to which fields & Statuses, the UI & DB Rules via SQL queries. Just looking for an easier / faster way to keep up with SD documentation. Thanks in advance
5 REPLIES
Mark O'Loughlin
Honored Contributor

Re: Easyway to document Roles, DB & UI Rules doing SQL Queries

Hi Rory,

Im laughing to myself at the moment. I thought this was going to be a gem of a thread like Georges one on tips for relationships and sd_dataform but realised it was a question rather than a statement.
George M. Meneg
Honored Contributor

Re: Easyway to document Roles, DB & UI Rules doing SQL Queries

Hello Rory,

select distinct rol.ROL_DESCRIPTION as 'Role', ent.ent_name as 'Entity', rct1.rct_name as 'Folder',
rct2.rct_name as 'Status From', rct3.rct_name as 'Status To', ena.*
from REP_ENTITY_ACCESS ena inner join ifc_entities ent on ena.ena_ent_oid=ent.ent_oid
left outer join rep_codes_text rct1 on ena.ena_cod_oid=rct1.rct_rcd_oid
left outer join rep_codes_text rct2 on ena.ena_status_from_oid=rct2.rct_rcd_oid
left outer join rep_codes_text rct3 on ena.ena_status_to_oid=rct3.rct_rcd_oid
inner join rep_roles rol on ena.ena_rol_oid=rol.rol_oid
where rct1.rct_lng_oid=1033
order by rol.ROL_DESCRIPTION asc

this query will fetch information about acess per entity/folder/status

The query doesn't take roles inclusion into account, it just presents access per role and doesn't care if the role is included to another role or includes another role.
menes fhtagn
George M. Meneg
Honored Contributor
Solution

Re: Easyway to document Roles, DB & UI Rules doing SQL Queries

Something else: While a query about db/ui rules can be created, since db/ui rules are encoded java objects and no one besides hp knows how to decode isn't meaningful. The only information you can have is whether it is UI or DB rule and a long encoded string that no one can understand.
menes fhtagn
Rory Emmans
Member

Re: Easyway to document Roles, DB & UI Rules doing SQL Queries

Thanks George that will be a time saver for sure. I know I am pushing my luck but you wouldn't by chance have a sql command to extract fields, forms and templates used by Role via SQL commands would you?
JaS_4
Honored Contributor

Re: Easyway to document Roles, DB & UI Rules doing SQL Queries

Hi Rory,

HP created ITSM007071 available since Sp12 which will unnserialize the UI & DB info and put it in a text format for you.
If you look in the docs of the SP, you will find itsm007071 which have a cmd file which you will need to change to point to your client path (do a search and replace).
Once you run it, it will produce 2 output. One for ui and one for db rules. Note, this is read only program and do not affect your db. It's original intention was not for rules documentation but to locate corrupted rules and provide sql statements to delete those corrupted rules.
//Add this to "OnDomLoad" event