RestAPI OutputUsers
- Former user (Deleted)
This is the 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 |
Connect to the RestAPI
The RestAPI uses HTTP basic authentication rather than domain credentials. To write a script that extracts data from the RestAPI you will need to know the login name and password for an iQSonar user who has the "access Rest API" permission enabled. By default the admin user always has this permission. These variables will need to be configured for your site:
$user = "admin" $pass = "password" $sonar = "iQSonar Host" $secpass = ConvertTo-SecureString $pass -AsPlainText -Force $credential = New-Object System.Management.Automation.PSCredential($user,$secpass)
The first step is to determine exactly how many applications we're going to be collecting data for. This information is returned in the header of the RestAPI return value. By default a call to the devices endpoint will return a batch of 200 devices, and the user is expected to page through the list of available devices by making successive calls to this endpoint using an offset parameter to specify how many devices have already been seen. The fetch_size parameter is used to determine how many devices are returned per batch. The fastest way to determine the total number of available applications is to explicitly request the first device only. Since we want data from the headers of the result, we use the Invoke-WebRequest PowerShell cmdlet to get this information.
$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'
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.
# 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
The inner and outer loops
We need to iterate over the list of applications, fetching Fetch_Size applications per batch, then processing each application one at a time, until we have dealt with all of them. Only some application types have users and if users exist they will be in the applications/application_id endpoint, so what we need to do for each application we fetch the applications/application_id and check if it has a list of users. We add a row to the CSV file for each user if any.
Then we save the resulting CSV file. As a large dataset may take some time to process, we display a status indicator to give feedback to the user. For a more detailed breakdown on how this is developed, see the OutputDevices example article.
$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."
# 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.
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.