This is the second 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 "OutputApplications" view using PowerShell. For a shorter example that returns a simpler dataset, you can see the article Use RestAPI and PHP to generate a list of Applications.

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	OutputApplications fields required

# Build the CSV File header row
$csv = @()
$row = New-Object System.Object

$row | Add-Member -MemberType NoteProperty -Name "Hostname" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "FQDN" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "SoftwareName" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "SoftwareVersion" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "SoftwareVendor" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "SoftwareEdition" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "InstanceIdentifier" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "ClusterInformation" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "UserCount" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "LastScanDate" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "Location" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "Language" -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 "DNSHostname" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "DNSFQDN" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "Evidence" -Value $null

...

Process each application

language

Code Block

powershell
theme	Midnight
title	Inner loop - process the application
# # Incomplete Code # # Process the $applications[$i] $thisApplication = $applications[$i] $currDevice = Invoke-RestMethod $thisApplication.self -Credential $credential $row = New-Object System.Object # Hostname, FQDN and DeviceID are defined by the devices subsection if present. if ( $thisApplication.devices.count -eq 0) { # If we have no devices, we do not have this info $row \| Add-Member -MemberType NoteProperty -Name "Hostname" -Value $null $row \| Add-Member -MemberType NoteProperty -Name "FQDN" -Value $null $row \| Add-Member -MemberType NoteProperty -Name "DeviceID" -Value $null $row \| Add-Member -MemberType NoteProperty -Name "DNSHostname" -Value $null $row \| Add-Member -MemberType NoteProperty -Name "DNSFQDN" -Value $null } else { # one or more devices. So we will list the details for devices[0] - other devices will get listed in the cluster info section $row \| Add-Member -MemberType NoteProperty -Name "Hostname" -Value $thisApplication.devices[0].host_name $row \| Add-Member -MemberType NoteProperty -Name "DeviceID" -Value $thisApplication.devices[0].device_id $row \| Add-Member -MemberType NoteProperty -Name "DNSHostname" -Value $thisApplication.devices[0].host_name $k=0 while ($k -lt $thisApplication.devices[0].qualified_name.count) { if ($thisApplication.devices[0].qualified_name[$k].name_type -eq "DNSFQDN") { $row \| Add-Member -MemberType NoteProperty -Name "FQDN" -Value $null $row \| Add-Member -MemberType NoteProperty -Name "DNSFQDN" -Value $null } $k = $k + 1 } } if () # Device name was complicated. Software details are more simple $row \| Add-Member -MemberType NoteProperty -Name "SoftwareName" -Value $thisApplication.product.name $row \| Add-Member -MemberType NoteProperty -Name "SoftwareVersion" -Value $thisApplication.version $row \| Add-Member -MemberType NoteProperty -Name "SoftwareVendor" -Value $thisApplication.product.vendor $row \| Add-Member -MemberType NoteProperty -Name "SoftwareEdition" -Value $thisApplication.edition $row \| Add-Member -MemberType NoteProperty -Name "InstanceIdentifier" -Value $thisApplication.name # UserCount is optional if ( (!test-var variable:\$currDevice.users).count -gt 0 ) { $row \| Add-Member -MemberType NoteProperty -Name "UserCount" -Value $currDevice.users.count } $row \| Add-Member -MemberType NoteProperty -Name "LastScanDate" -Value $thisApplication.last_scan $row \| Add-Member -MemberType NoteProperty -Name "ApplicationID" -Value $thisApplication.application_id # Deal with clusters -- this gets complicated, so break it out into its own section

...

Cluster information

Different types of clusters are reported in the RestAPI in different ways, so the code to populate this field is rather complex.

A VMWare cluster reports ESX Nodes and ESX Clusters as distinct entries in the Applications output.
- ESX Node has "product.name" set to "VMware VSphere Hypervisor",
  The parent_cluster information (if present) points to the cluster this node is part of.
- ESX Cluster entry has "type" set to "VMWare Cluster", no edition, "name" is name of cluster
  Links to cluster nodes, link to cluster parent (self), node id given, need to generate and follow link to device ID for node names
Veritas Cluster Server,
Links to clusters gives list of nodes, same as in devices. node names in devices.
Windows Server Cluster
Links to two or more devices
No "cluster" or "parent_cluster" entry
Oracle Database Cluster Server
Multiple devices
No "clusters" or "parent_cluster" entry
SQL Server Cluster
No cluster info available
Websphere AS Cluster
Multiple devices
No "clusters" or "parent_cluster" entry

...

language	powershell
theme	Midnight
title	Cluster Information code

...

Getting details for a VMWare cluster involves a further level of redirection and an additional set of RestAPI calls since we're given the unique identifier for the node, not the host_name, so we need to call the API devices/device_id endpoint to get the node_name

Code Block

language	powershell
theme	Midnight
title	Cluster Information code

		# Deal with clusters -- this gets hairy, finish it tomorrow
