This is the thirdin third in a series of articles giving extended example of how to use the RestAPI. This article will demonstrate how to generate a report equivalent to the Version 3 "OutputUsers" view using PowerShell.
You can download the latest version of the file: OutputUsers.ps1, and simply customise the $user, $pass and $sonar values and it should run in your environment
Fields in the original report
| Field | V3 Comment | RestAPI Comment |
|---|---|---|
| Account | Account Name / User Name from the Enterprise Application | |
| (This was not used in V3) | Not implemented in V4 | |
| UserID | UserID from the application. V3 allowed Account and UserID to be distinct | In V4 this is the same as the Account |
| SoftwareName | Enterprise application name | E.g. Oracle Database Server, SQL Server, Active Directory Server, Informix |
| Module Name | (This was not used in V3) | Not implemented in V4 |
| Application ID | Identifier for the application. Used to map to OutputApplications | In V4 this is in GUID format |
| DeviceID | Identifier for the application. Used to map to OutputDevices | In V4 this is in GUID format |
| Role | User Role within the Enterprise Application | Not present for all application types |
...
Build the CSV file header row
PowerShell has a number of built in libraries for handling output to various file formats. For this example we will be saving the results in a CSV file that can be viewed directly in EXCEL or imported into other databases.
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
# Build the CSV File header row $csv = @() $row = New-Object System.Object # email and ModuleName will always be NULL. Omit if not needed for backwards compatibility $row | Add-Member -MemberType NoteProperty -Name "Account" -Value $null $row | Add-Member -MemberType NoteProperty -Name "eMail" -Value $null $row | Add-Member -MemberType NoteProperty -Name "UserID" -Value $null $row | Add-Member -MemberType NoteProperty -Name "SoftwareName" -Value $null $row | Add-Member -MemberType NoteProperty -Name "ModuleName" -Value $null $row | Add-Member -MemberType NoteProperty -Name "ApplicationID" -Value $null $row | Add-Member -MemberType NoteProperty -Name "DeviceID" -Value $null $row | Add-Member -MemberType NoteProperty -Name "Role" -Value $null |
...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
$offset = 1 # offset
$seen = 1; # first offset is 1, not 0
while ( $seen -lt $appCount)
{
$url = -join ("http://", $sonar, "/api/v1/applications/?offset=", $offset, "&fetch_size=", $fs)
$applications = Invoke-RestMethod $url -Credential $credential
$i = 1
while ($i -lt $applications.count)
{
$thisApplication = $applications[$i]
$currApp = Invoke-RestMethod $thisApplication.self -Credential $credential
$usercount = $currApp.users.count
if ($usercount -gt 0)
{
$appid = $currApp.application_id
$deviceid = $currApp.devices[0].device_id
$SoftwareName = $currApp.product.name
$k = 0
while ( $k -lt $usercount)
{
$row = New-Object System.Object
$row | Add-Member -MemberType NoteProperty -Name "Account" -Value $currApp.users[$k].user_name
$row | Add-Member -MemberType NoteProperty -Name "UserID" -Value $currApp.users[$k].user_name
$row | Add-Member -MemberType NoteProperty -Name "SoftwareName" -Value $SoftwareName
$row | Add-Member -MemberType NoteProperty -Name "ApplicationID" -Value $appid
$row | Add-Member -MemberType NoteProperty -Name "DeviceID" -Value $deviceid
$row | Add-Member -MemberType NoteProperty -Name "Role" -Value $currApp.users[$k].user_role
$csv += $row
$k = $k + 1
}
}
$i = $i + 1; # keep track for inner loop
$seen = $seen + 1; # keep track for outer loop
if ( $seen % 10 -eq 0) {
# progress indicator - display a "." every 10 devices
write-host "." -nonewline
}
}
# Finished this batch
$offset = $seen
}
write-host " Done. Saving output to OutputApplicationsOutputUsers.csv now."
# Since we always insert all the columns in the same order, we don't need to insert the code to force an ordering here unless you NEED the empty columns
$csv | Export-csv OutputUsers.csv -NoTypeInformation # We will further refine this line later |
As this report has a smaller number of columns and requires simpler logic, that's all we need to do.
| Code Block | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
#
# Output Users
# Generate OutputDevices equivalent report via RestAPI
# Version 1.00 2018-10-11 First version
#
# Change these values to suit your location
$user = "admin"
$pass = "your password"
$sonar = "your iQSonar Host"
# Optional - UPDATE THIS FOR YOUR SITE
$fs = 100 # fetch_size
# Connect to RestAPI and count number of Applications
$secpass = ConvertTo-SecureString $pass -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential($user,$secpass)
$uri = -join ("http://", $sonar, "/api/v1/applications/?offset=1&fetch_size=1")
$r = Invoke-WebRequest $uri -Credential $credential
# $r.headers has HTML headers, $r.content has text content
$appCount = $r.headers.'X-fetch-count'
# Let the user know how many devices we can see
$output = -join ( "There are: ", $appCount, " applications in total")
# Build the CSV File header row
$csv = @()
$row = New-Object System.Object
# Build the CSV File header row
$csv = @()
$row = New-Object System.Object
# email and ModuleName will always be NULL. Omit if not needed for backwards compatibility
$row | Add-Member -MemberType NoteProperty -Name "Account" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "eMail" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "UserID" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "SoftwareName" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "ModuleName" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "ApplicationID" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "DeviceID" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "Role" -Value $null
$offset = 1 # offset
$seen = 1; # first offset is 1, not 0
while ( $seen -lt $appCount)
{
$url = -join ("http://", $sonar, "/api/v1/applications/?offset=", $offset, "&fetch_size=", $fs)
$applications = Invoke-RestMethod $url -Credential $credential
$i = 1
while ($i -lt $applications.count)
{
$thisApplication = $applications[$i]
$currApp = Invoke-RestMethod $thisApplication.self -Credential $credential
$usercount = $currApp.users.count
if ($usercount -gt 0)
{
$appid = $currApp.application_id
$deviceid = $currApp.devices[0].device_id
$SoftwareName = $currApp.product.name
$k = 0
while ( $k -lt $usercount)
{
$row = New-Object System.Object
$row | Add-Member -MemberType NoteProperty -Name "Account" -Value $currApp.users[$k].user_name
$row | Add-Member -MemberType NoteProperty -Name "UserID" -Value $currApp.users[$k].user_name
$row | Add-Member -MemberType NoteProperty -Name "SoftwareName" -Value $SoftwareName
$row | Add-Member -MemberType NoteProperty -Name "ApplicationID" -Value $appid
$row | Add-Member -MemberType NoteProperty -Name "DeviceID" -Value $deviceid
$row | Add-Member -MemberType NoteProperty -Name "Role" -Value $currApp.users[$k].user_role
$csv += $row
$k = $k + 1
}
}
$i = $i + 1; # keep track for inner loop
$seen = $seen + 1; # keep track for outer loop
if ( $seen % 10 -eq 0) {
# progress indicator - display a "." every 10 devices
write-host "." -nonewline
}
}
# Finished this batch
$offset = $seen
}
write-host " Done. Saving output to OutputUsers.csv now."
$csv | Export-csv OutputUsers.csv -NoTypeInformation |
What might be useful to add?
This script duplicates the output from the Version 3 report as closely as possible.
It might be useful to include the device hostname ($currApp.devices[0].host_name) or the application name (usually Instance name for MS SQL or database name for Oracle for example, found in $currApp.name) as columns in the spreadsheet.