SQL query - Find projects with less than x amount of issues within them

[Casper Hjorth Christensen]

Hi Community

I'm working an automated way, to inform / ask our "projects leads" if a project can be deleted.

I've created a powershell script to go through an excel sheet, created on the basis of:

<JIRA INSTANCE>/secure/project/BrowseProjects.jspa?s=view_projects#

-> Export to Excel

The script draw out the following Data from the excel

• Lead Email
• Lead Display Name
• Project Name
• Project Key
• Issue Count

based on this I send an email to our project leads if the issue count is lower than #.

Now I can't find a way to automate the download of this file to the localhost - we are on JIRA Server.

----------------- EDITED TEXT ---------------------------------------------------------

If you know what to invoke to draw out this report, please do let me know, could be something like the following:

Invoke-WebRequest -UseBasicParsing "http://localhost:8080/rest/api/2/reindex?os_username=% USERNAME % &os_password=% PASSWORD % " -ContentType "application/json" -Method POST -OutFile "C:\ % WHERE TO STORE THE FILE %

----------------- END OF EDITED TEXT ------------------------------------------------

So I figured I could get help generating the SQL query required to grab this data directly from the DB, which I can then convert to Excel or use directly in my Powershell, from a scheduled task.

Best Regards

Casper

The Powershell script is here:

# Send email to JIRA Project Lead, based on projects with no issues in them

# Import needed modules, and install if needed
If(-not(Get-InstalledModule ImportExcel -ErrorAction silentlycontinue)){
Install-Module ImportExcel -Confirm:$False -Force
}

# What CSV file should users and project name be drawn from?
$XLSXFileLocation = Read-Host "Please provide the XLSX file location, to draw data from"
#When turned to Scheduled task - hardcode file name + path


# Send email function
function SendNotification
{

# Define local Exchange server info for message relay. 
# Ensure that any servers running this script have permission to relay.
$smtpserver = “smtp.gmail.com”
$FromAddress = "@gmail.com"
$username = ""
$Password = "" 


# CREATE OBJECTS TO BE USED
$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer, 587)


# Enable SSL
$smtp.EnableSsl = $true 

# Adding Credentials to send email
$smtp.Credentials = New-Object System.Net.NetworkCredential("$username", $Pass); # Put username without the @GMAIL.com or – @gmail.com

# Where should the email be sent to
# Use variable previously HARDCODED
$msg.From = $FromAddress
# Send email to, will be defined by CSV file
$msg.To.Add($ToAddress)
$msg.Bcc.Add("") #Insert Service Desk Email to be notified

# Allow HTML code within Body 
$msg.IsBodyHTML = $true

# Set Subject - HARDCODED, could be set to Variable
# Set Project name from CSV File
$msg.Subject = "Important: Is this $Project still in use?"

# Add Text to the email Body, will be defined later to allow for individual variables
# E.g. Project Names, Project Lead Name, etc.
$msg.Body = $EmailBody

# Send email
$Smtp.Send($msg)
}

# Import user list and information from .CSV file
# CSV File Location is drawn from Read-Host at top
# Defining what collums to draw data from
$XLSXFile = Import-Excel -path "$XLSXFileLocation"
if($XLSXFile){write-host "file imported"}


# Send Email to each Project Lead in the list
foreach ($x in $XLSXFile){#Start foreach

if($x.'issue count' -lt 10){#Start IF

$ToAddress = $x.'Lead Email'

$LeadDisplayName = $x.'Lead display name'

$ProjectName = $x.Name

$ProjectKey = $x.Key

$IssueCount = $x.'Issue count'

# Add Email body @" = Start of Body
# "@ = End of Body
# There can be no "String" infront of "@
# HTML Body can be generated here: https://html-online.com/editor/
$EmailBody = @"
"@
# Write to Console, where the email is headed
Write-Host "Sending email to ($ProjectName) ($ToAddress)" -ForegroundColor Yellow
SendNotification
}#End IF
}#End Foreach

[fran garcia gomera · Accepted solution]

Think this could work

SELECT us.email_address, us.display_name, pr.pname, pr.pkey, count(ji.id) 
FROM jiraissue ji 
left join project pr on pr.id = ji.project 
left join cwd_user us on us.lower_user_name = pr.LEAD

group by pr.pkey;

