Home Analyst Portal

Leveraging the NEW Archive!

Brian_WinterBrian_Winter Customer Advanced IT Monkey ✭✭✭

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.



  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

    @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


    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 | Export-Csv -Path "Archive.csv" -NoTypeInformation

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

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


        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'

Sign In or Register to comment.