[Broken Power BI Integration]

@m.vandun @chriscorcoran @Paul Alexander @Will J Douglas

Afternoon all,

I've just released v1.7.0 of the R scripts - they now support using the XLSX output of the reports (as well as the existing CSV output), so you can get your Power BI reports back up and running now instead of waiting on the platform fix to the CSV issue (which is actually done and will be in the next platform update).

Hope this helps,

Steve

[IMPORTANT - Power BI R Script Updates!]

Good afternoon,

We've just released v1.7.0 of the R scripts that are used to pull data from Hornbill Reporting into Power BI. The Report and HistoricReport scripts now support the use of XLSX files in the output of Hornbill reports, as well as the existing CSV options.

The updated scripts can be downloaded now from Github.

As always, please review the wiki documentation for the changes, but as a very basic overview:

• XLSX output needs to be enabled against your target report configuration:
  • 
• useXLSX needs to be set to TRUE in the scripts for them to target the XLSX file in the report output rather than the CSV file;
• When useXLSX is TRUE, the readxl package is required to be installed on the machine running the scripts.

Thanks,

Steve

[Broken Power BI Integration]

Hi @Paul Alexander,

There are two distinct issues here, one that's been fixed by an update to the R script that allows you to pull report data from Hornbill into Power BI (a correction to the response content type when API calls are made to Hornbill, that the R didn't cater for), and the second issue where CSV report data is improperly formatted. The second one I'm not sure about, as others in development are investigating that issue, but updates to that issue will be posted here:

Cheers,

Steve

[Powershell API module issue]

@Giuseppe Iannacone,

Is the substate value not being updated in the record, if you were to check it in Database Direct? What about if you populate the h_substate_name field too? I'm not sure how the interface works in this app, so maybe @ArmandoDM would be best placed to assist, as this isn't an issue with the module...

Thanks,

Steve

[Broken Power BI Integration]

Hi @m.vandun,

Looks like we have a separate issue with report CSV data not being correctly output, which is causing this knock-on issue with the Power BI integration. This is currently under investigation:

Thanks,

Steve

[Powershell API module issue]

@Giuseppe Iannacone No worries. Sorry, I didn't realise that old Github repo was linked on the wiki, I'll get that updated too. We moved the PowerShell modules over to the PowerShell Gallery a good while ago, so that we had a single module source for both Windows and Azure PowerShell, rather than maintaining two codebases.

If you visit the module pages linked above, and expand Package Details, you will get a list of the cmdlets exported by the module:

And if you expand FileList and click on the psm1 file, this will show you the cmdlet sourcecode, with documentation contained within for each of the exported cmdlets. See the below for Add-HB-Param:

Cheers,

Steve

 

 

[Powershell API module issue]

Hi @Giuseppe Iannacone,

The latest versions of the HornbillAPI and HornbillHelpers modules can be viewed in the PowerShell Gallery:

https://www.powershellgallery.com/packages/HornbillAPI/1.1.0

https://www.powershellgallery.com/packages/HornbillHelpers/1.1.1

From PowerShell, run the following commands:

Install-Module -Name HornbillAPI
Install-Module -Name HornbillHelpers

And this will install the latest versions on to your machine.

The Github version is old, and should be deprecated - I'll do this now, thanks.

Cheers,

Steve

[HTTP Request Integration Help]

Hi @Jeremy,

Did you get anywhere with this? At a glance, I would think that the endpoint is expecting a JSON payload, but the Body param doesn't appear to be JSON formatted - unless there's some JSON written into that pcf variable...  

I've just given it a test to be 100% sure though, pointing a HTTP Request node out to RequestBin, and the POST request was received as expected:

 

Cheers,

Steve

[Powershell API module issue]

Hi @Giuseppe Iannacone,

I've just given this a whirl, the HornbillAPI module is working as expected, but there are a number of issues with your script - all calls to Open-Element, Close-Element and Add-Param should be replaced by the correct cmdlets: Open-HB-Element, Close-HB-Element and Add-HB-Param otherwise you wont be sending any payload to the API.

I wrote the following very basic script as an example, using v1.1.0 of the Hornbill API module, which searches for an exact match and includes some very basic handling of when a record is not found. This is when it's successfully found a matching asset:

And when an asset record is not returned:

Hope this helps,

Steve

[Broken Power BI Integration]

Hi @Will J Douglas,

I was able to replicate this, it appears to have been caused by a change in the platform. I've updated the R scripts to cater for this, v1.6.0 is on Github now.

You may still get the 'httr' was built under R version 3.6.2 warning - this is due to a mismatch of the version of R and the httr library on your local R installation. Updating the module from the R package library on your machine running R should sort that. 

Thanks,

Steve

[Powershell API module issue]

@Giuseppe Iannacone,

The column you are searching for looks to be correct, but I'd suggest you also add the matchType property to the searchFilter parameter, and set it to exact, as by default this is set to wildcard.

Thanks,

Steve

[Powershell API module issue]

Hi @Giuseppe Iannacone,

