Does anyone know if we have a report or able to extract a report to show all Datalink connectors in details? We got more than 100 connectors and most of the connectors are scheduled. We need to leverage the schedules by day and time to avoid some scheduler conflicts. I believe it will be handful if we can have a report to show the details. Thanks!

Hi Alex! We do not currently have the ability to produce a report containing all connector details. It's been coming up more so I've got an enhancement request in the queue to get it done. (Can't promise a timeline at this point, probably no sooner than this summer given other things on the list.)

In the meantime, it should be possible to create a script that extracts the information via our REST API. It would be awesome if someone were to figure out how to do this and shares back with the Community!

Thanks @Ken Haniu!, Glad to hear this was on the list.

Hello Alex,

I wrote a PowerShell script to pull all the DataLink connectors for our DL instance and save the csv data to your local desktop.  Please see below and I would be happy to provide feedback if you have any questions.

You must update the below bolded and underlined code with your DL connector url.

example:

YOURDATALINKURLHERE would be replaced with, dl-YOURCOMPANYName-manager.apptio.com

You must also use an API key from your Front Door user management to pull information using this script.  I cannot use simple username/password pairs in my company so the API key was used. More information about API keys can be found here, API keys and Frontdoor: Overview and FAQs (Outdated; click for updated version) .  The script will check for encrypted files in your executable path and if no keys are found it will ask you to enter them then encrypt them in your executable path for future API calls.  

#############################################################################################################
#Script to use API calls for Apptio FrontDoor applications #
#Script written by Robert Schneider December 15th 2017 currently using Robert Schneider's API Key #
#############################################################################################################

$PSScriptRoot = Split-Path $MyInvocation.MyCommand.Path -Parent #set the current path of the script Get-location |select path
$secretkey = "$PSScriptRoot\secretkey.pwd" #setting path of secretkey file
$publickey = "$PSScriptRoot\publickey.pwd" #setting path of publickey file

if (((Test-Path $secretkey) -eq $false) -or (Test-Path $publickey) -eq $false){ #testing path for public / secret key file

#below is a custom input form for the public/private key pair
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing

$objForm = New-Object System.Windows.Forms.Form
$objForm.Text = "Public/Private Key Entry"
$objForm.Size = New-Object System.Drawing.Size(300,250)
$objForm.StartPosition = "CenterScreen"

$OKButton = New-Object System.Windows.Forms.Button
$OKButton.Location = New-Object System.Drawing.Size(75,180)
$OKButton.Size = New-Object System.Drawing.Size(75,23)
$OKButton.Text = "OK"
$OKButton.DialogResult = [System.windows.forms.dialogresult]::OK
$objForm.AcceptButton= $OKButton
$objForm.Controls.Add($OKButton)

$CancelButton = New-Object System.Windows.Forms.Button
$CancelButton.Location = New-Object System.Drawing.Size(150,180)
$CancelButton.Size = New-Object System.Drawing.Size(75,23)
$CancelButton.Text = "Cancel"
$CancelButton.DialogResult = [System.windows.forms.dialogresult]::Cancel
$objForm.AcceptButton= $CancelButton
$objForm.Controls.Add($CancelButton)

$objLabel = New-Object System.Windows.Forms.Label
$objLabel.Location = New-Object System.Drawing.Size(10,20)
$objLabel.Size = New-Object System.Drawing.Size(280,20)
$objLabel.Text = "PLEASE ENTER PUBLIC KEY:"
$objForm.Controls.Add($objLabel)

$objTextBox = New-Object System.Windows.Forms.TextBox
$objTextBox.Location = New-Object System.Drawing.Size(10,40)
$objTextBox.Size = New-Object System.Drawing.Size(260,20)
$objForm.Controls.Add($objTextBox)

$objLabel = New-Object System.Windows.Forms.Label
$objLabel.Location = New-Object System.Drawing.Size(10,100)
$objLabel.Size = New-Object System.Drawing.Size(280,20)
$objLabel.Text = "PLEASE ENTER PRIVATE KEY:"
$objForm.Controls.Add($objLabel)

$objTextBox2 = New-Object System.Windows.Forms.TextBox
$objTextBox2.Location = New-Object System.Drawing.Size(10,120)
$objTextBox2.Size = New-Object System.Drawing.Size(260,20)
$objForm.Controls.Add($objTextBox2)

$objForm.topmost = $true

$result = $objform.ShowDialog()

if ($result -eq [System.Windows.Forms.DialogResult]::OK)
{
$x= $objTextBox.Text
$y= $objTextBox2.Text
}
if ($result -eq [System.Windows.Forms.DialogResult]::Cancel)
{
$wshell = New-Object -ComObject Wscript.Shell
$wshell.Popup("YOU HAVE CANCELLED YOUR INPUT, PLEASE TRY AGAIN AND ENSURE YOU'RE SELECTING OK.",0,"Error")|Out-Null
exit}

#end of custom input form for the public/private key pair

$x|ConvertTo-SecureString -AsPlainText -Force|convertFrom-SecureString > $publickey #exporting and encrypting public
$y|ConvertTo-SecureString -AsPlainText -Force|convertFrom-SecureString > $secretkey #exporting and encrypting secretkey
}

$secretkeyplain = Get-Content -path $secretkey #pulling encrypted secretkey

$publickeyplain = Get-Content -path $publickey #pulling encrypted publickey

$PlainTextSecret= [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR( (ConvertTo-SecureString $secretkeyplain) )) #unencrypting secretkey

$PlainTextPublic= [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR( (ConvertTo-SecureString $publickeyplain) ))#unencrypting publickey

$string =@{keySecret=$PlainTextSecret;keyAccess=$PlainTextPublic} |convertto-json -compress #creating hash-table to pass public/private key pair to apptio

$header = Invoke-WebRequest -Method Post -Uri 'https://frontdoor.apptio.com/service/apikeylogin' -ContentType "application/json" -Body $string |Select -ExpandProperty headers #retrieving key header for opentoken

$opentoken=@{"apptio-opentoken"=$header."apptio-opentoken"} #must be in a table format for passing credentials must pass in -header parameter in invoke-restmethod (get) and Invoke-WebRequest (curl/post)

#############################################################################################################
#Remaining script are API calls using opentoken from authentication above must pass $opentoken in -header #
#############################################################################################################

$dlagentlist = Invoke-RestMethod -Method Get -Uri 'https://PUTYOURDATALINKURLHERE/api/v1/resource/agent?include=name' -Headers $opentoken | Select-Object result -ExpandProperty result |select url #retrieves URL agent key for pulling connectors

foreach ($agent in $dlagentlist){ #foreach loop if url has multiple agents
$url = $agent.url #setting URL variable for API call below
Invoke-RestMethod -Method Get -Uri "$url/connector?include=name,type" -Headers $opentoken | Select-Object result -ExpandProperty result |select connectorId,url,name,type | Export-Csv C:\Users\$env:USER\Desktop\Connectors_List.csv -Append -NoTypeInformation} #pulling all connector information available via API and saving to your desktop

Thanks @Robert Schneider! This is very helpful. I will try this out 

Glad to hear you found it helpful @Alex Ng!  If you have any question about the code, feel free to reach out to me. 

This is awesome, @Robert Schneider! Another one to add to New and Noteworthy on the DL KB for a while so people notice it! Thank you!

Hi @Robert Schneider! I am able to extract the report nicely. However, I am not an expert using PowerShell script. Are we able to add in additional columns like Status, Next Run, Last Run, Schedule frequency day and time, etc.?

I tried to modify here "ExpandProperty result |select connectorId,url,name,type,status,next run,last run | Export-Csv C:\Users\$env:USER\Desktop\Connectors_List.csv" but it's not working  Can you guide me? Thanks!

I think i got it with below code:

Invoke-RestMethod -Method Get -Uri "$url/connector?include=name,type,status,nextrun,lastrun" -Headers $opentoken | Select-Object result -ExpandProperty result |select connectorId,url,name,type,status,nextrun,lastrun| Export-Csv C:\Users\1171261\Desktop\Connectors_List.csv -Append -NoTypeInformation} #pulling all connector information available via API and saving to your desktop

