Forum Discussion
MarcusH
7 months agoValued Contributor
I think the quickest way to do this would be in a SQL query and then use LINQ to filter the result. This prints 10 records to the error log. The output could then be printed to Excel or a csv file etc
Dim SQL As New Text.StringBuilder()
' Get the Security Group info
SQL.AppendLine("Select UniqueID, Name, Description from SecGroup")
Dim dtSecGroupInfo As New DataTable
Using DbConnApp As DbConnInfo = BRApi.Database.CreateFrameworkDbConnInfo(si)
dtSecGroupInfo = BRApi.Database.ExecuteSql(DbconnApp, sql.ToString, False)
'Application DB
Dim appSQL As New Text.StringBuilder()
appSQL.AppendLine("Select ProfileName, AccessGroupUniqueID, MaintenanceGroupUniqueID ")
appSQL.AppendLine("FROM WORKFLOWPROFILEHIERARCHY")
Dim dtWorkFlow As New DataTable
Using appDbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
dtWorkFlow = BRApi.Database.ExecuteSql(appDbConnApp, appSQL.ToString, False)
' Find where SecName is used or not used
Dim query = From secGroup In dtSecGroupInfo.AsEnumerable()
Group Join accessGroup In dtWorkFlow.AsEnumerable()
On secGroup.Field(Of Guid)("UniqueID") Equals accessGroup.Field(Of Guid)("AccessGroupUniqueID")
Into AccessMatches = Group
Group Join maintenanceGroup In dtWorkFlow.AsEnumerable()
On secGroup.Field(Of Guid)("UniqueID") Equals maintenanceGroup.Field(Of Guid)("MaintenanceGroupUniqueID")
Into MaintenanceMatches = Group
Select New With {
.SecName = secGroup.Field(Of String)("Name"),
.UsedAsAccessGroup = AccessMatches.Any(),
.UsedAsMaintenanceGroup = MaintenanceMatches.Any(),
.IsUsed = AccessMatches.Any() Or MaintenanceMatches.Any()
}
' Iterate and display the results
Dim counter As Int16 = 0
For Each result In query
If result.IsUsed Then
BRAPI.ErrorLog.LogMessage(si, $"SecName: {result.SecName} is used as AccessGroup: {result.UsedAsAccessGroup}, MaintenanceGroup: {result.UsedAsMaintenanceGroup}")
Else
BRAPI.ErrorLog.LogMessage(si, $"SecName: {result.SecName} is NOT used in any workflow.")
End If
' Just show 10 records for testing
counter += 1
If counter = 10 Then Exit For
Next
End Using
End Using
Related Content
- 2 years ago
- 4 years ago
- 2 years ago