PowerShell - Download Campaign Data to File

Identity Security Cloud (ISC) ISC Show and Tell
, ,
1

At my company, we do certification campaigns for all our SOX apps semi-annually in Q2 and Q4. Currently, I have 28 active campaigns and 12k+ review tasks assigned across reviewers.

I needed some sort of way to export all that data at once to help understand progress of the campaigns. Yes, you can set up campaign reminder emails to automatically be sent, but sometimes it requires a real person and a “name and shame” type approach to get people to stop procrastinating.

The script below utilizes the PowerShell SDK, so you need to download and install that first.

There are two files that are generated from this script

  1. certification_summary.csv - This shows all the certification records with their status, decisions made, decisions total. It’s a nice format because you can easily manipulate it in excel and find users who haven’t started yet

  2. certification_tasks.csv - This is an output of every single certification task rolled up inside the campaign, very similar to the Campaign Status Report you can download. I use that data to plug into a spreadsheet where someone built a nice pivot table for me to see where we are from a total progress perspective across all campaigns.

Notably, we group all our campaigns by application, and the naming convention is as follows

Time period - Application Name

So for this go around, an example would be

2023 Q4 - WellView

Because of that, I used the “starts with” filter in my query parameter so I can easily download all campaigns in a given cycle.

Here’s the script below

param($campaign_name,$save_directory)

$campaigns = Get-ActiveCampaigns -Filters "name sw `"$($campaign_name)`""
$cert_tasks = @()
$certs = @()

foreach ($campaign in $campaigns) {
    Write-Host "Querying Certifications for Campaign $($campaign.name)"
    $certifications = Get-IdentityCertifications -Filters "campaign.id eq `"$($campaign.id)`""
    foreach ($certification in $certifications) {
        if ($certification.decisionsTotal -gt 0) {
            Write-Host "Querying Certification Tasks assigned to $($certification.reviewer.name) for Campaign $($campaign.name)"
            $certification_tasks = Invoke-Paginate -function Get-IdentityAccessReviewItems -InitialOffset 0 -Limit 2000 -Increment 250 -Parameters @{"id" = $certification.id }
            $cert_obj = New-Object PSObject -Property @{
                "CampaignName"   = $campaign.name
                "Reviewer"       = $certification.reviewer.name
                "Phase"          = $certification.Phase
                "DecisionsMade"  = $certification.decisionsMade
                "DecisionsTotal" = $certification.decisionsTotal
            }
            $certs += $cert_obj
    
            foreach ($task in $certification_tasks) {
                $cert_task = New-Object PSObject -Property @{
                    "CampaignName" = $campaign.name
                    "Reviewer"     = $certification.reviewer.name
                    "Access"       = $task.accessSummary.access.name
                    "AccessHolder" = $task.identitySummary.name
                    "Completed"    = $task.completed
                }
                $cert_tasks += $cert_task
            }
        }
    }
}

$certs | Select-Object CampaignName, Reviewer, Phase, DEcisionsMade, DecisionsTotal | export-csv -NoTypeInformation "$($save_directory)\certification_summary.csv"
$cert_tasks | Select-Object CampaignName, Reviewer, AccessHolder, Access, Completed | export-csv -NoTypeInformation "$($save_directory)\certification_tasks.csv"
2 Likes