complex
		$cluster=""
		if  ($thisApplication.product.name -eq "VMware Cluster")
		{
			# VMWare cluster - will need to generate and follow links to nodes to get device names
			$cluster 		}
		elseif= -join ("Cluster: (", $thisApplication.productparent_cluster.name, -eq 'Oracle Database Cluster Server' ) -or " Nodes: ");
			$k=0
				while ($k -lt  $thisApplication.productclusters.name -eq 'WebSphere AS Cluster')            -or count)
			{
				$url2 = -join ("http://", $sonar, "/api/v1/devices/", $thisApplication.clusters[$k].node_id)
				$node = Invoke-RestMethod $url2 -Credential $credential
				
				$cluster = -join($cluster, $thisApplication$node.product.host_name -eq 'Windows Server Clustering')       -or
				  ( $thisApplication.product.name -eq 'Veritas Cluster Server')
				if ($k -lt $thisApplication.clusters.count)
				){
		{
			# Getmore listto of node names from $thisApplication -> devices
do
					$cluster = -join ("Cluster: ", $thisApplication.name, " Nodes: ");
$cluster, "; ")
				}
				$k =0 			while ($k -lt+ 1
$thisApplication.devices.count)
			{}
		}
		$clusterelseif =( -join ($cluster, $thisApplication.devices[$k].host_name)
				if ($k -lt $thisApplication.devices.count)
				{
					# more to do
					$cluster = -join ($cluster, "; ")
				}
				$k = $k + 1
			}
		}

Getting details for a VMWare cluster involves a further level of redirection and an additional set of RestAPI calls since we're given the unique identifier for the node, not the host_name, so we need to call the API devices/device_id endpoint to get the node_name

Code Block

language	powershell
theme	Midnight
title	Get VMWare nodes

		if  ($thisApplication.product.name -eq "VMware Cluster")
		{
			# VMWare cluster - will need to generate and follow links to nodes to get device names
			$cluster = -join ("Cluster: ", $thisApplication.name, " Nodes: ");
			$k=0
			while ($k -lt  $thisApplication.clusters.count)
			{
				$url2 = = -join ("http://", $sonar, "/api/v1/devices/", $thisApplication.clusters[$k].node_id)
				$node = Invoke-RestMethod $url2 -Credential $credential
				
				$cluster = -join($cluster, $node.host_name)
				if ($k -lt $thisApplication.clusters.count)
				{
					# more to do
					$cluster = -join ($cluster, "; ")
				}
				$k = $k + 1
			}
		}product.name -eq 'Oracle Database Cluster Server' ) -or 
				 ( $thisApplication.product.name -eq 'WebSphere AS Cluster')            -or 
				 ( $thisApplication.product.name -eq 'Windows Server Clustering')       -or
				 ( $thisApplication.product.name -eq 'Veritas Cluster Server')
				)
		{
			# Get list of node names from $thisApplication -> devices
			$cluster = -join ("Cluster: ", $thisApplication.name, " Nodes: ");
			
			$k=0
			while ($k -lt  $thisApplication.devices.count)
			{
				$cluster = -join ($cluster, $thisApplication.devices[$k].host_name)
				if ($k -lt $thisApplication.devices.count)
				{
					# more to do
					$cluster = -join ($cluster, "; ")
				}
				$k = $k + 1
			}
		}
		$row | Add-Member -MemberType NoteProperty -Name "ClusterInformation" -Value $cluster

Write the output with the columns in the correct order

PowerShell will continue the statement if the line ends with obviously incomplete syntax, as well as with the backtick escape method used in the previous example. This looks "cleaner"

Code Block

language	powershell
theme	Midnight
title	Oitput the CSV

write-host " Done. Saving output to OutputApplications.csv now."

$csv | 
    Select-Object Hostname, FQDN, SoftwareName, SoftwareVersion, SoftwareVendor, SoftwareEdition, 
	              InstanceIdentifier, ClusterInformation, UserCount, LastScanDate, Language,
				  ApplicationID, DeviceID, DNSHostname, DNSFQDN, Evidence |
    Export-csv OutputApplications.csv -NoTypeInformation

Finished Script

Code Block

language	powershell
theme	Midnight
title	Finished Script
linenumbers	true

#
# Change these values to suit your location
$user    = "admin"
$pass    = "password"
$sonar	 = "iQSonar Server"
# 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