[Atlassian Mac]

deleted

[Casper Hjorth Christensen]

Hi @fran garcia gomera 

Thanks for taking the time to respond.

Unfortunately the query results in an error:

Msg 8120, Level 16, State 1, Line 1
Column 'cwd_user.email_address' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I'm of course running the query towards our TEST server and DB, tho it is a near-complete replica of our PROD environment.

The test environment is v8.4.2

[fran garcia gomera]

it's working in my mySQL server, which DB are you using? have you the server ONLY_FULL_GROUP_BY enabled?

try this anyway

SELECT us.email_address, us.display_name, pr.pname, pr.pkey, count(ji.id) 
FROM jiraissue ji 
left join project pr on pr.id = ji.project 
left join cwd_user us on us.lower_user_name = pr.LEAD

group by pr.pkey, us.email_address, us.display_name, pr.pname;

[Casper Hjorth Christensen]

That works - Thanks

a little BUT, nearly each line is doubled, shown by the project key.

We are currently using a Microsoft SQL 2012 server... :S 

Unfortunately i'm not an DB admin or SQL sysadmin - due to my lack of knowledge with SQL, i've asked not to have these rights..

But as i'm unable to run the first query provided, i would say "Yes, ONLY_FULL_GROUP_BY is enabled"

----- EDIT -----

Oh - And how do I get a name for the last column? easier to call a column when its named ;)

[fran garcia gomera]

can you  try this so we can see the username of the LEAD of every project, 

SELECT us.email_address, us.display_name, us.lower_user_name, pr.LEAD, pr.pname, pr.pkey, count(ji.id) 
FROM jiraissue ji 
left join project pr on pr.id = ji.project 
left join cwd_user us on us.lower_user_name = pr.LEAD

group by pr.pkey, us.email_address, us.display_name, pr.pname,us.lower_user_name, pr.LEAD;

[Casper Hjorth Christensen]

Here you go :)

[fran garcia gomera]

seems like you have Johnny Klostergaard twice in the database, with different display_name but with the same user_name (jbk) and the same email (jbk@...)

the query is going to give you one row for every matching lower_user_name.

I think you should solve that by deleting the duplicates or you can just get the first one using MIN(display_name) or MAX(display_name to get only one row per project.

Something like this should do the work (can't test it because i don't have duplicates).

SELECT us.email_address, MAX(us.display_name), us.lower_user_name, pr.LEAD, pr.pname, pr.pkey, count(ji.id) 
FROM jiraissue ji 
left join project pr on pr.id = ji.project 
left join cwd_user us on us.lower_user_name = pr.LEAD

group by pr.pkey, us.email_address, pr.pname,us.lower_user_name, pr.LEAD;

probably there is a fancier way to do the SQL, but i think the idea is there

[Casper Hjorth Christensen]

My "User Management" interface in JIRA, only shows him once.. dont know why there would be two.

That seemed to solve it - Thanks a bunch!

[Casper Hjorth Christensen]

I will post the full Powershell script once done ;)

Someone out there might need it as well.

we are currently at 1452 projects... which is why I would like to automate some of the cleanup, or notification email part at least.

[fran garcia gomera]

That will be very helpful.

Good luck finishing it.

