Digging into SQL errors when copying a dataset

There was an interesting question over on the forum.  I figured I should follow-up with some information about the process of copying a dataset using the migration feature when creating a new dataset.  This is a relatively new feature for some organizations upgrading, so let's dive right on in!

To get things started, I opened the Enterprise Studio and clicked Create Dataset.

Then I entered the new dataset's name, ID, and RDBMS platform (dataset type)...

After clicking Next, I clicked the KwikSelect icon on the dataset connection string property. 

This displays the Data Link Properties dialog where I can enter the server name, authentication mechanism, and select the database name.  I want to configure this to point to the new database, which will end up being a duplicate of an existing dataset.  If you select SQL authentication (the use a specific user name and password option), be sure to also check "allow saving password".  

Be sure to point to the new database!  The source content will be bulk loaded into this database.

After configuring the data link properties dialog, I clicked OK and was returned to the new dataset connection dialog.  I changed the command timeout to 300.  The default value equates to 30 seconds, a value far too low that will just lead to timeout errors for users. 

I clicked Next to  move onto the Options page of the create new dataset wizard.  It prompted me for a working path for bulk load.  I pointed it to a share on my workgroup server.  Then I clicked Next (skipping the IDOL index staging field).

I provided a path for my new document store and then clicked Next.

On the Storage dialog I simply clicked Next and did not change any of the options.  You would only change these options if you have created additional files within the database.  Contact your database administrator for an explanation and review of these settings.

Within the initialization page of the wizard I must select the Migrate radio button, which then enabled me to select my source dataset and migration style.  It's best to select a basic setup data migration style when promoting from development (DEV) to user acceptance testing (UAT) or UAT to production (PROD).  Otherwise I just pick Entire Dataset (you can always purge records or locations later).  Note that I have not selected to support Unicode characters, but you may need to (discuss with your DBA).  

I clicked Next one last time and am presented with the run parameters page of the wizard.  I clicked Finish without changing any options.

Then I had to click OK on this warning dialog.

Finally, I can click Start to kick this copy off!

It completed step one (of four) and then bombed with this error message...

Well the error is saying there's an access denied error on a file, so I open windows explorer and look at the file....

The existence of a non-zero file tells me that there were appropriate permissions to create the file.  Therefore the issue must be with permissions reading the file, which makes sense given the error states "the file ... could not be opened". 

Since I'm using Windows as an authentication mechanism the SQL Server must send my credentials for validation.  This fails because SQL Server cannot transmit my credentials to the remote share (this would be a double-hop situation).  If I change my bulk load share to one created on the SQL Server then my credentials won't need to be transmitted and the process works correctly.   If I changed the identity of the SQL Server service away from the virtual network service account (shown below) and to a defined domain user, I could enable constrained delegation.  That would allow the double-hop of credentials to work without failure.

SQL Server is set to use a virtual network service account

If I cannot create a share off the SQL server then I must switch from Windows authentication to SQL authentication.  That would also require that I change the identity of SQL Server from the virtual network service (shown in the picture above) to a domain account.  Additionally, I would need to configure constrained delegation on that new account.  The SQL authentication route is significantly more complicated to configure.  Therefore the best option for me is to use a local share (local to the SQL Server since Windows authentication fails without constrained delegation).

My quick fix is to re-do all the steps above but to use "\\apps\bulkload" as my work path for bulk load...

Once I did that my process works flawlessly...

I've now got two datasets defined.  Next I should configure events, IDOL, and any other settings unique to the new dataset.

Generating Statistics and Uploading to Power BI

Another question over on the forum!  This time about generating statistics about how the system is being used.  I thought I might share something I played with earlier.

First I created a powershell script that generates a CSV file.  To add a row to the file, I search for the number of records registered every day between 1/1/2010 and today.  I track the registration counts separately per record type; and I don't track a day where 0 were registered for a given type.

My results look like this...

Once my extraction of metrics was completed, I created a free account on Power BI and uploaded the 20,000 row CSV file. I then created a few reports....

