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
# 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