PS. Probably the duplicates are on different directories, jira would only show you the first one. You can check that by adding the column 'directory_id' to the query (don't forget to add it in the group by clause or it won't work in your DB

[Casper Hjorth Christensen]

Indeed the duplicate is from the JIRA Internal DB

Thanks a bunch, i will let you know once its done

[Casper Hjorth Christensen]

Both scripts are now done.

Any suggestions as to where to upload?

I'll insert the two code blocks here.

First run the "ExportSQLtoExcel.ps1", this will extract data from the SQL, and place it in an Excel on C:\Temp\project-Statistics.xlsx

Ensure that "ExportSQLtoExcel.ps1" is executed with an account, that have Read rights on the SQL.

The run "send email to jira project lead based on XLSX.ps1"

This will grab the XLSX file from C:\Temp\Project-Statistics.xlsx, and send emails to all Leads, where the issue count is less than X.

The X will be defined upon executing "send email to jira project lead based on XLSX.ps1"

"send email to jira project lead based on XLSX.ps1" is currently created to be executed manually, and will ask for a number -> Issue Count with projects, and date + time you want to hear back from your project leads.

Remember before executing the script, to edit it with and From Address, Bcc Address (To create a case in your ServiceDesk,) Username and Password of the From Address. 

"send email to jira project lead based on XLSX.ps1" could be changed to look at current date and then add e.g. +1 month, and to always look at a specified amount of "Less than" Issues Count, to allow for full automation.

#"send email to jira project lead based on XLSX.ps1"

# Send email to JIRA Project Lead, based on projects with # amount of issues in them

# Import needed modules, and install if needed
If(-not(Get-InstalledModule ImportExcel -ErrorAction silentlycontinue)){
Install-Module ImportExcel -Confirm:$False -Force
}

# What XLSX file should users and project name be drawn from?
# Match proper provided FullName path for XLSX file
Do {$XLSXFileLocation = Read-Host "Please provide the XLSX file location, to draw data from"} 
Until ((Test-Path $XLSXFileLocation) -and $XLSXFileLocation -match '.xlsx')


# Ensure that the user inputs an Integer as the Issue Count
while(1)
{
try{
[uint16]$LessThanIssueCount = Read-Host -Prompt "Send email to Project Leads, where Issue Count is Less Than"

write-Host "You are about to send emails to all project leads, where the Project has Less Than "" $LessThanIssueCount "" Issues within it"

$selection = Read-Host "Contiue? Yes(Default, Enter) / No"
Switch($selection){
'Yes'{
Write-Host "You have selected YES, the script will continue"
break}
'No'{
# Set the variable to something not an integer
$LessThanIssueCount = 'a'
}
Default {
Write-Host "You have selected YES, the script will continue"
break}
}#End Switch statement

break

}# End Try
catch{
Write-Host "Please insert a number"
}
}# End While

#Read the desired disable date
do
{
Write-Host "By what date do you want to hear back from the Project Leads?"
$date= read-host "Use the format (i.e.: '25/12/2012 09:00', '25 oct 2012 9:00'; If only a date is entered, the time will be set to 00:00):"

$date = $date -as [datetime]

if (!$date) {
"Not A valid date and time"
}
} while ($date -isnot [datetime])




#$XLSXFileLocation = "C:\temp\projectTest.xlsx" # Created for testing purposes


# Send email function
function SendNotification
{

# Define local Exchange server info for message relay. 
# Ensure that any servers running this script have permission to relay.
$smtpserver = “smtp.gmail.com”
$username = ""
$Pass = ""
$FromAddress = ""
$BCCAddress = ""


# CREATE OBJECTS TO BE USED
$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer, 587)


# Enable SSL
$smtp.EnableSsl = $true 

# Adding Credentials to send email
$smtp.Credentials = New-Object System.Net.NetworkCredential("$username", $Pass); # Put username without the @GMAIL.com or – @gmail.com - if enterprise with separate domain use full email, e.g. support@mydomain.com

# Where should the email be sent to
# Use variable previously HARDCODED
$msg.From = $FromAddress
# Send email to, will be defined by CSV file
$msg.To.Add($ToAddress)
$msg.Bcc.Add($BCCAddress)

# Allow HTML code within Body 
$msg.IsBodyHTML = $true

# Set Subject - HARDCODED, could be set to Variable
# Set Project name from CSV File
$msg.Subject = "Important: Is this $Project still in use?"

# Add Text to the email Body, will be defined later to allow for individual variables
# E.g. Project Names, Project Lead Name, etc.
$msg.Body = $EmailBody

# Send email
$Smtp.Send($msg)
}

# Import user list and information from .CSV file
# XLSX File Location is drawn from Read-Host at top
# Can be modified to include specific columns to draw data from
# 
$XLSXFile = Import-Excel -path "$XLSXFileLocation"
if($XLSXFile){write-host "file imported"}


