0
Answered

What type is $entity, how do I search, and what is Product.IdentityBroker.PowerShellValue?

Carol Wapshere 2 years ago • updated by anonymous 2 years ago 7

I'm trying to write a PowerShell adapter transformation. A couple of points about this:

- I want to be able to use a variable field name,

- I want to be able to search for another entity.

The use case is flattening Org Units. The feed is a classic parent-child feed and what I want is to add attributes OrgUnit1 .. OrgUnit9 to each Org Unit object in the adapter. This means I have to be able to loop through looking for parent Org Units in the $entities collection.

The following script does not work in IdB - but gives an idea about what I'm trying to do. It does work outside IdB simulating the $entity as a hashtable. If you could share some more info about what exactly $entity is I may be able to do better at getting this working outside of IdB in such as way as it will work inside too.

DOESN'T WORK:

foreach ($entity in $entities){
  $id = $entity["OrganisationUnitNumber"]
  $level = $entity["OrganisationUnitLevel"]
  $fieldname = "OrgUnit" + $level
  $entity[$fieldname]=$id
  $entity2 = $entity
  while ($level -ne "1")
  {
    $id = $entity2["SuperiorOrgUnitNumber"]
    $entity2 = $entities | where {$_["OrganisationUnitNumber"] -eq $id}
    $level = $entity2["OrganisationUnitLevel"]
    $fieldname = "OrgUnit" + $level
    $entity[$fieldname]=$id
  }
}
Affected Versions:
Fixed by Version:

Answer

Answer
Answered

Hi Carol,

Similarly to the PowerShell connector, you will need to call the Value property on the values to extract the raw value, e.g. $entity["OrganisationUnitLevel"].Value. Please make sure you have RC2, as there was a bug in RC1 which required calling the Value property twice (i.e. .Value.Value)

Also please note that the transformations happen during reflection, which batches changes into pages and performs the transformation once per page. This means that you will not necessarily have access to the entire entity context, and so it's unreliable whether you will find the entity you are looking for in the$entities container.

Answer
Answered

Hi Carol,

Similarly to the PowerShell connector, you will need to call the Value property on the values to extract the raw value, e.g. $entity["OrganisationUnitLevel"].Value. Please make sure you have RC2, as there was a bug in RC1 which required calling the Value property twice (i.e. .Value.Value)

Also please note that the transformations happen during reflection, which batches changes into pages and performs the transformation once per page. This means that you will not necessarily have access to the entire entity context, and so it's unreliable whether you will find the entity you are looking for in the$entities container.

So you're saying I can't realiably search for another entity? Searching for the parent orgunit (and doing it recursively) is the only way this is going to work. Any other suggestions as to how I might achieve this?

This is actually a pretty common use case - "flattening" the entire org hierarchy on the person object so you can use it in policy and group population. It would be good to get a "UNIFY best practise" solution for this.

Currently the PowerShell components provided to the transformation script don't include the full entity context. The reason for this is that other transformations can access the context via LINQ to SQL which lets them do filtering/lookup at the DB layer, not in memory, whereas the PowerShell script wouldn't have this capability and so you wouldn't get the performance you'd be hoping for anyway.

You said that this is a common use case, is there an existing solution to this problem that you could use instead? The PowerShell transform is great for small ad-hoc transformations like string manipulation, type conversions, etc., but I don't think it would be the right tool in this case.

I don't believe there is an existing generally-preferred solution. I asked on yammer a couple of months back and no one had anything new for me. I've also been looking at a SQL based approach which involves a great long query to get data out of the Entity table and put it in another table (in another DB) and then reimport it back into IdB from there. But would much prefer to handle it in the Adapter if I can.

I've now created a Powershell connector which is reading the data out of the IdB database and presenting it how I need it. Could I add a feature request that IdB handles this natively?

$ErrorActionPreference = "Stop"

. D:\Scripts\Shared\Set-LocalVariables.ps1
. D:\Scripts\Shared\FIMFunctions.ps1

$Query = @"
DECLARE @cid NVARCHAR(50) = '4C1AA662-F100-415F-A398-2B1225EE12FC';
DECLARE @orgid INT = (SELECT CollectionKeyId from [Unify.IdentityBroker].[dbo].[CollectionKey] where [Caption]='Organisation Unit Number');
DECLARE @level INT = (SELECT CollectionKeyId from [Unify.IdentityBroker].[dbo].[CollectionKey] where [Caption]='Organisation Unit Level');
DECLARE @parentid INT = (SELECT CollectionKeyId from [Unify.IdentityBroker].[dbo].[CollectionKey] where [Caption]='Superior Org Unit Number');

SELECT EntityId,
       MAX( CASE [CollectionKeyId] WHEN @level THEN StringValue ELSE '' END ) OrgUnitLevel, 
       MAX( CASE [CollectionKeyId] WHEN @orgid THEN StringValue ELSE '' END ) OrgUnitID,
       MAX( CASE [CollectionKeyId] WHEN @parentid THEN StringValue ELSE '' END ) ParentID
FROM [Unify.IdentityBroker].[dbo].[EntityValue]
WHERE [PartitionId]= @cid
GROUP BY EntityId;
"@

$rows = Invoke-SQLQuery -Server $SQLServer -Instance $SQLInstance -Database "Unify.IdentityBroker" -Query $Query

$logger.LogInformation("OrgUnit Flatten: Retrieved {0} rows from SQL query" -f $rows.count)

if ($rows.Count -gt 0)
{
    foreach ($row in $rows)
    {
        $entity = $entities.Create()
        [string]$id = $row."OrgUnitID"
        $entity["OrgUnitID"] = $id
        [string]$level = $row."OrgUnitLevel"
        $entity["OrgUnitLevel"] = $level
        [string]$fieldname = "OrgUnit" + $level
        $entity[$fieldname] = $id
        $row2 = $row
        while ($level -ne "1")
        {
            $id = $row2."ParentID"
            $row2 = $rows | where {$_."OrgUnitID" -eq $id}
            $level = $row2."OrgUnitLevel"
            $fieldname = "OrgUnit" + $level
            $entity[$fieldname] = $id
        }
        $entity.Commit()
    }
} 

Are you able to do up a data model to go with the feature request, so that it's a little more clear?

Thanks.