Turn reported suspicious mail into Jira tickets

Whenever new mail arrives in Gmail, the messages your people have reported as suspicious are found by a search you choose, and each one becomes a ticket in the Jira project your responders already work from.

Every line of the ticket is copied from the message: the sender address, the name shown, whether that address is on one of your own domains, the subject, the hosts its links point to and the Gmail message id. Nothing is rated or scored: a conclusion would be invention. It does note when a name you list as commonly impersonated appears in the sender name or subject while the address is not yours, and it records only the host part of each link. One ticket per sender, not per report: later reports of the same sender become comments on the ticket already open. A message is labelled as triaged only after its ticket is written, so a failed run repeats a report rather than losing it.

You need a Gmail label or search that identifies reported mail and a Jira project for the tickets.

GmailWhen an email arrives.When an email arrives.

Set reportSearch to blank.Set reportSearch to blank.

Set jiraProjectKey to blank.Set jiraProjectKey to blank.

Set ourDomains to blank.Set ourDomains to blank.

Set impersonatedNames to blank.Set impersonatedNames to blank.

Set triagedLabel to Reported mail triaged.Set triagedLabel to Reported mail triaged.

Set issueType to Task.Set issueType to Task.

Set maxReportsPerRun to 10.Set maxReportsPerRun to 10.

Join a list of [reportSearch, -label:, @triagedLabel, ] with nothing and save it as reportQuery.Join a list of [reportSearch, -label:, @triagedLabel, ] with nothing and save it as reportQuery.

Set reportLimit to maxReportsPerRun multiplied by 1.Set reportLimit to maxReportsPerRun multiplied by 1.

