Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 17 Next »

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.

You can download the latest version of the file: OutputApplications-v1.ps1, and simply customise the $user, $pass and $sonar values and it should run in your environment

Fields in the original report

Field NameV3 DescriptionV4 Comment
HostnameThe hostname of the device the application is running onGet this from the Applications endpoint. 
FQDNFully Qualified Domain Name of the deviceGet this from the Applications endpoint. 
SoftwareNameSoftwareName Enterprise application nameGet this from the Applications endpoint.
SoftwareVersionVersionGet this from the Applications endpoint.
SoftwareVendorSoftware vendor nameGet this from the Applications endpoint.
SoftwareEditionSoftware Edition (Enterprise, Web, etc)Get this from the Applications endpoint where applicable.
InstanceIdentifierUnique application nameUse the name field in the Applications endpoint. For database applications, this is in the "instance" name. For VMWare clusters it is the node name, etc.
ClusterInformationThe cluster that the physical machine is part of.See clusters and/or parent_cluster in the Applications endpoint.
IN V3 this contained the cluster name and list of nodes separated by ";" for some cluster types, but not all
UserCountNumber of users (where applicable)Get this from the Applications/Application_ID endpoint
LastScanDateTimestamp of last scanGet this from the Applications endpoint.
LocationLocation of the application (user defined, inherited from the device location)Get this from the Applications endpoint device section for non-clustered applications.
LanguageSQL Server instance language (SQL Server only)Not exposed via V4 RestAPI
ApplicationIDUnique Identifier for the applicationGet this from the Applications endpoint.
DeviceIDUnique Identifier for the device. Can be used to map to OutputDevicesGet this from the Applications endpoint device section for non-clustered applications.
DNSHostnameThe hostname for the device as reported by DNSGet this from the Applications endpoint device section for non-clustered applications.
DNSFQDNThe FQDN for the device as reported from DNSGet this from the Applications endpoint device section for non-clustered applications.
EvidenceThis column not available in V4Not exposed via V4 RestAPI


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:

Initial connection
$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.

Get total number of devices
$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.

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

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

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)
	{
		# Process $applications[$i]
		
		$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 | Export-csv OutputApplications.csv -NoTypeInformation		# We will further refine this line later

Process each application

Process the applications one by one
		$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

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

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

Cluster Information code
		# 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

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"

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

Finished Script
#
# 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




  • No labels