Automate Jira Assets Integration Tasks from PowerShell

The CData Cmdlets for Jira Assets are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Jira Assets.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to Jira Assets, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete Jira Assets data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for Jira Assets. To access Jira Assets data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Jira Assets.

Once you have acquired the necessary connection properties, accessing Jira Assets data in PowerShell can be enabled in three steps.

Jira Assets supports connecting and authenticating via the APIToken.

To generate an API token:

1. Log in to your Atlassian account.
2. Navigate to Security < Create and manage API Token < Create API Token.

Atlassian generates and then displays the API token.

After you have generated the API token, set these parameters:

• AuthScheme: APIToken.
• User: The login of the authenticating user.
• APIToken: The API token you just generated.

You are now ready to connect and authenticate to Jira Assets.

PowerShell

1. Install the module:

   Install-Module JiraAssetsCmdlets

2. Connect:

   $jiraassets = Connect-JiraAssets -User "$User" -APIToken "$APIToken" -Url "$Url"
   

3. Search for and retrieve data:

   $label = "SYD-1"
   $objects = Select-JiraAssets -Connection $jiraassets -Table "Objects" -Where "Label = `'$Label`'"
   $objects
   

   You can also use the Invoke-JiraAssets cmdlet to execute SQL commands:

   $objects = Invoke-JiraAssets -Connection $jiraassets -Query 'SELECT * FROM Objects WHERE Label = @Label' -Params @{'@Label'='SYD-1'}
   

ADO.NET

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Jira Assets\lib\System.Data.CData.JiraAssets.dll")
    

2. Connect to Jira Assets:

    
   $conn= New-Object System.Data.CData.JiraAssets.JiraAssetsConnection("User=MyUser;APIToken=myApiToken;Url=https://yoursitename.atlassian.net")
   $conn.Open()
   

3. Instantiate the JiraAssetsDataAdapter, execute an SQL query, and output the results:

   $sql="SELECT ID, Name from Objects"
   
   $da= New-Object System.Data.CData.JiraAssets.JiraAssetsDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.id $_.name
   }
    

Update Jira Assets Data

PowerShell

Update-JiraAssets -Connection $JiraAssets -Columns @('ID','Name') -Values @('MyID', 'MyName') -Table Objects -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.JiraAssets.JiraAssetsCommand("UPDATE Objects SET Label='SYD-1' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.JiraAssets.JiraAssetsParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Jira Assets Data

PowerShell

Add-JiraAssets -Connection $JiraAssets -Table Objects -Columns @("ID", "Name") -Values @("MyID", "MyName") 

ADO.NET

$cmd = New-Object System.Data.CData.JiraAssets.JiraAssetsCommand("INSERT INTO Objects (Label) VALUES (@myLabel)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.JiraAssets.JiraAssetsParameter("@myLabel","SYD-1")))
$cmd.ExecuteNonQuery()

Delete Jira Assets Data

PowerShell

Remove-JiraAssets -Connection $JiraAssets -Table "Objects" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.JiraAssets.JiraAssetsCommand("DELETE FROM Objects WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.JiraAssets.JiraAssetsParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()

CodeProject