[PowerShell] API (entityAddRecord) Date format for Adding Assets?

[samwoo]

Hello,

I have started working on an Asset Upload/Update script which I will use in various circumstances. and will pull information from various other sources (such as Active Directory etc).   What I have currently set up is the part which adds the new records from a spreadsheet automatically.

clear 

#===================================================================
# Import required modules
#===================================================================
    #===================================================================
    # [MODULE] HornbillAPI
    # Description:  Allows to talk to the Hornbill API via PowerShell
    #===================================================================
        $module_HornbillAPI = Import-Module HornbillAPI -ErrorAction SilentlyContinue

        #===================================================================
        # If the HornbillAPI module doesn't exist, install and import it
        #===================================================================
        if(!$module_HornbillAPI)
        {
            Install-Module -Name HornbillAPI
            Import-Module HornbillAPI -ErrorAction SilentlyContinue
        }

    #===================================================================
    # [MODULE] PSExcel
    # Description:  Allows being able to import data from XLSX files
    #===================================================================
        $module_PSExcel = Import-Module PSExcel -ErrorAction SilentlyContinue

        #===================================================================
        # If the PSExcel module doesn't exist, install and import it
        #===================================================================
        if (!$module_PSExcel)
        {
            Install-module -Name PSExcel
            Import-Module PSExcel -ErrorAction SilentlyContinue
        }

#===================================================================
# Set up the credentials to the Hornbill instance
#===================================================================
    $instanceName = "##############"
    $instanceZone = "eur"
    $APIKey = "###############"

#===================================================================
# Connect to the Hornbill instance
#===================================================================
    Set-HB-Instance -Instance $instanceName -Zone $instanceZone -Key $APIKey

#===================================================================
# Import the contents of the XLSX file
#===================================================================
    $AssetsUploadXLSX = Import-XLSX 'C:\Users\########\Desktop\Bulk Upload Samsung Galaxy A40 into Hornbill.xlsx'

#===================================================================
# Retrieve the Asset Substates directly from Hornbill
#===================================================================

    #===================================================================
    # API Call to Hornbill
    #===================================================================
    Add-HB-Param "application" "com.hornbill.servicemanager"
    Add-HB-Param "entity" "AssetSubstate"

    $XMLMCOutputAssetSubstates = Invoke-HB-XMLMC "data" "entityBrowseRecords2"

    [object]$HBAssetSubstates = $XMLMCOutputAssetSubstates.Params.rowData.row