Here's one that includes filters the dataset to the previous two years and then displays the results in 3 different ways.  I can see year-over-year comparisons highlighting the growth of electronic records.  I could easily add ownership/business unit as an export metric too.

I don't have to have a report either.  They have an interactive "Ask a question" feature that lets you click you way into selecting from the dataset.

If you like this solution, give it a whirl!  Here's the powershell script I used to generate these metrics.

Add-Type -Path "D:\Program Files\Hewlett Packard Enterprise\Content Manager\HP.HPTRIM.SDK.dll"
$db = New-Object HP.HPTRIM.SDK.Database
$db.Connect
 
$startDate = (Get-Date -Year 2010 -Month 1 -Day 1)
$endDate = (Get-Date -Year 2017 -Month 12 -Day 31)
$totalDays = (New-TimeSpan -Start $startDate -End $endDate).Days
 
$metrics=$null
$metrics=@()
$x = 0
for($date = $startDate; $date -lt $endDate; $date = $date.AddDays(1))
{
    $x++
    $dateString = ($date.ToShortDateString()) 
    Write-Progress -id 1 -Activity "Gathering Metrics" -Status "$($dateString)" -PercentComplete (($x/$totalDays)*100) 
    $recordTypes = New-Object HP.HPTRIM.SDK.TrimMainObjectSearch -ArgumentList $db, RecordType
    $recordTypes.SearchString = "all"
   
    $y = 0
    foreach ( $recordType in $recordTypes ) 
    {
        $y++
        $recordsOfType = New-Object HP.HPTRIM.SDK.TrimMainObjectSearch -ArgumentList $db, Record
        $recordsOfType.SearchString = "registeredOn:$($dateString) type:$($recordType.Uri)"
 
        if ( $recordsOfType.Count -gt 0 ) {
            $metric = new-object PSObject 
            $metric | add-member -membertype NoteProperty -name "Record Type Name" -Value $recordType.Name
            $metric | add-member -membertype NoteProperty -name "Date" -Value $dateString
            $metric | add-member -membertype NoteProperty -name "Count" -Value $recordsOfType.Count
            $metrics += $metric
        }
    }
}
$metrics | Export-Csv "E:\temp\metrics.csv" -NoTypeInformation

Once you've executed the script you login to Power BI and create a new dataset.  

Then I picked Files and selected the output from the Powershell

Then I created a new report and played with the interface until I got the dashboard in the original screenshot.  Once I was done I went back and updated my powershell script so that I can run it each day and it posts to the REST service.  I then scheduled the powershell to run daily and now have a dashboard with new metrics each day!.

If you want a script that does exactly what the original poster did, here's a script for that.  The approach is different, as it processes all system activity.  The first script simply checks each day for specific values.

Add-Type -Path "D:\Program Files\Hewlett Packard Enterprise\Content Manager\HP.HPTRIM.SDK.dll"
$db = New-Object HP.HPTRIM.SDK.Database
$db.Connect
$events = New-Object HP.HPTRIM.SDK.TrimMainObjectSearch -ArgumentList $db, History
$events.SearchString = "objectType:Record date:this year"
$history = $events | where Record 
 
$metrics = @{}
foreach ( $event in $history ) 
{
    $recTypeName = ([HP.HPTRIM.SDK.History]$event).Record.RecordType.Name
    if ( $metrics.Contains($recTypeName) ) 
    {
        $metrics[$recTypeName]++
    }
    else 
    {
        $metrics.Add($recTypeName,1)
    }
}
$metrics| Format-Table -AutoSize

I didn't really have any requirements so the output for this script is light at the moment.  The metrics are still interesting though.  The values represent how many actions have been performed by record type for the current year.  So there have been 40 "events" on Request for Information records so far this year.

Security Breaches 101

When I provide training for Content Manager I always start with security.  It doesn't matter if security is a major concern for the organization or not.  The main reason I do this, besides just needing to explain how it works, is to minimize the occurrences of security breaches.  A security breach is a system flag indicating that the objects associated with a record are not in alignment with the security paradigm.  

