Коллеги, данный материал будет полезен администраторам системы bpm'online с большим количеством пользователей, а также консультантам и программистам, ведущим разработку на платформе bpm'online.
Для решения разного рода бизнес-задач, а также быстрого анализа проблем, связанных с организационной структурой, пригодится SQL-скрипт, который выводит сводную информацию о пользователях системы:
1) Имя пользователя
2) Активен (да / нет)
3) Контакт пользователя
4) Тип контакта пользователя
5) Контрагент контакта пользователя
6) Тип контрагента контакта пользователя
7) Культура пользователя (5.4.0+)
8) Лицензии пользователя
9) Роли пользователя
10) Является ли пользователь синхронизированным с LDAP (да / нет)
11) Идентификатор пользователя
12) Идентификатор контакта пользователя
13) Идентификатор контрагента контакта пользователя
bpm'online 5.4.0 - 7.X:
SELECT
SysAdminUnit.Name AS Users,
SysLicPackage.Name AS SLPN,
SAUR.Name AS Rol,
SysAdminUnit.Active AS Active,
Contact.Name AS Contacts,
ContactType.Name AS ContactTypes,
Account.Name AS Accounts,
AccountType.Name AS AccountTypes,
SysAdminUnit.SynchronizeWithLDAP AS LDAP,
SysCulture.Name AS Culture,
SysAdminUnit.Id AS UserID,
Contact.Id AS ContactID,
Account.Id AS AccountID
INTO #temp FROM SysLicUser
FULL OUTER JOIN SysAdminUnit ON SysAdminUnit.Id = SysLicUser.SysUserId
LEFT OUTER JOIN SysUserInRole ON SysUserInRole.SysUserId = SysAdminUnit.Id
LEFT OUTER JOIN SysAdminUnit SAUR ON SAUR.Id = SysUserInRole.SysRoleId
LEFT OUTER JOIN SysLicPackage ON SysLicPackage.Id = SysLicUser.SysLicPackageId
INNER JOIN Contact ON Contact.Id = SysAdminUnit.ContactId
LEFT OUTER JOIN ContactType ON ContactType.Id = Contact.TypeId
LEFT OUTER JOIN Account ON Account.Id = Contact.AccountId
LEFT OUTER JOIN AccountType ON AccountType.Id = Account.TypeId
LEFT OUTER JOIN SysCulture ON SysCulture.Id = SysAdminUnit.SysCultureId
SELECT DISTINCT
Users,
Active,
Contacts,
ContactTypes,
Accounts,
AccountTypes,
Culture,
(SELECT DISTINCT cast(SLPN AS varchar(max)) + ', ' AS 'data()' FROM #temp SLU2 WHERE SLU1.Users=SLU2.Users FOR xml path('') ) AS Licenses,
(SELECT DISTINCT cast(Rol AS varchar(max)) + ', ' AS 'data()' FROM #temp SLU2 WHERE SLU1.Users=SLU2.Users FOR xml path('') ) AS Roles,
LDAP,
UserID,
ContactID,
AccountID
FROM #temp SLU1
GROUP BY Users, Active, Contacts, ContactTypes, Accounts, AccountTypes, LDAP, Culture, UserID, ContactID, AccountID
DROP TABLE #temp
bpm'online 5.3:
SELECT
SysAdminUnit.Name AS Users,
SysPackage.Name AS SLPN,
SAUR.Name AS Rol,
SysAdminUnit.Active AS Active,
Contact.Name AS Contacts,
ContactType.Name AS ContactTypes,
Account.Name AS Accounts,
AccountType.Name AS AccountTypes,
SysAdminUnit.SynchronizeWithLDAP AS LDAP,
SysAdminUnit.Id AS UserID,
Contact.Id AS ContactID,
Account.Id AS AccountID
INTO #temp FROM SysLicUser
FULL OUTER JOIN SysAdminUnit ON SysAdminUnit.Id = SysLicUser.SysUserId
LEFT OUTER JOIN SysUserInRole ON SysUserInRole.SysUserId = SysAdminUnit.Id
LEFT OUTER JOIN SysAdminUnit SAUR ON SAUR.Id = SysUserInRole.SysRoleId
LEFT OUTER JOIN SysPackage ON SysPackage.Id = SysLicUser.SysPackageId
INNER JOIN Contact ON Contact.Id = SysAdminUnit.ContactId
LEFT OUTER JOIN ContactType ON ContactType.Id = Contact.TypeId
LEFT OUTER JOIN Account ON Account.Id = Contact.AccountId
LEFT OUTER JOIN AccountType ON AccountType.Id = Account.TypeId
SELECT DISTINCT
Users,
Active,
Contacts,
ContactTypes,
Accounts,
AccountTypes,
(SELECT DISTINCT cast(SLPN AS varchar(max)) + ', ' AS 'data()' FROM #temp SLU2 WHERE SLU1.Users=SLU2.Users FOR xml path('') ) AS Licenses,
(SELECT DISTINCT cast(Rol AS varchar(max)) + ', ' AS 'data()' FROM #temp SLU2 WHERE SLU1.Users=SLU2.Users FOR xml path('') ) AS Roles,
LDAP,
UserID,
ContactID,
AccountID
FROM #temp SLU1
GROUP BY Users, Active, Contacts, ContactTypes, Accounts, AccountTypes, LDAP, UserID, ContactID, AccountID
DROP TABLE #temp