#===================================================================
# Loop through each line of the XLSX file
#===================================================================
foreach($AssetToCheck in $AssetsUploadXLSX)
{
    #===================================================================
    # Add the IMEI and Serial number to variables
    #===================================================================
    $AssetIMEI 					= $AssetToCheck.imeiNumber
    $AssetSerial 				= $AssetToCheck.serialNumber
    $AssetName 					= $AssetToCheck.name
    $AssetTag 					= $AssetToCheck.assetTag
    [String]$AssetReceivedDate 	= $AssetToCheck.receivedDate.("dd MMM yyyy hh:mm tt")
    #$AssetReceivedDate 		= $AssetReceivedDateImport.ToString("dd MMM yyyy hh:mm tt")
    #Write-Host $AssetReceivedDate
    $AssetSubstateName 			= $AssetToCheck.subState
    $AssetSubstateID 			= ($HBAssetSubstates | Select-Object -Property h_pk_asset_substate_id, h_asset_substate_name | Where-Object h_asset_substate_name -like "$AssetSubstateName").h_pk_asset_substate_id
    $AssetCapacity 				= $AssetToCheck.capacity
    $AssetManufacturer 			= $AssetToCheck.manufacturer
    $AssetModel 				= $AssetToCheck.model
    $AssetOSVersion 			= $AssetToCheck.osVersion
	
  	#===================================================================
  	# Set Operational State to the relevant number 
  	#===================================================================
    switch ($AssetToCheck.operationalState)
    {
        "pre-production" { $AssetOperationalState = 0 }
        "operational" { $AssetOperationalState = 1 }
        "retired" { $AssetOperationalState = 2 }
        default { $AssetOperationalState = $AssetToCheck.operationalState }
    }
	
  	#===================================================================
  	# Set Record State to the relevant number 
  	#===================================================================
    switch ($AssetToCheck.state)
    {
        "current" { $AssetRecordState = 0 }
        "active" { $AssetRecordState = 1 }
        "archived" { $AssetRecordState = 2 }
        default { $AssetRecordState = $AssetToCheck.state }
    }

    #===================================================================
    # If the IMEI number doesn't exist, skip the record 
  	#
  	# TODO: Create method of notifying the user that this record did
  	#       not get processed
    #===================================================================
    if($AssetIMEI -eq $null) { continue }

    #===================================================================
    # If the Serial Number doesn't exist, skip the record
    #
    # TODO: Create method of notifying the user that this record did
  	#       not get processed
    #===================================================================
    #===================================================================
    if($AssetSerial -eq $null) { continue }

    #===================================================================
    # Prepare the API call to search Hornbill MobileDevice assets for
    # the IMEI number from the current line from the XLSX file
    #===================================================================
    Add-HB-Param "application" "com.hornbill.servicemanager"
    Add-HB-Param "entity" "AssetsMobileDevice"
    Add-HB-Param "matchScope" "any"

    Open-HB-Element "searchFilter"
        Add-HB-Param "column" "h_imei_number" 
        Add-HB-Param "value" $AssetIMEI
    Close-HB-Element "searchFilter"

    Open-HB-Element "searchFilter"
        Add-HB-Param "column" "h_serial_number" 
        Add-HB-Param "value" $AssetSerial
    Close-HB-Element "searchFilter"

    #Get-HB-Params

    #===================================================================
    # Call the API using the parameters above
    #===================================================================
    $HBmobileDevice = Invoke-HB-XMLMC "data" "entityBrowseRecords2"

    #===================================================================
    # Check the status of the API call
    #===================================================================
    if($HBmobileDevice.Params)
    {
        Write-Host "Asset in Hornbill"
        #===================================================================
        # If the API call worked, return the data from Hornbill
      	#
      	# TODO: Create the process to update existing Assets
        #===================================================================
        $HBmobileDevice.params.rowdata.row | ft
    } 
    else
    {

        $date = "$(get-date -Format 'yyyy-MM-dd HH:mm:ss')"
        #$date = "$(get-date -Format 'dd/MM/yyyy')"
      	#$date = "$(get-date -Format 'yyyy-MM-dd HH:mm:ss')Z"

        Write-Host "Asset NOT in Hornbill. Inserting...."

        Add-HB-Param "Application" "com.hornbill.servicemanager"
        Add-HB-Param "entity" "Asset"
        Add-HB-Param "returnModifiedData" "true"

        Open-HB-Element    "primaryEntityData" 
            Open-HB-Element    "record" 
               Add-HB-Param    "h_name"                $AssetName
               Add-HB-Param    "h_asset_tag"           $AssetTag
               Add-HB-Param    "h_class"               "mobileDevice"     # Mobile Device class
               Add-HB-Param    "h_type"                "483"              # Smart Phone asset type
               Add-HB-Param    "h_last_updated"        $date              
               Add-HB-Param    "h_last_updated_by"     "Import - Add"              
               Add-HB-Param    "h_company_name"        "######### ######## #######"
               Add-HB-Param    "h_company_id"          "###"
               Add-HB-Param    "h_operational_state"   $AssetOperationalState
               Add-HB-Param    "h_received_date"       $AssetReceivedDate
               Add-HB-Param    "h_record_state"        $AssetRecordState
               Add-HB-Param    "h_substate_id"         $AssetSubstateID
               Add-HB-Param    "h_substate_name"       $AssetSubstateName
             Close-HB-Element "record" 
          Close-HB-Element   "primaryEntityData" 

          Open-HB-Element "relatedEntityData"
            Add-HB-Param       "relationshipName" "AssetClass"
            Add-HB-Param       "entityAction" "insert"
            Open-HB-Element "record"
               Add-HB-Param    "h_type" "483"
               Add-HB-Param    "h_imei_number" $AssetIMEI
               Add-HB-Param    "h_serial_number" $AssetIMEI
               Add-HB-Param    "h_capacity" $AssetCapacity
               Add-HB-Param    "h_manufacturer" $AssetManufacturer
               Add-HB-Param    "h_model" $AssetModel
               Add-HB-Param    "h_os_version" $AssetOSVersion
            Close-HB-Element "record"
          Close-HB-Element "relatedEntityData"

          $XMLCOutputAdd = Invoke-HB-XMLMC "data" "entityAddRecord"

          $XMLCOutputAdd

    }

}

The problem I am having is getting the "Received Date" inserted when the process creates the record.  It doesn't appear to like the date format, which is what I took from the tables.

I cannot see any other information about the date format and am hoping someone can advise me on what I should use?

Thanks,

Samuel

[samwoo]

Hello,

Nevermind the above query, I have managed to find the correct format by doing a google search on MySQL default DATETIME format

[String]$AssetReceivedDate = ([datetime]$AssetToCheck.receivedDate).ToString("yyyy-MM-dd hh:mm:ss")

Thanks,

Samuel