Replicate DataHub Summary_V1.Device report using RestAPI

Owned by Former user (Deleted)
Last updated: Jan 25, 2018

Introduction

The REST API allows users to query the iQSonar results directly using the web client protocol. The results are returned in JSON format. The long-term plan is that Datahub will be deprecated and eventually retired, and development of third party applications which rely on data from iQSonar will use the RestAPI.

This worked example uses PowerShell to produce a CSV file containing details of devices directly from the scan results. A shorter version of this project can be found here. This longer example replicates the result of the DataHub Summary_V1.devices view as closely as possible. Note that DataHub does perform some data normalization tasks (for example on the Operating System name/version/edition). As this is a tutorial example, this normalization is left as an exercise for the reader. 

This tutorial article is written to describe the Rest API as implemented in the Elcano R3 release (January 2018)

DataHub Summary_v1.Devices table

The DataHub Summary_v1.Devices table contains the following columns:

ColumnDescription

DeviceID

Unique identifier

Product

Type of device (e.g. Unix, Windows, SNMP)

Hostname

The host name for the device

DNSHostname

The FQDN for the device if available

DeviceIsVirtualized

Is this a virtual machine

VirtualizationMethod

Virtualization Technology used

PhysicalDeviceID

Device ID for host if this is a VM

PhysicalHostname

Hostname for host if this is a VM

Operating System Name

The name of the O/S which is used on the

Operating System Version

Which version of the OS

Operating System Edition

Standard, Enterprise etc

Operating System Release

Release no or build

Operating System InstallDate

Date the OS was installed

BiosName

The Device instance’s BIOS Name e.g. ‘PhoenixBIOS 4.0 Release 6.0’ (if it isknown)

BiosVersion

Version number for the BIOS if known

BiosVendor

Manufacturer of the BIOS

DeviceSerialNumber

Device serial number

DeviceVendor

Manufacturer of the device

DeviceModel

Model for the device

ProcessorCount

How many CPUs

ProcessorCoresPerProcessor

How many Cores per CPU

MotherboardProcessorSocketCount

Number of CPU sockets on motherboard (Not available in Rest API)

TotalMemory

Total memory for device (Not available in Rest API)

OS Reported Memory

Memory reported by the OS

ScanStatus

Scan status for this device (Not available in Rest API)

LastSeenDate

Date this device was last scanned

There are a small number of these columns we will not be able to populate in this example because the Rest API does not expose that information.

Connect to the device

You must update this part of the powershell script to reflect your location. In this example we have used the default (prior to Elcano R3) setting of login admin password "password". In fact, as of Elcano R3, you can NOT leave the default password unchanged. In our example we are using iQSonar with HTML authentication. It is possible to write a powershell script to use Windows Authentication as well.

Build the connection string
# We use simple authentication, default login/password
$apihost = "iqsonar-host"		# UPDATE THIS for your location
$user    = "admin"				# UPDATE THIS for your location
$pass    = "password"			# UPDATE THIS for your location

$secpass = ConvertTo-SecureString $pass -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential($user,$secpass)
$uribase = -join ( 'http://', $apihost, '/api/v1/devices')

Next, we need to determine how many records we are going to process. The RestAPI returns the total number of device records available in the header of the API call to get a list of devices. The "offset" and "fetch_size" parameters are then used to step through the results to avoid returning too much data in any one call to the API.

Determine number of devices
# Get a count of all devices
$uri = -join($uribase, '?offset=1&fetch_size=1')
$r = Invoke-WebRequest $uri -Credential $credential
 
# $r.headers has HTML headers, $r.content has text content
$deviceCount = $r.headers.'X-fetch-count'

Build the structure for the CSV file

To export a CSV file at the end of processing we need to build it first. This CSV file will need a column for every column in the summary_v1.devices table.

