38. One Identity Динамические группы
6 мая 2024 г.
12:14
The dynamic group calculations are done by generated stored procedures.
One option to get the runtimes is to rely on the statistics from the SQL Server itself.
Something like this:
* *
SELECT
DG.DisplayName AS DynamicGroup,
OBJECT_NAME(object_id) AS Procedurename,
CONVERT(INT, 1.0*SUM(deps.total_elapsed_time) / SUM(deps.execution_count) / 1000.0) AS AvgRuntimeMs,
(SELECT COUNT(*) FROM PersonInBasetree BT WHERE BT.UID_Org = dbo.QBM_FCVObjectkeyToElement('ColumnValue1', dg.ObjectKeyBaseTree)) AS [Number of persons assigned to that org]
FROM sys.dm_exec_procedure_stats deps
JOIN DynamicGroup dg ON OBJECT_ID(dbo.QER_FCVDynGuidToProcName(dg.uid_DynamicGroup, 'All')) = deps.object_id
WHERE deps.database_id = db_id()
AND dbo.QBM_FCVGUIDToModuleOwner(dg.UID_DynamicGroup) = 'CCC' --only custom defined dynamic group
GROUP BY DG.DisplayName, OBJECT_NAME(object_id), dg.ObjectKeyBaseTree
ORDER BY 3 DESC
Attention:
The runtime depends on 2 things:
-
How many changes are made in the run?
-
How many objects (person, workdesk, hardware) are already attached to the dynamic role?
So, please do not try to optimize the runtime down to 2ms for a dynamic role that contains 300k objects.
Создание динамической группы из скрипта
Public Function CCC_HostingOU_CreateDynamicGroup(objectKey As String, uidAERole As String) As String
Dim dynamicGroup As IEntity = Nothing
Dim f As ISqlFormatter = Session.SqlFormatter
Dim whereClause = String.Format("EXISTS (SELECT 1 FROM (SELECT UID_Person FROM PersonInAERole WHERE EXISTS (SELECT 1 FROM (SELECT UID_AERole FROM AERole WHERE UID_AERole = '{0}') as X WHERE X.UID_AERole = PersonInAERole.UID_AERole)) as X WHERE X.UID_Person = Person.UID_Person)", uidAERole)
dynamicGroup = Session.Source.CreateNew("DynamicGroup")
dynamicGroup.PutValue("ObjectKeyBaseTree", objectKey)
dynamicGroup.PutValue("UID_DialogTableObjectClass", "QER-T-Person")
dynamicGroup.PutValue("WhereClause", whereClause)
Using uow As IUnitOfWork = Session.StartUnitOfWork()
uow.Put(dynamicGroup)
uow.Commit()
End Using
Return dynamicGroup.GetValue("UID_DynamicGroup")
End Function
Или
Public Sub CCC_CreateOrUpdate_DynamicGroup(uid_customers As String)
' create or find dynamic group
Dim dynamicGroup As IEntity = Nothing
Dim f As ISqlFormatter = Session.SqlFormatter
' Create an ObjectKey for the customer node
Dim ObjectKeyBaseTree As DbObjectKey = New DbObjectKey("ITShopOrg", uid_customers)
' Try to load the dynamic group frm the database
If Session.Source.TryGet(
Query.From("DynamicGroup") _
.Where(f.Comparison("ObjectKeyBaseTree", ObjectKeyBaseTree.ToXmlString(), ValType.String, CompareOperator.Equal, FormatterOptions.NonUnicodeLiterals)) _
.SelectNonLobs, dynamicGroup
) Then
' A dynamic group exists for our customer node
' Just change the where clause
dynamicGroup.PutValue("whereclause", "isnull(IsInActive, 0) = 0") 'only active employes
Else
' There is no dynamic group for our customer node
' Create a new dynamic group
dynamicGroup = Session.Source.CreateNew("DynamicGroup")
' Set the values
dynamicGroup.PutValue("ObjectKeyBaseTree", ObjectKeyBaseTree.ToXmlString())
' Optimization: UID_DialogSchedule for ootb schedules never change since version 7
dynamicGroup.PutValue("UID_DialogSchedule", "QER-B78E7C59F09D487085506ED339F0257D")
' Optimization: UID_DialogTable for ootb tables never change since version 7
dynamicGroup.PutValue("UID_DialogTableObjectClass", "QER-T-Person")
dynamicGroup.PutValue("whereclause", "isnull(IsInActive, 0) = 0") 'only active employes
End If
' Saving the changed or created dynamic group
Using uow As IUnitOfWork = Session.StartUnitOfWork()
uow.Put(dynamicGroup)
uow.Commit()
End Using
End Sub