Leveraging the NEW Archive!

Brian_Winter

Do we have an example/demo of how to use the Archive to lookup and do some basic activities such as print or download old work items? We use Power BI extensively but we're also open to other technologies.

End Users and Analyst want to be able to look up old WI's to be able to see Resolution, Action Logs, download Attachments, use the information to copy/past into a new form, etc. Leadership is looking to create multi-year metrics and trend reports.

TIA!

Justin_Workman

@Brian_Winter trends would be hard to pull due to having to parse the JSON from the archiver essentially on the fly. Lookups for single items would be a little easier however.

Here's some Powershell similar to what I wrote on the Open Floor that will export a csv of the last 10 modified records in the Archiver:

$query = @"

SELECT TOP 10 o.ObjectDefinition, p.Projection

FROM Archive_Managed_Object o

JOIN Archive_Object_Projection p

ON p.ObjectId = o.ObjectId

WHERE o.ManagedTypeId = 'a604b942-4c7b-2fb2-28dc-61dc6f465c68'

ORDER BY o.LastModified DESC

"@

$result = Invoke-Sqlcmd -ServerInstance $sqlserver -Database "CiresonAnalytics" -Query $query

$output = New-Object System.Collections.ArrayList

read-host

foreach ($r in $result) {

    $objDef = $r.ObjectDefinition | ConvertFrom-Json

    $projDef = $r.Projection | ConvertFrom-Json

   

    $row = [PSCustomObject]@{

        WorkItemId = $objDef.Name

        Title = $objDef.Title

        AffectedUser = $projDef."WorkItemAffectedUser__dff9be66-38b0-b6d6-6144-a412a3ebd4ce".Value.Name

        AssignedUser = $projDef."WorkItemAssignedToUser__15e577a3-6bf9-6713-4eac-ba5a5b7c4722".Value.Name

        CreatedDate = $objDef.CreatedDate

    }

    $output.add($row)

}

$output | Export-Csv -Path "Archive.csv" -NoTypeInformation

Justin_Workman

Here's a straight SQL lookup that parses the JSON in SQL on the fly.

SELECT

    JSON_VALUE(o.ObjectDefinition, '$.Name') 'Name'

,   JSON_VALUE(o.ObjectDefinition, '$.Title') 'Title'

,   JSON_VALUE(u.ObjectDefinition, '$.DisplayName' ) 'AssignedUser'

,   JSON_VALUE(o.ObjectDefinition, '$.CreatedDate') 'CreatedDate'

FROM CiresonAnalyticsDev.dbo.Archive_Managed_Object o

JOIN CiresonAnalyticsDev.dbo.Archive_Object_Projection p

on p.ObjectId = o.ObjectId

LEFT JOIN CiresonAnalyticsDev.dbo.Archive_Managed_Object u

on JSON_VALUE(p.Projection, '$."WorkItemAssignedToUser__15e577a3-6bf9-6713-4eac-ba5a5b7c4722".Value.BaseId') = u.ObjectId

WHERE JSON_VALUE(o.ObjectDefinition, '$.Name') = 'IR14360'