Build structure to hold CSV file
$csv = @()
$row = New-Object System.Object
$row | Add-Member -MemberType NoteProperty -Name "DeviceID" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "Product" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "Hostname" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "DNSHostname" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "DeviceIsVirtualized" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "VirtualizationMethod" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "PhysicalDeviceID" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "PhysicalHostname" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemName" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemVersion" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemEdition" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemRelease" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemInstallDate" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "BiosName" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "BiosVersion" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "BiosVendor" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "DeviceSerialNumber" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "DeviceVendor" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "DeviceModel" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "ProcessorCount" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "ProcessorCoresPerProcessor" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "MotherboardProcessorSocketCount" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "TotalMemory" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "OSReportedMemory" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "ScanStatus" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "LastSeenDate" -Value $null
$csv += $row

Process devices one at a time

The following is the skeleton of the work to process each device, with part of the processing omitted

Process each device
$count = 1;
$uri_a = -join('http://', $apihost, '/api/v1/devices/?offset=')
$uri_b = '&fetch_size=1'
 
while ( $count -le $deviceCount )
{
	# Process each device
	
    # Build the URL
     
    $url =  -join ($uri_a, $count, $uri_b )
    $dev = Invoke-RestMethod $url -Credential $credential
	$row = New-Object System.Object

	
	$row | Add-Member -MemberType NoteProperty -Name "DeviceID" -Value $dev.device_id
	$row | Add-Member  -MemberType NoteProperty -Name "LastSeenDate" -Value $dev.last_scan
	$url2 = $dev.self 
    $device = Invoke-RestMethod $url2 -Credential $credential
	
	# Product (i.e. how did we scan it?)
	$row | Add-Member -MemberType NoteProperty -Name "Product" -Value $device.operating_system.product.name
	
	#
	# Do more work
	#
	
    $count = $count + 1;
	$csv += $row
    # display progress
    if ( $count % 10 -eq 0) {
        write-host "." -nonewline
    }	
}

For each device, we get the short summary info (Device ID, Hostname, Link to rest of the details) and populate the DeviceID and Hostname fields (stored in the variable called "$dev"). Then we use the "self" link to make a second API call to get the rest of the device details for this device (stored in the variable "$device").
For every ten devices we process, we print a "." to the powershell screen to allow the user to see that progress is being made as this script can take some time to execute.

Hostname

The summary info contains the short hostname usually. ($dev.host_name) The longer Fully Qualified Domain Name is stored in an array $device.qualified_name[] (it is possible for a host to have more than one qualified name, or for a device not to return any data). To avoid an exception if the array is not populated, we test to see if the array exists - if so we return the first qualified name as the DNS name, if not we return the short name again.

This trick of testing if a variable is an array is also used again later when dealing with CPU info.

Get the host name
	$row | Add-Member -MemberType NoteProperty -Name "Hostname" -Value $dev.host_name
	# DNS Hostname. Use FQDN if available, if not use Hostname
	
	if ($device.qualified_name -is [array]) {		
		$row | Add-Member -MemberType NoteProperty -Name "DNSHostname" -Value $device.qualified_name[0].name
	}
	else {
		$row | Add-Member -MemberType NoteProperty -Name "DNSHostname" -Value $dev.host_name
	}


Virtual Devices

If this is a VM, get the properties of the host.

Virtual device Y/N
	# Device Is Virtual, will contain "true" or "false"
	$row | Add-Member -MemberType NoteProperty -Name "DeviceIsVirtualized" -Value $device.is_virtual
	if ( $device.is_virtual -eq "true" )
	{
		# list info about virtualization and host
		$row | Add-Member -MemberType NoteProperty -Name "VirtualizationMethod" -Value $device.virtualization_platform
		$row | Add-Member -MemberType NoteProperty -Name "PhysicalDeviceID" -Value $device.virtual_host.device_id
		$row | Add-Member -MemberType NoteProperty -Name "PhysicalHostname" -Value $device.virtual_host.host_name
	}

Operating System Info

This is one of the areas we run into the DataHub normalizing data, which is not done for the RestAPI - the values of these feilds will hold the raw data rather than the normalized results you would expect from DataHub