But still I need to get the schedules information 

@Alex Ng, it is my understanding that specific information is not currently available via the API calls. 

In the PowerShell Select command you can use a star as a wild card character to get all available objects.  Example:

Select-Object result -ExpandProperty result | select * | Export-Csv 

Below is a snapshot of the information from the DataLink connector API documentation.  You can find more documentation after you log into your DataLink manager and select the cog wheel in the top right and select API Documentation, see below.

Connectors ¶

Thanks @Robert Schneider. Got it!

We recently realized the documentation was out of date. It will be updated next week to specify that name, type, nextrun, lastrun, enabled, status, and groupid are valid properties for the include parameter.

@Ken Haniu and @Alex Ng, that is great information! Thank you!  All you would need to do to capture this additional information from the API call would be to add these columns in the include API URL here, Invoke-RestMethod -Method Get -Uri "$url/connector?include=name,type,status,nextrun,lastrun", and  remove the "|select *" in the following code: Select-Object result -ExpandProperty result | select * | Export-Csv.  However, if you want to only select specific headers from the output of the API you would want to keep the select and just add the header names as they appear in the file.  I prefer to have all the data returned so I removed the "|select *" from the command.

Hello,

We are having some trouble with the URL ''https://frontdoor.apptio.com/service/apikeylogin'. 

When we run the script it errors out on the POST URL line. Is there a more updated URL to utilize?

This script will help us with financial month end reporting and help to streamline our close process via Apptio TBM

We recently put in a ticket however it appears that custom powershell scripts cannot be reviewed at this time. 

@Jeff Cairns, @Ryan Greene, @Eric Antonides, @Gregg Palaian, @Juliet Orgain

, ,

Hi @Jaiveer Kahlon,

I am using Control-M to call the DataLink API. I submit a POST request to https://frontdoor.apptio.com/service/apikeylogin and include the "content-type: 'application/json'" and "Accept: 'application/json'" HTTP headers. In the request body are my API user's keyAccess and keySecret values as JSON. It works as expected. I'm not sure about PowerShell. But I know you can easily test it with cURL in the command prompt.

Hi Jeff,

Thank you. We are still getting an error in PowerShell. 

I have attached a screenshot below of the error. 

I'd confirm you are including the {"keyAccess":"<keyAccessValue>"} pair in the request body of JSON.