$row | Add-Member -MemberType NoteProperty -Name "Hostname" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "FQDN" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "SoftwareName" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "SoftwareVersion" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "SoftwareVendor" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "SoftwareEdition" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "InstanceIdentifier" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "ClusterInformation" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "UserCount" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "LastScanDate" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "Location" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "Language" -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 "DNSHostname" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "DNSFQDN" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "Evidence" -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]
		$currDevice = Invoke-RestMethod $thisApplication.self -Credential $credential
		
		$row = New-Object System.Object
		
		# Hostname, FQDN and DeviceID are defined by the devices subsection if present. 
		
		if ( $thisApplication.devices.count -eq 0)
		{
			# If we have no devices, we do not have this info
			$row | Add-Member -MemberType NoteProperty -Name "Hostname" -Value $null
			$row | Add-Member -MemberType NoteProperty -Name "FQDN" -Value $null
			$row | Add-Member -MemberType NoteProperty -Name "DeviceID" -Value $null
			$row | Add-Member -MemberType NoteProperty -Name "DNSHostname" -Value $null
			$row | Add-Member -MemberType NoteProperty -Name "DNSFQDN" -Value $null
		}
		else
		{
			# one or more devices. So we will list the details for devices[0] - other devices will get listed in the cluster info section
			$row | Add-Member -MemberType NoteProperty -Name "Hostname" -Value $thisApplication.devices[0].host_name			
			$row | Add-Member -MemberType NoteProperty -Name "DeviceID" -Value $thisApplication.devices[0].device_id
			$row | Add-Member -MemberType NoteProperty -Name "DNSHostname" -Value $thisApplication.devices[0].host_name
			$k=0
			while ($k -lt $thisApplication.devices[0].qualified_name.count)
			{
				if ($thisApplication.devices[0].qualified_name[$k].name_type -eq "DNSFQDN")
				{
					$row | Add-Member -MemberType NoteProperty -Name "FQDN" -Value $null
					$row | Add-Member -MemberType NoteProperty -Name "DNSFQDN" -Value $null
				}
				$k = $k + 1
			}
		}

		# Device name was complicated. Software details are more simple
		$row | Add-Member -MemberType NoteProperty -Name "SoftwareName" -Value $thisApplication.product.name
		$row | Add-Member -MemberType NoteProperty -Name "SoftwareVersion" -Value $thisApplication.version
		$row | Add-Member -MemberType NoteProperty -Name "SoftwareVendor" -Value $thisApplication.product.vendor
		$row | Add-Member -MemberType NoteProperty -Name "SoftwareEdition" -Value $thisApplication.edition
		$row | Add-Member -MemberType NoteProperty -Name "InstanceIdentifier" -Value $thisApplication.name
		# UserCount is optional
		if ( $currDevice.users.count -gt 0 )
		{
			$row | Add-Member -MemberType NoteProperty -Name "UserCount" -Value $currDevice.users.count
		}
		$row | Add-Member -MemberType NoteProperty -Name "LastScanDate" -Value $thisApplication.last_scan
		$row | Add-Member -MemberType NoteProperty -Name "ApplicationID" -Value $thisApplication.application_id
		
		# Deal with clusters -- this gets complex
		$cluster=""
		if  ($thisApplication.product.name -eq "VMware Cluster")
		{
			# VMWare cluster - will need to generate and follow links to nodes to get device names
			$cluster = -join ("Cluster: ", $thisApplication.parent_cluster.name, " Nodes: ");
			$k=0
			while ($k -lt  $thisApplication.clusters.count)
			{
				$url2 = -join ("http://", $sonar, "/api/v1/devices/", $thisApplication.clusters[$k].node_id)
				$node = Invoke-RestMethod $url2 -Credential $credential
				
				$cluster = -join($cluster, $node.host_name)
				if ($k -lt $thisApplication.clusters.count)
				{
					# more to do
					$cluster = -join ($cluster, "; ")
				}
				$k = $k + 1
			}
		}
		elseif ( ( $thisApplication.product.name -eq 'Oracle Database Cluster Server' ) -or 
				 ( $thisApplication.product.name -eq 'WebSphere AS Cluster')            -or 
				 ( $thisApplication.product.name -eq 'Windows Server Clustering')       -or
				 ( $thisApplication.product.name -eq 'Veritas Cluster Server')
				)
		{
			# Get list of node names from $thisApplication -> devices
			$cluster = -join ("Cluster: ", $thisApplication.name, " Nodes: ");
			
			$k=0
			while ($k -lt  $thisApplication.devices.count)
			{
				$cluster = -join ($cluster, $thisApplication.devices[$k].host_name)
				if ($k -lt $thisApplication.devices.count)
				{
					# more to do
					$cluster = -join ($cluster, "; ")
				}
				$k = $k + 1
			}
		}
		$row | Add-Member -MemberType NoteProperty -Name "ClusterInformation" -Value $cluster			
		
		$csv += $row
		$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 OutputApplications.csv now."

$csv | 
    Select-Object Hostname, FQDN, SoftwareName, SoftwareVersion, SoftwareVendor, SoftwareEdition, 
	              InstanceIdentifier, ClusterInformation, UserCount, LastScanDate, Language,
				  ApplicationID, DeviceID, DNSHostname, DNSFQDN, Evidence |
    Export-csv OutputApplications.csv -NoTypeInformation

Versions Compared

Old Version 14

New Version 15

Key

Build the CSV file header row

Process each application

Cluster information

Write the output with the columns in the correct order

Finished Script

Page Comparison

Versions Compared

Old Version 14

New Version 15

Key

Build the CSV file header row

Process each application

Cluster information

Write the output with the columns in the correct order

Finished Script