OS Details
	#
	# Device Operating System columns available in UI do not directly correlate with the fields in the DataHub report
	# because the DataHub output is normalized - this is not done to the data stored in iQSonar itself, but is left to
	# the API clients to normalize according to their own criteria.
	#
	# Best match with the data is as follows
	#
	$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemName" -Value $device.operating_system.description.name
	$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemVersion" -Value $device.operating_system.name
	$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemEdition" -Value $device.operating_system.name
	$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemRelease" -Value $device.operating_system.build_number
	$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemInstallDate" -Value $device.os_install_date

BIOS, CPU, Memory information

These fields contain information about the system BIOS, Reported RAM and CPU data.

BIOS info etc
	$row | Add-Member -MemberType NoteProperty -Name "BiosName" -Value $device.bios.name
	$row | Add-Member -MemberType NoteProperty -Name "BiosVersion" -Value $device.bios.version
	$row | Add-Member -MemberType NoteProperty -Name "BiosVendor" -Value $device.bios.manufecturer

	$row | Add-Member -MemberType NoteProperty -Name "DeviceSerialNumber" -Value $device.serial_number
	$row | Add-Member -MemberType NoteProperty -Name "DeviceVendor" -Value $device.manufacturer
	$row | Add-Member -MemberType NoteProperty -Name "DeviceModel" -Value $device.model
	$row | Add-Member -MemberType NoteProperty -Name "ProcessorCount" -Value $device.cpu_count
	
	if ($device.cpu -is [array])
	{
		$row | Add-Member -MemberType NoteProperty -Name "ProcessorCoresPerProcessor" -Value $device.cpu[0].core_count
	}
	else {
		$row | Add-Member -MemberType NoteProperty -Name "ProcessorCoresPerProcessor" -Value "0"
	}
	$row | Add-Member -MemberType NoteProperty -Name "MotherboardProcessorSocketCount" -Value "n/a"
	$row | Add-Member -MemberType NoteProperty -Name "TotalMemory" -Value "n/a"
	$row | Add-Member -MemberType NoteProperty -Name "OSReportedMemory" -Value $device.total_memory_mb

Export the resulting CSV file

We save a CSV file with the name output.csv in the current folder. (Make sure you run this script in a folder you can create files in)

Export Results
csv | Export-csv output.csv -NoTypeInformation

Compled Script

Completed Script 
#
# Powershell Script to attempt to replicate the Summary_v1.Device report out of DataHub using Rest API
#

# Define the RestAPI target host and credentials -- Define these to suit your site location

# We use simple authentication, default login/password
$apihost = "iqsonar-host"		# UPDATE THIS for your location
$user    = "admin"				# UPDATE THIS for your location
$pass    = "password"			# UPDATE THIS for your location

$secpass = ConvertTo-SecureString $pass -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential($user,$secpass)
$uribase = -join ( 'http://', $apihost, '/api/v1/devices')

# Get a count of all devices
$uri = -join($uribase, '?offset=1&fetch_size=1')
$r = Invoke-WebRequest $uri -Credential $credential
 
# $r.headers has HTML headers, $r.content has text content
$deviceCount = $r.headers.'X-fetch-count'

#
# We want to produce a CSV file, so let us build the structure
$csv = @()
$row = New-Object System.Object
$row | Add-Member -MemberType NoteProperty -Name "DeviceID" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "Product" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "Hostname" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "DNSHostname" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "DeviceIsVirtualized" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "VirtualizationMethod" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "PhysicalDeviceID" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "PhysicalHostname" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemName" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemVersion" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemEdition" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemRelease" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemInstallDate" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "BiosName" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "BiosVersion" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "BiosVendor" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "DeviceSerialNumber" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "DeviceVendor" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "DeviceModel" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "ProcessorCount" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "ProcessorCoresPerProcessor" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "MotherboardProcessorSocketCount" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "TotalMemory" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "OSReportedMemory" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "ScanStatus" -Value $null
$row | Add-Member -MemberType NoteProperty -Name "LastSeenDate" -Value $null
$csv += $row

$count = 1;
$uri_a = -join('http://', $apihost, '/api/v1/devices/?offset=')
$uri_b = '&fetch_size=1'
 