Join a list of [,, ourDomains with "Join a list of [,, ourDomains with replaced by , with replaced by in lowercase, ,] with nothing and save it as ourDomainList.

Split impersonatedNames with "Split impersonatedNames with replaced by , by , and save it as nameList.

GmailList emails matching reportQuery. Save it as reported.List emails matching reportQuery. Save it as reported.

Set handledCount to 0.Set handledCount to 0.

Set ticketCount to 0.Set ticketCount to 0.

Set commentCount to 0.Set commentCount to 0.

For each message in reported.emails:For each message in reported.emails:

If handledCount is greater than or equal to reportLimitIf handledCount is greater than or equal to reportLimit

Yes

Stop repeating.Stop repeating.

No

No steps

If a step below fails, save the error as reportErrorIf a step below fails, save the error as reportError

Try

Trim message.id and save it as messageId.Trim message.id and save it as messageId.

If messageId is emptyIf messageId is empty

Yes

Skip to the next item.Skip to the next item.

No

No steps

Trim message.from and save it as fromHeader.Trim message.from and save it as fromHeader.

If fromHeader is emptyIf fromHeader is empty

Yes

Show in the output: A reported message arrived with no sender address; skipped..Show in the output: A reported message arrived with no sender address; skipped..

Skip to the next item.Skip to the next item.

No

No steps

Set addressPart to fromHeader.Set addressPart to fromHeader.

Set displayName to blank.Set displayName to blank.

If fromHeader contains <If fromHeader contains <

Yes

Get the first item of the last item of fromHeader split by < split by > and save it as addressPart.Get the first item of the last item of fromHeader split by < split by > and save it as addressPart.

Trim the first item of fromHeader split by < with replaced by " and save it as displayName.Trim the first item of fromHeader split by < with replaced by " and save it as displayName.

No

No steps

Make addressPart with replaced by with \ replaced by with replaced by " lowercase and save it as senderKey.Make addressPart with replaced by with \ replaced by with replaced by " lowercase and save it as senderKey.

If senderKey doesn't contain @If senderKey doesn't contain @

Yes

Show in the output: a list of [The From line of message , messageId, holds no address, so there is nothing to group it by; skipped.] joined by nothing.Show in the output: a list of [The From line of message , messageId, holds no address, so there is nothing to group it by; skipped.] joined by nothing.

Skip to the next item.Skip to the next item.

No

No steps

If senderKey contains :// or senderKey contains /If senderKey contains :// or senderKey contains /

Yes

Show in the output: a list of [The From line of message , messageId, is not shaped like an address; skipped.] joined by nothing.Show in the output: a list of [The From line of message , messageId, is not shaped like an address; skipped.] joined by nothing.

Skip to the next item.Skip to the next item.

No

No steps

If the length of senderKey is greater than 120If the length of senderKey is greater than 120

Yes

Show in the output: a list of [The sender address on message , messageId, is too long to use as a ticket title; skipped.] joined by nothing.Show in the output: a list of [The sender address on message , messageId, is too long to use as a ticket title; skipped.] joined by nothing.

Skip to the next item.Skip to the next item.

No

No steps

Get the last item of senderKey split by @ and save it as senderDomain.Get the last item of senderKey split by @ and save it as senderDomain.

Check whether ourDomainList contains a list of [,, senderDomain, ,] joined by nothing and save it as ownDomain.Check whether ourDomainList contains a list of [,, senderDomain, ,] joined by nothing and save it as ownDomain.

Set subject to (no subject).Set subject to (no subject).

If a step below fails, save the error as subjectMissingIf a step below fails, save the error as subjectMissing

Try

Trim message.subject and save it as subjectText.Trim message.subject and save it as subjectText.

If subjectText isn't empty and subjectText is not nullIf subjectText isn't empty and subjectText is not null

Yes

Change subject to subjectText.Change subject to subjectText.

No

No steps

On error

Show in the output: A reported message arrived with no subject..Show in the output: A reported message arrived with no subject..

For each subjectLink in all web addresses in subject:For each subjectLink in all web addresses in subject:

Replace subjectLink with [link removed] in subject and save it as subject. Replace subjectLink with [link removed] in subject and save it as subject.

If subject contains ://If subject contains ://

Yes

Change subject to (subject withheld: it carried a web address).Change subject to (subject withheld: it carried a web address).

No

No steps

Set bodyText to blank.Set bodyText to blank.

If a step below fails, save the error as bodyMissingIf a step below fails, save the error as bodyMissing

Try

Trim message.body and save it as bodyValue.Trim message.body and save it as bodyValue.

If bodyValue is not nullIf bodyValue is not null

Yes

Change bodyText to bodyValue.Change bodyText to bodyValue.

No

No steps

On error

Show in the output: A reported message arrived with no readable text..Show in the output: A reported message arrived with no readable text..

Start an empty list and save it as namesUsed.Start an empty list and save it as namesUsed.

For each candidate in nameList:For each candidate in nameList:

Trim candidate and save it as nameText. Trim candidate and save it as nameText.

If nameText is empty or nameText is null If nameText is empty or nameText is null

Yes

Skip to the next item.Skip to the next item.

No

No steps

Check whether displayName contains nameText (ignoring case) and save it as inName. Check whether displayName contains nameText (ignoring case) and save it as inName.

Check whether subject contains nameText (ignoring case) and save it as inSubject. Check whether subject contains nameText (ignoring case) and save it as inSubject.

If inName is yes or inSubject is yes and ownDomain is no If inName is yes or inSubject is yes and ownDomain is no

Yes

Add nameText to the end of namesUsed and save it as namesUsed.Add nameText to the end of namesUsed and save it as namesUsed.

No

No steps

Set nameLine to None of the names in 'impersonatedNames' appear in the sender name or the subject..Set nameLine to None of the names in 'impersonatedNames' appear in the sender name or the subject..

If namesUsed isn't emptyIf namesUsed isn't empty

Yes

Join a list of [Names from 'impersonatedNames' that appear in the sender name or the subject: , namesUsed joined by , , . The address itself is not on one of your own domains.] with nothing and save it as nameLine.Join a list of [Names from 'impersonatedNames' that appear in the sender name or the subject: , namesUsed joined by , , . The address itself is not on one of your own domains.] with nothing and save it as nameLine.

No

No steps

Start an empty list and save it as linkHosts.Start an empty list and save it as linkHosts.

Set linkCount to 0.Set linkCount to 0.

For each linkUrl in all web addresses in bodyText:For each linkUrl in all web addresses in bodyText:

If linkCount is greater than or equal to 8 If linkCount is greater than or equal to 8

Yes

Stop repeating.Stop repeating.

No

No steps

Join the items from index 1 onward of linkUrl split by :// with :// and save it as afterScheme. Join the items from index 1 onward of linkUrl split by :// with :// and save it as afterScheme.

Get the first item of afterScheme split by / and save it as hostPart. Get the first item of afterScheme split by / and save it as hostPart.

Get the first item of hostPart split by ? and save it as hostPart. Get the first item of hostPart split by ? and save it as hostPart.

Get the first item of hostPart split by # and save it as hostPart. Get the first item of hostPart split by # and save it as hostPart.

Get the last item of hostPart split by @ and save it as hostPart. Get the last item of hostPart split by @ and save it as hostPart.

Get the first item of hostPart split by : and save it as hostPart. Get the first item of hostPart split by : and save it as hostPart.

Make hostPart lowercase and save it as linkHost. Make hostPart lowercase and save it as linkHost.

If linkHost is empty or linkHost doesn't contain . or linkHost contains : or… If linkHost is empty or linkHost doesn't contain . or linkHost contains : or…

Yes

Skip to the next item.Skip to the next item.

No

No steps

If linkHosts contains linkHost If linkHosts contains linkHost

Yes

Skip to the next item.Skip to the next item.

No

No steps

Add linkHost to the end of linkHosts and save it as linkHosts. Add linkHost to the end of linkHosts and save it as linkHosts.

Add 1 to linkCount. Add 1 to linkCount.

Set hostLine to No web addresses were found in the text of the message..Set hostLine to No web addresses were found in the text of the message..

If linkHosts isn't emptyIf linkHosts isn't empty

Yes

Join a list of [Hosts the message text links to: , linkHosts joined by , , . Only the host part of each link is recorded here; the rest of each address is left out of the ticket on purpose, because it can carry a code that identifies the person who was written to.] with nothing and save it as hostLine.Join a list of [Hosts the message text links to: , linkHosts joined by , , . Only the host part of each link is recorded here; the rest of each address is left out of the ticket on purpose, because it can carry a code that identifies the person who was written to.] with nothing and save it as hostLine.

No

No steps

Join a list of [[sender: , senderKey, ]] with nothing and save it as marker.Join a list of [[sender: , senderKey, ]] with nothing and save it as marker.

Join a list of [project = , @jiraProjectKey, AND summary ~ , @senderKey, ORDER BY created DESC] with nothing and save it as jql.Join a list of [project = , @jiraProjectKey, AND summary ~ , @senderKey, ORDER BY created DESC] with nothing and save it as jql.

JiraFind issues matching jql. Save it as candidates.Find issues matching jql. Save it as candidates.

Set openTicket to blank.Set openTicket to blank.

For each issue in candidates.issues:For each issue in candidates.issues:

Trim issue.summary and save it as issueSummary. Trim issue.summary and save it as issueSummary.

If issueSummary contains marker If issueSummary contains marker

Yes

Trim issue.key and save it as issueKey.Trim issue.key and save it as issueKey.

If issueKey isn't empty and issueKey is not nullIf issueKey isn't empty and issueKey is not null

Yes

Change openTicket to issueKey.Change openTicket to issueKey.

Stop repeating.Stop repeating.

No

No steps

No

No steps

Shorten subject to 100 characters and save it as subjectShort.Shorten subject to 100 characters and save it as subjectShort.

Set ownDomainLine to The address is not on one of the domains listed in 'ourDomains'..Set ownDomainLine to The address is not on one of the domains listed in 'ourDomains'..

If ownDomain is yesIf ownDomain is yes

Yes

Change ownDomainLine to The address is on one of the domains listed in 'ourDomains'..Change ownDomainLine to The address is on one of the domains listed in 'ourDomains'..

No

No steps

Set nameShown to displayName.Set nameShown to displayName.

If displayName is emptyIf displayName is empty

Yes

Change nameShown to (none).Change nameShown to (none).

No

No steps

Join a list of [Sender address: , senderKey, "Join a list of [Sender address: , senderKey, Name shown on the message: , nameShown, Sender domain: , senderDomain, , ownDomainLine] with nothing and save it as sectionSender.

Set sectionSubject to Subject as received: subjectShort.Set sectionSubject to Subject as received: subjectShort.

Set sectionNames to nameLine.Set sectionNames to nameLine.

Set sectionHosts to hostLine.Set sectionHosts to hostLine.

Join a list of [Gmail message id: , messageId, "Join a list of [Gmail message id: , messageId, Picked up on: , today's date] with nothing and save it as sectionIds.

Set sectionNote to Every line above is copied from the message itself. Nothing here reads the body into the ticket, and no step decides what the message is - that is the reader's job..Set sectionNote to Every line above is copied from the message itself. Nothing here reads the body into the ticket, and no step decides what the message is - that is the reader's job..

Join a list of [sectionSender, sectionSubject, sectionNames, sectionHosts, sectionIds, sectionNote] with a blank line and save it as ticketBody.Join a list of [sectionSender, sectionSubject, sectionNames, sectionHosts, sectionIds, sectionNote] with a blank line and save it as ticketBody.

For each bodyLink in all web addresses in ticketBody:For each bodyLink in all web addresses in ticketBody:

Replace bodyLink with [link removed] in ticketBody and save it as ticketBody. Replace bodyLink with [link removed] in ticketBody and save it as ticketBody.

If ticketBody contains ://If ticketBody contains ://

Yes

Change ticketBody to The details of this report carried a web address and were left out. Open the message in the mailbox instead..Change ticketBody to The details of this report carried a web address and were left out. Open the message in the mailbox instead..

No

No steps

If openTicket isn't emptyIf openTicket isn't empty

Yes

JiraComment on openTicket: 'Another message from this sender was reported.Comment on openTicket: 'Another message from this sender was reported. ticketBody'.

Add 1 to commentCount.Add 1 to commentCount.

No

JiraCreate the issue 'a list of [Reported mail , marker, , subjectShort] joined by nothing' in jiraProjectKey.Create the issue 'a list of [Reported mail , marker, , subjectShort] joined by nothing' in jiraProjectKey.ticketBody

Add 1 to ticketCount.Add 1 to ticketCount.

GmailAdd label triagedLabel to email messageId.Add label triagedLabel to email messageId.

Add 1 to handledCount.Add 1 to handledCount.

On error

Show in the output: Could not raise a ticket for one reported message: reportError as text.Show in the output: Could not raise a ticket for one reported message: reportError as text.

Show in the output: a list of [Reports handled: , handledCount as text, (new tickets: , ticketCount as text, , added to a ticket already open: , commentCount as text, )] joined by nothing.Show in the output: a list of [Reports handled: , handledCount as text, (new tickets: , ticketCount as text, , added to a ticket already open: , commentCount as text, )] joined by nothing.