There are two places that you can define the behavior of security breaches within the software: the security tab of the system options and on the properties of a record type.  If I use the out-of-the-box configuration of Content Manager, the former is set to "Display Warning" and the latter is set to "Ignore".  For the moment I'll focus on the first.

Here's the option I'm talking about:

System Options for Movement policy

If you press F1 you'll see a description for the setting, shown below.

“When changing Assignee, Home or Owner for a Record to a less secure Location - set the method of Location change when a selected record has a lower security classification than the Location. See also Security breaches.

The options are
•Ignore
•Display Warning - default
•Prevent”

If I lookup "Security breaches", as it recommends, I get this description:

“This function enables a HPE Content Manager administrator to view and print occurrences of security breaches that may have occurred.

Security breaches appear in an Historical Events dialog box with all other events that have occurred, for example, Location and container changes and movement history.

By default, the security breaches function will only show security breaches for the current day.

For events to be logged, the relevant event must be selected in the Audit tab of the Record Type.”

The end result is a pop-up message like this:

Funny!  I have the "Display warning" option selected but it still won't let me save it.  I feel bad for developers at times... having to maintain this complex web of features and then having silly bugs like this.  In a non-buggy build I would be able to click OK and continue on saving the record. For now I'll work around the bug by assigning it to myself and then reassigning it to Elmer.

Once I've done that I can go check out the online audit log.  It will show me all the activity for today, including the right-most column that indicates if a security breach was detected.  The screenshot below shows a series of assignments.  Note the assignments to Elmer are breaches but the one to myself is not.

To resolve this I need to find out what's missing from Elmer's profile.  Now you might think I could use the View Rights feature whilst impersonating Elmer, but you'd be wrong!  As shown below, the UI is indicating the security profile is met by Elmer... but I suspect this is another bug

Instead, I look at the security profile of the record.  I see that it has a security caveat of "HR"...

Now if I look at my location structure I can see the problem...

When I on-boarded Elmer I didn't give him a security profile.  So it makes sense that assigning something to him would breach the security of the record.  Elmer is completely unaware because he can't even access the record.  So this notification is the administrator's opportunity to fix the problem.  To resolve it I need to add the HR caveat to Elmer's profile.

The situation happens anytime you've elected to use a mixture of security levels and caveats.  The best example is a secured facility's mail room, where they may be indexing incoming correspondence and assigning it to the wrong person accidentally.  For that scenario they should be prevented from continuing, because we can't deliver the item as we register it officially.  In other environments it's not the end of the world... it's something to manage.  

If you take a look at this question over on the forum, you'll see a common situation.  Imagine how much time he's going to have to spend fixing this problem!  For a 50 user or less implementation it's a quick task; but for a 5,000 user site, good luck.  He might want to engage a developer within his organization or a knowledgeable consultant.

I thought I'd be creative and write a powershell script to sort this out for him, but no such luck.  It executes fine but doesn't actually save the updated security profile.  Maybe a developer will read this and fix it! :)

Add-Type -Path "D:\Program Files\Hewlett Packard Enterprise\Content Manager\HP.HPTRIM.SDK.dll"
$db = New-Object HP.HPTRIM.SDK.Database
$db.Connect
$breaches = New-Object HP.HPTRIM.SDK.TrimMainObjectSearch -ArgumentList $db, History
$breaches.SearchString = "breach"
foreach ( $breach in $breaches ) 
{
    $location = ([HP.HPTRIM.SDK.History]$breach).MovementLocation
    $unit = $location.Unit
    $recordProfile = ([HP.HPTRIM.SDK.History]$breach).Record.SecurityProfile
    if ( $unit -ne $null -and $unit.SecurityProfile.CanAccess($recordProfile) ) {
        $location.SecurityProfile.UpgradeTo($unit.SecurityProfile)
        $location.Save()
        Write-Host "Updating $($location.FullFormattedName) to security profile of $($unit.FullFormattedName)"
    }
}

Executing this will at least give me a report of all the users that should be updated.  The example below lists Elmer Fudd twice, exactly as shown in the online audit log.