...

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
eMail	(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:

...

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

language	powershell
theme	Midnight
title	Define columns

# 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

...

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 only add a row to the CSV file if there are usersfor 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.

Code Block

language	powershell
theme	Midnight
title	Inner and Outer loops

$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)
	{
    		$GotUsers = 0;

		# Process $applications[$i]
		# Add a row to the CSV file for each user if we have one or more users
		# so for this version the CSV update goes in the innermost loop		    $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 OutputApplicationsOutputUsers.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

language	powershell
theme	Midnight
title	Completed Script
collapse	true

#
# 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.

Versions Compared

Old Version 1

New Version Current

Key

Fields in the original report

Connect to the RestAPI

Build the CSV file header row

What might be useful to add?

Page Comparison

Versions Compared

Old Version 1

New Version Current

Key

Fields in the original report

Connect to the RestAPI

Build the CSV file header row

What might be useful to add?