while ( $count -le $deviceCount )
{
	# Process each device
	
    # Build the URL
     
    $url =  -join ($uri_a, $count, $uri_b )
    $dev = Invoke-RestMethod $url -Credential $credential
	$row = New-Object System.Object

	
	$row | Add-Member -MemberType NoteProperty -Name "DeviceID" -Value $dev.device_id
	$row | Add-Member  -MemberType NoteProperty -Name "LastSeenDate" -Value $dev.last_scan
	$url2 = $dev.self 
    $device = Invoke-RestMethod $url2 -Credential $credential
	
	# Product (i.e. how did we scan it?)
	$row | Add-Member -MemberType NoteProperty -Name "Product" -Value $device.operating_system.product.name
	$row | Add-Member -MemberType NoteProperty -Name "Hostname" -Value $dev.host_name
	# DNS Hostname. Use FQDN if available, if not use Hostname
	
	if ($device.qualified_name -is [array]) {		
		$row | Add-Member -MemberType NoteProperty -Name "DNSHostname" -Value $device.qualified_name[0].name
	}
	else {
		$row | Add-Member -MemberType NoteProperty -Name "DNSHostname" -Value $dev.host_name
	}
	

	# Device Is Virtual, will contain "true" or "false"
	$row | Add-Member -MemberType NoteProperty -Name "DeviceIsVirtualized" -Value $device.is_virtual
	if ( $device.is_virtual -eq "true" )
	{
		# list info about virtualization and host
		$row | Add-Member -MemberType NoteProperty -Name "VirtualizationMethod" -Value $device.virtualization_platform
		$row | Add-Member -MemberType NoteProperty -Name "PhysicalDeviceID" -Value $device.virtual_host.device_id
		$row | Add-Member -MemberType NoteProperty -Name "PhysicalHostname" -Value $device.virtual_host.host_name
	}
	#
	# Device Operating System columns available in UI do not directly correlate with the fields in the DataHub report
	# because the DataHub output is normalized - this is not done to the data stored in iQSonar itself, but is left to
	# the API clients to normalize according to their own criteria.
	#
	# Best match with the data is as follows
	#
	$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemName" -Value $device.operating_system.description.name
	$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemVersion" -Value $device.operating_system.name
	$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemEdition" -Value $device.operating_system.name
	$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemRelease" -Value $device.operating_system.build_number
	$row | Add-Member -MemberType NoteProperty -Name "OperatingSystemInstallDate" -Value $device.os_install_date
	
	$row | Add-Member -MemberType NoteProperty -Name "BiosName" -Value $device.bios.name
	$row | Add-Member -MemberType NoteProperty -Name "BiosVersion" -Value $device.bios.version
	$row | Add-Member -MemberType NoteProperty -Name "BiosVendor" -Value $device.bios.manufecturer

	$row | Add-Member -MemberType NoteProperty -Name "DeviceSerialNumber" -Value $device.serial_number
	$row | Add-Member -MemberType NoteProperty -Name "DeviceVendor" -Value $device.manufacturer
	$row | Add-Member -MemberType NoteProperty -Name "DeviceModel" -Value $device.model
	$row | Add-Member -MemberType NoteProperty -Name "ProcessorCount" -Value $device.cpu_count
	
	if ($device.cpu -is [array])
	{
		$row | Add-Member -MemberType NoteProperty -Name "ProcessorCoresPerProcessor" -Value $device.cpu[0].core_count
	}
	else {
		$row | Add-Member -MemberType NoteProperty -Name "ProcessorCoresPerProcessor" -Value "0"
	}
	$row | Add-Member -MemberType NoteProperty -Name "MotherboardProcessorSocketCount" -Value "n/a"
	$row | Add-Member -MemberType NoteProperty -Name "TotalMemory" -Value "n/a"
	$row | Add-Member -MemberType NoteProperty -Name "OSReportedMemory" -Value $device.total_memory_mb
	$row | Add-Member -MemberType NoteProperty -Name "ScanStatus" -Value "n/a"
	
	
    $count = $count + 1;
	$csv += $row
    # display progress
    if ( $count % 10 -eq 0) {
        write-host "." -nonewline
    }	
}
$csv | Export-csv output.csv -NoTypeInformation