This is basically saying that the value in your $h_id varible is null, and you can't .Trim() a null. This would suggest that either no records have been returned by your call to entityBrowseRecords, or more than one has (so row would be an array of PSObjects rather than a flat PSObject), in which case you need to handle either of those outcomes in your script.

Cheers,

Steve

[Web Hook issue]

Hi @Francis von Stein,

Apologies for the delayed response. I've spoken to the relevant development team, and the ability for the webhooks to output fields that have been emptied is not possible, and is not currently on the development roadmap.

Kind regards,

Steve

[Tick Allow Task View using iBridge]

Hi @Paul Alexander,

That's done, should be available on your instance in the next 10 mins or so:

Let me know how you get on with this.

Cheers,

Steve

[Update cleaner tool to remove all assets using their name]

@Paul Alexander Forgot to mention, as with any of the open source tools, and especially those that are destructive, it's highly recommended that you run your new config through this in dryrun mode first and check the logs to make sure the output is going to be as expected!

Cheers,

Steve 

[Update cleaner tool to remove all assets using their name]

Hi @Paul Alexander,

We've just released v1.12.0 of the cleaner tool, which allows you to add more complex filters to filter the assets for deletion by.

So for example, if you wanted to delete all assets that have a name of YourAssetName, you would apply the following filter in the new AssetFilters array:

{
	"CleanRequests": false,
	"RequestServices":[],
	"RequestStatuses":[],
	"RequestTypes":[],
	"RequestReferences":[
	],
	"RequestLogDateFrom":"",
	"RequestLogDateTo":"",
	"RequestClosedDateFrom":"",
	"RequestClosedDateTo":"",
	"CleanAssets": true,
	"AssetClassID": "",
	"AssetTypeID": 0,
	"AssetFilters": [
		{
			"ColumnName": "h_name",
			"ColumnValue": "YourAssetName",
			"Operator": "Equals",
			"IsGeneralProperty": true
		}
	],
	"CleanUsers":false,
	"Users":[]
}

The way the filters work (including available operators) is all documented on the Hornbill wiki.

Re: the 100 link limit, I'm not sure about that so I'll get someone who knows that area of the app a little better to respond.

Cheers,

Steve

[Google Sheets Integration]

Hi @Jeremy,

I've made some changes to the Update and Append operations, so that they will now populate success with a boolean false in the event that the operation fails for something other than an authentication issue. Authentication/token issues are still handled by a hard-fail of the operation. I've also included a new output parameter called errors, which will be populated with the error returned from Google if success equals false.

Let me know how you get on with these.

Cheers,

Steve

 

[Google Sheets Integration]

@Jeremy ok, no worries, I'll take a look and let you know how I get on.

Cheers,

Steve

[Power BI connection issues]

Hi @Lee Jones,

This is an issue with the R packages on the machine where you're running R and Power BI. The error is stating that the R6 package (which is imported by the httr package that the script uses) isn't available on your machine. I'd suggest re-installing httr from the CRAN repository, and if you get the same error then manually install the R6 package within your R package manager.

Cheers,

Steve

[Google Sheets Integration]

Hi @Jeremy,

I'll take a look, there should be an output param called success that is set to true if the operation was successful, is this not working?

Thanks,

Steve

[Automated request logging?]

Hi @clampj,

You could use the Request Import Tool, with an ODBC connection pointing to your spreadsheet as the data source.

Thanks,

Steve

[Get PC answers to use as PShell variables]

@Dan Munns

Yeah that's correct, so in your node that's starting a job against this package operation in workflow, you'd have that variable in the PSVariable input parameter.

[Get PC answers to use as PShell variables]

@Dan Munns We've also got a task in our list to add a package to manage Exchange mailboxes, I'll make sure New-RemoteMailbox gets looked at.

Cheers,

Steve

[Get PC answers to use as PShell variables]

Hi @Dan Munns,

There are a couple of ways you could do this:

1. You could write the answers into request custom fields, then pass the values through as input parameters to your package operation, reading them in the PowerShell and using however you need;
2. In the PowerShell, you could make an API call to Hornbill to get the questions for the request you're processing, then extract and use the information from the response. You'd want to use the data::entityBrowseRecords2 API, something like this if you're using the HornbillAPI Module:

Set-HB-Instance -Instance "yourinstance" -Key "yourapikey"
Add-HB-Param "application" "com.hornbill.servicemanager"
Add-HB-Param "entity" "Questions"
Open-HB-Element "searchFilter"
Add-HB-Param "column" "h_entity_ref"
Add-HB-Param "value" $TheRequestReference
Add-HB-Param "matchType" "exact"
Close-HB-Element "searchFilter"
$Questions = Invoke-HB-XMLMC "data" "entityBrowseRecords2"

Let me know if you need anything else.

Cheers,

Steve

[Retrieve a Contact Record in the Business Process]

Hi @RIchard Horton,

There is an automation to Unarchive a Contact, available here:  

I agree that there should be an automation to retrieve the details of a contact from a given email address, I'll raise that as an enhancement and will post back here once implemented.

Thanks,

Steve