Need a way to get Zendesk field info in JQL

[Aarón López ITERA]

Hi,

Is there a way to get data from the zendesk support field in a JQL?.

The JQL is to get the report document and see all the data.

If there is another way to get this info, it works fine.

I have tried API call but could not see it.

Thank you!

[leslie_carter]

Here is the Powershell that will pull your data showing zendesk tickets..  Dump the CSV in to SQL or SSRS report... Fill in the "XXXX" with your data and update file paths appropriately.

# Install the required module (if not already installed)
#Install-Module -Name PSJira -Scope CurrentUser

# Tell the module what is the server's address
Set-JiraConfigServer -Server "https://xxxxxx.atlassian.net"

# Clean up previous files

Remove-Item -Path "C:\temp\*"

# Define your Jira instance URL, username, and API token
$JiraUrl = "https://xxxxx.atlassian.net"
$Username = "your email"
$ApiToken = Your Token
$JqlQuery1 = "project = xxxx"
# Make the REST API call to Jira
$response = Invoke-RestMethod -Uri "$JiraUrl/rest/api/2/search?jql=$JqlQuery1" -Method Get -Headers @{
Authorization = "Basic $Base64AuthInfo"
Accept = "application/json" }

# Initialize an empty array to store issues
$AllIssues = @()

# Calculate the total number of issues
$TotalIssues = $response.total
$TotalIssues
# Set page size and start index
$PageSize = 50
$StartIndex = 0

# Encode the credentials
$Base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes("leslie.carter@bostonbeer.com:$ApiToken"))

# Loop through all issues in batches
while ($StartIndex -lt ($TotalIssues + 30)) {
# Define the JQL query to retrieve issues
$JqlQuery2 = "project = xxxxx ORDER+BY+KEY+ASC&startAt=$StartIndex&maxResults=$PageSize"

# Get a batch of issues
$CurrentBatch = Invoke-RestMethod -Uri "$JiraUrl/rest/api/2/search?jql=$JqlQuery2" -Method Get -Headers @{
Authorization = "Basic $Base64AuthInfo"
Accept = "application/json"}

# Output the retrieved issues

if ($StartIndex -eq 0)

# Print header only once
{
$CurrentBatch.issues |
ForEach-Object {

"Issue Key"+ ", "+ "Summary"+", "+"Status"+", " +"Asignee"+", "+"Created"+", "+"Hours"+", " +"Story Points"+","+"Sprint Name"+"," +"Sprint StartDT"+"," +"Sprint EndDT"+", "+"Resolution Date"+", "+"Jira Label"+", "+"Jira Name"+", "+"Requestor"+", "+"Business Value"

} {

" $($_.key)" + " , " + # issue key
" $($_.fields.summary -replace '[[+•,*?()\\.#]',' ')" + " , " + #issue summary
" $($_.fields.status.name -replace '[[+•*,?()\\.•#]','')" + " , " + #issue status
" $($_.fields.assignee.displayname -replace '[[+,•*?()\\.•#`]',' ')" + " , " + #issue assignee
" $($_.fields.created)" + " , " + #issue date created
" $($_.fields.customfield_11080 -replace '[[+,•*?()\\\r•#]',' ')" + " , " + #issue hours
" $($_.fields.customfield_11002 -replace '[[+,•*?()\\\r•#]',' ')" + " , " + #issue story points
" $($_.fields.customfield_10006.name -replace '[[+,•*?()\\\r.•#]',' ')" + " , " + #issue sprint name
" $($_.fields.customfield_10006.startdate -replace '[[+,•*?()\\\r.•#]',' ')" + " , " + #sprint start date
" $($_.fields.customfield_10006.enddate -replace '[[+,•*?()\\\r.•#]',' ')" + " , " + #sprint end date
" $($_.fields.resolutiondate)" + " , " + #Resolution Date
" $($_.fields.labels -replace ',', ' ')" + " , " + #jira label
" $($_.fields.creator.displayName -replace ',', ' ')" + " , " + #jira label
" $($_.fields.customfield_11078 -replace ',', ' ')" + " , " + #issue requestor
" $($_.fields.customfield_11075 -replace ',', ' ')" #issue business value

} | ConvertTo-Json >> C:\temp\Jira.txt #save jira api request data to file

# Update start index for the next batch
$StartIndex += $PageSize

}

else

{
$CurrentBatch.issues |
ForEach-Object {

" $($_.key)" + " , " + # issue key
" $($_.fields.summary -replace '[[+•,*?()\\.#]',' ')" + " , " + #issue summary
" $($_.fields.status.name -replace '[[+•*,?()\\.•#]','')" + " , " + #issue status
" $($_.fields.assignee.displayname -replace '[[+,•*?()\\.•#`]',' ')" + " , " + #issue assignee
" $($_.fields.created)" + " , " + #issue date created
" $($_.fields.customfield_11080 -replace '[[+,•*?()\\\r•#]',' ')" + " , " + #issue hours
" $($_.fields.customfield_11002 -replace '[[+,•*?()\\\r•#]',' ')" + " , " + #issue story points
" $($_.fields.customfield_10006.name -replace '[[+,•*?()\\\r.•#]',' ')" + " , " + #issue sprint name
" $($_.fields.customfield_10006.startdate -replace '[[+,•*?()\\\r.•#]',' ')" + " , " + #sprint start date
" $($_.fields.customfield_10006.enddate -replace '[[+,•*?()\\\r.•#]',' ')" + " , " + #sprint end date
" $($_.fields.resolutiondate)" + " , " + #Resolution Date
" $($_.fields.labels -replace ',', ' ')" + " , " + #jira label
" $($_.fields.creator.displayName -replace ',', ' ')" + " , " + #jira label
" $($_.fields.customfield_11078 -replace ',', ' ')" + " , " + #issue requestor
" $($_.fields.customfield_11075 -replace ',', ' ')" #issue business value

} | ConvertTo-Json >> C:\temp\jira.txt #save jira api request data to file

# Update start index for the next batch
$StartIndex += $PageSize

}

}

#Clean Files

$file = "C:\temp\Jira.txt"
$filename="C:\temp\Jira_Clean.txt"
$outputfile="$filename"

Get-Content $file | Foreach-object {
$_ -replace '\[', '' `
-replace '\]', '' `
-replace '\"', '' `
-replace '\|', ''
} | Set-Content $outputfile

(gc C:\temp\BSYS_Jira_Clean.txt) | Foreach {$_.TrimEnd()} | where {$_ -ne ""} | Set-Content C:\temp\Jira_Clean.csv

[Esteban Diaz]

Hi @Aarón López ITERA , 

If you need to create reports with Jira data and other sources like Zendesk, give it a try to EazyBI .... This is a great BI tool for Jira and you can add external sources to gather data and create your reports.

In fact you can easily integrate it with Zendesk: https://eazybi.com/integrations/zendesk 

Cheers, 

Esteban

[Aarón López ITERA]

Hi Esteban, there is this field from zendesk support that has some data in the project we are using, it doesn't appear con JQL and need to have it on reports.

I've been looking but I am not used to eazyBi.

So I'm not really sure if this helps me. Do you understand me?

Thank you for your comment.

Cheers.

[Dima]

Hi @Aarón López ITERA , do you find a solution ?

[Aarón López ITERA]

No, sorry @Dima