# Send Email to each Project Lead in the list
foreach ($x in $XLSXFile){#Start foreach

if($x.'issue count' -lt $LessThanIssueCount){#Start IF

$ToAddress = $x.'Lead Email'

$LeadDisplayName = $x.'Lead display name'

$ProjectName = $x.Name

$ProjectKey = $x.Key

$IssueCount = $x.'Issue count'

# Add Email body @" = Start of Body
# "@ = End of Body
# There can be no "String" infront of "@
# HTML Body can be generated here: https://html-online.com/editor/
$EmailBody = @"
<p>Dear $LeadDisplayName,<br />The Atlassian team is in the process of cleaning up our JIRA environment.<br />In this process, we have found that the project $ProjectName have less than $LessThanIssueCount issues within it, <br />and therefore doesn't seem to be in use.</p>
<p>May we delete this project:</p>
<table>
<tbody>
<tr>
<td>Project Lead:</td>
<td>$LeadDisplayName</td>
</tr>
<tr>
<td>Project Name:</td>
<td>$ProjectName</td>
</tr>
<tr>
<td>Project Key:</td>
<td>$ProjectKey</td>
</tr>
<tr>
<td>Issue Count:</td>
<td>$IssueCount</td>
</tr>
</tbody>
</table>
<p><br />If we haven't heard from you by the $date, we will proceed to delete the project.</p>
<p>Please advise us on email by replying to this email.</p>

"@
# Write to Console, where the email is headed
Write-Host "Sending email to ($ProjectName) ($ToAddress)" -ForegroundColor Yellow
SendNotification
}#End IF
}#End Foreach

#ExportSQLtoExcel.ps1

#some variables
$serverName = "<SQL Server Name>"; # Fill in with the SQL Server Name
$databaseName = "'<JIRA DATABASE>'"; # Fill in with the desired JIRA database to run this extract against.

#the save location for the new Excel file
$filepath = "C:\temp\Project-Statistics.xlsx";

# Check if file exist, if it does, delete it
# File contains previous output of Project Statistics
$file = "C:\temp\Project-Statistics.xlsx"
If(test-path $file)
{
Remove-Item -Path $file -Recurse
}
#create excel object

$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.add()
$worksheetA = $workbook.Worksheets.Add()

#create byUser worksheet
$sheet1 = $workbook.worksheets.Item(1)
$sheet1.name = "Project-Statistics"

#create a Dataset to store the DataTable 
$dataSet = new-object "System.Data.DataSet" "Project-Statistics"

#create a Connection to the SQL Server database
$cn = new-object System.Data.SqlClient.SqlConnection "server=$serverName;database=$databaseName;Integrated Security=sspi"
$query= "SELECT us.email_address, MAX(us.display_name)Lead_Display_Name, us.lower_user_name, pr.LEAD, pr.pname, pr.pkey, count(ji.id)Issue_Count FROM jiraissue ji left join project pr on pr.id = ji.project left join cwd_user us on us.lower_user_name = pr.LEAD group by pr.pkey, us.email_address, pr.pname,us.lower_user_name, pr.LEAD;"

#Create a SQL Data Adapter to place the resultset into the DataSet
$dataAdapter = new-object "System.Data.SqlClient.SqlDataAdapter" ($query, $cn)
$dataAdapter.Fill($dataSet) | Out-Null

#close the connection
$cn.Close()

$dataTable = new-object "System.Data.DataTable" "Principals"
$dataTable = $dataSet.Tables[0]
#assign column names
$sheet1.cells.item(1, 1) = "Lead Email"
$sheet1.cells.item(1, 2) = "Lead Display Name"
$sheet1.cells.item(1, 3) = "Lead Username Lower Case"
$sheet1.cells.item(1, 4) = "Lead Username"
$sheet1.cells.item(1, 5) = "Project Name"
$sheet1.cells.item(1, 6) = "Project Key"
$sheet1.cells.item(1, 7) = "Issue Count"

#iterate through every DataTable line item and insert to the Excel worksheete

##Note: starts at 2 as 1 is the column headers

$x=2

$dataTable | FOREACH-OBJECT{
$sheet1.cells.item($x, 1) = $_.email_address
$sheet1.cells.item($x, 2) = $_.Lead_Display_Name
$sheet1.cells.item($x, 3) = $_.lower_user_name
$sheet1.cells.item($x, 4) = $_.LEAD
$sheet1.cells.item($x, 5) = $_.pname
$sheet1.cells.item($x, 6) = $_.pkey
$sheet1.cells.item($x, 7) = $_.Issue_Count
$x++
}

$range1 = $sheet1.UsedRange
$range1.EntireColumn.AutoFit()

#save excel worksbook
$excel.ActiveWorkbook.SaveAs("$filepath ")
$excel.quit()