Looking for a way to report Workflow Profile properties/attributes

Question

Just wondering if anyone out there has figured out a way to report the attributes/properties of a Workflow profile. What I'm actually trying to do as part of a security overhaul is determine WHERE a particular security group is actually being used.
I am using the XFGetMemberProperty() function to pull security for all of my dimension members but I need a solution to pull the security groups used in each of our FPA workflows.
There is no built in security dashboard report to provide this and the best advice I've received so far is to download the application and search for the security groups there. That's one way, but we literally have dozens of security groups set up so it'll be a long and manual effort.
Would love to hear from anyone who's found a better way to do this. Thanks

T_Kress · Answer

I do not know of anything pre-built for which provides what you are asking.&nbsp;
Security groups for workflows exist in the application database tables:

WorkflowProfileHierarchy
WorkflowProfileAttributes

You can join those 2 application tables via their security group IDs to the framework table:

SecGroup

The tricky part is that security groups on workflow profiles can vary by scenario type.&nbsp; So you have to do several JOINS in SQL to get all the information in one query.&nbsp;
Another tricky aspect with Workflow profiles is that there are several different types of security groups that can be assigned:

Access
Maintenance
Execution
Certification
Journal Process
Journal Approval
Journal Post

I have done this on my local machine and below is the SQL that works on my local machine.&nbsp; But in a customer environment, you cannot do JOINS across application and framework database tables as that is now allowed in SQL (not an OS constraint but an Azure Cloud constraint I beileve).
So you would need to modify the SQL below to achieve what you want but it is a starting point.&nbsp; You can start with a data adaptor and then dump those results in to a dashboard once working.
Good luck!
select [Workflow Profile], [Access Group], [Maintenance Group], [Scenario Type],max([Execution Group]) [Execution Group],max([Certification Group]) [Certification Group],max([Journal Process Group])[Journal Process Group],max([Journal Approval Group])[Journal Approval Group],max([Journal Post Group])[Journal Post Group]
from (SELECT Distinct
b.[ProfileName] as [Workflow Profile],
SecA.[Name] as [Access Group],
SecM.[Name] as [Maintenance Group],
CASE 
 WHEN Wf.[ScenarioTypeID] = '-1' THEN '(Default)'
 WHEN Wf.[ScenarioTypeID] = '0' THEN 'Actual'
 WHEN Wf.[ScenarioTypeID] = '1' THEN 'Budget'
 WHEN Wf.[ScenarioTypeID] = '10' THEN 'Administration'
 WHEN Wf.[ScenarioTypeID] = '101' THEN 'ScenarioType1'
 WHEN Wf.[ScenarioTypeID] = '102' THEN 'ScenarioType2'
 WHEN Wf.[ScenarioTypeID] = '103' THEN 'ScenarioType3'
 WHEN Wf.[ScenarioTypeID] = '104' THEN 'ScenarioType4'
 WHEN Wf.[ScenarioTypeID] = '105' THEN 'ScenarioType5'
 WHEN Wf.[ScenarioTypeID] = '106' THEN 'ScenarioType6'
 WHEN Wf.[ScenarioTypeID] = '107' THEN 'ScenarioType7'
 WHEN Wf.[ScenarioTypeID] = '108' THEN 'ScenarioType8'
 WHEN Wf.[ScenarioTypeID] = '11' THEN 'Control'
 WHEN Wf.[ScenarioTypeID] = '12' THEN 'LongTerm'
 WHEN Wf.[ScenarioTypeID] = '13' THEN 'Operational'
 WHEN Wf.[ScenarioTypeID] = '14' THEN 'Sustainability'
 WHEN Wf.[ScenarioTypeID] = '15' THEN 'Target'
 WHEN Wf.[ScenarioTypeID] = '2' THEN 'Flash'
 WHEN Wf.[ScenarioTypeID] = '3' THEN 'Forecast'
 WHEN Wf.[ScenarioTypeID] = '4' THEN 'FXModel'
 WHEN Wf.[ScenarioTypeID] = '5' THEN 'History'
 WHEN Wf.[ScenarioTypeID] = '6' THEN 'Model'
 WHEN Wf.[ScenarioTypeID] = '7' THEN 'Plan'
 WHEN Wf.[ScenarioTypeID] = '8' THEN 'Tax'
 WHEN Wf.[ScenarioTypeID] = '9' THEN 'Variance'
END as [Scenario Type],
SecWf.[Name] as [Execution Group],
SecWf1.[Name] as [Certification Group],
CASE 
  WHEN b.[ProfileName] NOT LIKE '%.Adj' THEN 'N/A'
  WHEN SecWf2.[Name] IS NULL THEN SecEWf2.[Name]
  ELSE SecWf2.[Name]
END as [Journal Process Group],
CASE 
  WHEN b.[ProfileName] NOT LIKE '%.Adj' THEN 'N/A'
  WHEN SecWf3.[Name] IS NULL THEN SecEWf3.[Name]
  ELSE SecWf3.[Name]
END as [Journal Approval Group],
CASE 
 WHEN b.[ProfileName] NOT LIKE '%.Adj' THEN 'N/A'
  WHEN SecWf4.[Name] IS NULL THEN SecEWf4.[Name]
  ELSE SecWf4.[Name]
END as [Journal Post Group]

FROM [dbo].[WorkflowProfileHierarchy] b
LEFT JOIN [OneStream_Framework].[dbo].[SecGroup] SecA 
ON b.[AccessGroupUniqueID] = SecA.[UniqueID]
LEFT JOIN [OneStream_Framework].[dbo].[SecGroup] SecM 
ON b.[MaintenanceGroupUniqueID] = SecM.[UniqueID]
LEFT JOIN [OneStream_Framework].[dbo].[SecExclGroup] SecEA 
ON b.[AccessGroupUniqueID] = SecEA.[UniqueID]
LEFT JOIN [OneStream_Framework].[dbo].[SecExclGroup] SecEM 
ON b.[MaintenanceGroupUniqueID] = SecEM.[UniqueID]

LEFT JOIN [dbo].[WorkflowProfileAttributes] Wf 
ON b.[ProfileKey] = Wf.[ProfileKey] 
AND wf.[AttributeIndex] in ('1250','16100','6100','6200','6300')

LEFT JOIN [OneStream_Framework].[dbo].[SecGroup] SecWF 
ON Wf.[ProfileAttributeValue] = convert(nvarchar(MAX), SecWF.[UniqueID]) 
AND wf.[AttributeIndex]= '1250'
LEFT JOIN [OneStream_Framework].[dbo].[SecExclGroup] SecEWF 
ON Wf.[ProfileAttributeValue] = convert(nvarchar(MAX), SecEWF.[UniqueID]) 
AND wf.[AttributeIndex]= '1250'

LEFT JOIN [OneStream_Framework].[dbo].[SecGroup] SecWF1 
ON Wf.[ProfileAttributeValue] = convert(nvarchar(MAX), SecWF1.[UniqueID]) 
AND wf.[AttributeIndex]= '16100'
LEFT JOIN [OneStream_Framework].[dbo].[SecExclGroup] SecEWF1 
ON Wf.[ProfileAttributeValue] = convert(nvarchar(MAX), SecEWF1.[UniqueID]) 
AND wf.[AttributeIndex]= '16100'

LEFT JOIN [OneStream_Framework].[dbo].[SecGroup] SecWF2 
ON Wf.[ProfileAttributeValue] = convert(nvarchar(MAX), SecWF2.[UniqueID]) 
AND wf.[AttributeIndex]= '6100'
LEFT JOIN [OneStream_Framework].[dbo].[SecExclGroup] SecEWF2 
ON Wf.[ProfileAttributeValue] = convert(nvarchar(MAX), SecEWF2.[UniqueID]) 
AND wf.[AttributeIndex]= '6100'

LEFT JOIN [OneStream_Framework].[dbo].[SecGroup] SecWF3 
ON Wf.[ProfileAttributeValue] = convert(nvarchar(MAX), SecWF3.[UniqueID]) 
AND wf.[AttributeIndex]= '6200'
LEFT JOIN [OneStream_Framework].[dbo].[SecExclGroup] SecEWF3 
ON Wf.[ProfileAttributeValue] = convert(nvarchar(MAX), SecEWF3.[UniqueID]) 
AND wf.[AttributeIndex]= '6200'

LEFT JOIN [OneStream_Framework].[dbo].[SecGroup] SecWF4 
ON Wf.[ProfileAttributeValue] = convert(nvarchar(MAX), SecWF4.[UniqueID]) 
AND wf.[AttributeIndex]= '6300'
LEFT JOIN [OneStream_Framework].[dbo].[SecExclGroup] SecEWF4 
ON Wf.[ProfileAttributeValue] = convert(nvarchar(MAX), SecEWF4.[UniqueID])   
AND wf.[AttributeIndex]= '6300'

WHERE b.[ProfileName] NOT IN ('(Default)','(Default).Adj','(Default).Forms','(Default).Import') ) x

group by [Workflow Profile], [Access Group], [Maintenance Group], [Scenario Type]
&nbsp;

MarcusH · Answer

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&nbsp;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
    &nbsp;&nbsp;

Forum Discussion

Looking for a way to report Workflow Profile properties/attributes

Related Content

Workflow Profiles

Looking for debugging advice

Workflow Templates

Cubeview looks different for 2 user with same access

Sample Workflow Upload Template