SQL query - Find projects with less than x amount of issues within them and email the project Lead

Casper Hjorth Christensen November 13, 2019

I've created 2 Powershell scripts for JIRA Server / DataCenter installations.

"ExportSQLtoExcel.ps1"

and

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

 

"ExportSQLtoExcel.ps1" will go through the Database, and extract the following data:

"Lead Email"
"Lead Display Name"
"Lead Username Lower Case"
"Lead Username"
"Project Name"
"Project Key"
"Issue Count"

and list it into an Excel sheet placed at C:\Temp\Project-Statistics.xlsx

 

You then have the option of going through the sheet manually, to see how many issues a single project have, or to see if a project lead have multiple project etc.

whatever you want to use this data for..

 

the second script, "send email to jira project lead based on XLSX.ps1", will grab the Excel file, and prompt you twice.

the first prompt is: "Send email to Project Leads, where Issue Count is Less Than"

it will, of course, check if the "Text" is an integer.

the second prompt is for an email, and for when you want to disable/archive the projects:

By what date do you want to hear back from the Project Leads?"
"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):

Again, it checks to see if its an actual date, and you have the option of inserting a time aswell.

 

Remember before executing any script, to run it through, to understand what it does, and how it does it.

Remember to edit theses scripts, to fit your needs.

e.g."send email to jira project lead based on XLSX.ps1" will need to be edited with an From Address and Bcc Address (To create a case at your ServiceDesk,) Username and Password of the From Address(to be able to send the email). 

"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.

 

I hope you enjoy and can use the input from a newcomer.

The scripts can be found here:

#"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()

 

1 comment

Comment

Log in or Sign up to comment
Erica Moss
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 14, 2019

@Casper Hjorth Christensen Hey, Casper! I'm going to go ahead and move this to the Jira collection where it's most relevant. 😄

Casper Hjorth Christensen November 21, 2019

Sure thing, wasnt sure as to where to post it in the first place

TAGS
AUG Leaders

Atlassian Community Events