Solved

liquid filter to extract information from an email body into fields


Userlevel 4
Badge +6

I’ve been trying, like really stressing, to find a way to take an email sent into my FreshService, pull out details from a consistently formatted body, and populate fields in a ticket with the data.

 

The email body consists of the following:

 

Name : JASON TEST
SamAccountName : JTEST
DistinguishedName : CN=JASON TEST,OU=USERS,OU=TEST,DC=contoso,DC=com
SID : S-1-5-21-953425057-2096312333-931750244-25872
Description : CORPORATE - GENERIC
EmployeeID : 79998
employeeType : Salaried
Enabled : True
accountExpires : 1/11/2023 12:00:00 AM

 

I am trying to

  1. extract the text after Name: as a string 
  2. extract the number after EmployeeID: as a numeral
  3. extract the data after accountExpires: as a date

and put each of those extractions into a corresponding service request field.

 

I was given this expression, which when it sees a hashtag (#req) in the email body finds the email address of the original sender from a forwarded email.

substring( '{{ticket.description}}', indexOf( '{{ticket.description}}','#req',0)+5, indexOf('{{ticket.description}}','.com',indexOf('{{ticket.description}}','#req',0)+5) +4)

 

and I will be hog tied if I can deconstruct this or make any sense of the “index of comments”

 

Would anyone be willing to help me figure this mess out?

 

 

icon

Best answer by BrynCYDEF 20 January 2023, 22:58

View original

14 replies

Userlevel 5
Badge +9

Hi @JTAVOLARO - stress no more I have the goods to share with you!

I will post here shortly my automation which pulls the formatted text off tickets that are being emailed to our FS - then the auto takes that extracted text and puts it into a few custom fields.

Some of the canned fields (Set Status as {{expression}} ) can also accept that extracted text - some cannot - the Company field and Requester field are not an option through the Automation Workflow

Userlevel 4
Badge +6

Oh gosh, yes PLEASE  :)

 

Userlevel 5
Badge +9

substring( '{{ticket.description}}', indexOf( '{{ticket.description}}','#req',0)+5,

→ this says to locate the 1st occurrence of   #req  then count forward by 5 chars

indexOf('{{ticket.description}}','.com',indexOf('{{ticket.description}}','#req',0)+5) +4)

 

Actually IDEK what on earth they were trying to do there.

 

What I would recommend is to break into 3 separate Expression nodes - 1 for each bit of extracted data

#1 Name

Add an Expression Node in your workflow

This code will extract whatever follows,    Name :     up to the 1st occurrence of SamAccountName.

Pls note the numeric value i.e. 7 must include the spaces Name_:_  which is where the 7 comes from - so whatever text you tell the substring to look for, count all those characters. 

Tested with sample and works as you can see below.

substring('{{ticket.description}}', (indexOf('{{ticket.description}}','Name : ', 0)+7), indexOf('{{ticket.description}}','SamAccountName',0))

 

Step 2: Create Action Node to assign the Expression Result to the field of your choice

 

Step 3:  Click Result

 

It should look like this

 

Userlevel 5
Badge +9

Then add the next 2 Expression nodes to search for the other data and set the Expected Output accordingly i.e. numeric, string, boolean.

To test the expression, copy and paste the sample into the Value field 

Then add the Action node at the end to populate all the fields as desired.

Final thought - Giving the nodes really clear names will help a lot at the Action Node step!

 

HTH @JTAVOLARO 

Bryn :-)

Userlevel 4
Badge +6

I’m jumping up and down…. you rock, Bryn, THANK YOU!

 

Userlevel 5
Badge +9

Glad I could help JT!

I spent literally HOURS deciphering this stuff so I’m glad to see others benefiting from that effort!  😁

Do LMK if you have any issues or further questions!

Bryn at CYDEF.ca

Userlevel 4
Badge +6

So, I was able to write the extraction p[ieces just fine thanks to your excellent writeup…. But for Actions, I do not have “set error value” as an option.

I’m thinking I need to do a web request to update fields?  For that matter, I need to add some fields as well.

In any case, you’ve given me a heck of a launch, and I am grateful!

 

Userlevel 5
Badge +9

Very glad I could help @JTAVOLARO 

[Error Value] is a custom field for my ticket properties in my FS - so you would need to create your own custom fields to receive the extracted data.

Where it might get sticky is if you are wanting to extract the data from the email/ticket and then update a USER account (i.e. Requester, Contact).

That I’m not sure can be done … @zachary.king @daniel.soderlund @afautley 

Thoughts? 

Userlevel 4
Badge +6

ah… so then I need to have the custom fields in the ticket form first….

I’m thinking then that I should make a service request form with my fields, and do an API PUT to launch a service request where I can enter the extracted data….

Userlevel 5
Badge +8

Great work @BrynCYDEF ! 

 

definitely could use it to create a user account… you can use results from expressions in the ad orchestrator app so this would work.

 

 

 

if you have an email coming in with all the details on it then what you have there could kick start all the automation you need, no need for an SR to kick off further automation. only need an SR if you need manual tasks doing after orchestration i think?

 

Userlevel 4
Badge +6

So now I’m faced with a challenge on how to 

  • change the emailed ticket from an incident to a service request, and add a requested item to the service request with custom fields
    • OR
  • add custom fields on the fly to the incident

AND THEN

  • paste the extracted details into custom fields

 

Is this a webhook? an API? Do I need an orchestrator?

Where does one find what the endpoint of an API should be? or a callback URL?

(Why do the KB pages always have amazing examples for everything except what I’m trying to do?)

 

I wish I could just tell the system that when a certain type of email comes in to use a specific template

OK, I’m done crying…. are there any actual training courses out there, or is it all trial and error?

Userlevel 5
Badge +9

Lol - I think we all just come here to whinge and then end up helping and learning from each other!

So easy question first - lol - in the Action Node of the WorkFlow you can just set the Type whichever way you need

 

In the Automator WorkFlow, you can set pretty specific conditions to flow the emails down specific paths to take actions and set fields. 

Here I’m taking an automated email from our proprietary portal and depending on what I find in the email, it streams the ticket into these different flows and then does a bunch of things like:

  • sets the Cat\SubCat\Item based on what’s happened in the portal
  • sends email here and there
  • sets custom fields
  • sets status
  • assigns group & agent

Usually I search on the subject or description field for specific text to use as the Condition - as you can see here by the example.

 

My #1 strong recommendation is to always end on a Yes so all the unmatching move onto the next WorkFlow - you don’t want every ticket to run through this WF.  (trust me on this!!!!! lol)

I’m not too sure what you mean about endpoints of API so I’ll let the others weigh in -- that’s definitely an area I’d like to understand better - webhooks, API, JSON etc

 

Userlevel 4
Badge +6

@BrynCYDEF , you have been absolutely awesome, and I thank you deeply.

 

After some more tinkering and reading through outdated support articles, after using Bryn’s extraction techniques I have a Web Request PUT API call to https://xxxxxx.freshservice.com/api/v2/tickets/{{ticket.id_numeric}} with the following in the Body

{   "email": "jtavolaro@xxxxx.com",   "subject": "Employee Termination: {{E1.result}} {{E2.result}} {{E3.result}}",   "status": 6,   "group_id": 21000044746 }

Testing the web request through the workflow editor, it works wonderfully… but a live test gives me an error that it’s not valid JSON.  

 

Argh… SOOOO CLOSE!

 

Userlevel 4
Badge +6

EUREKA!

I added the sanitize HTML pipe to the placeholders, and got the result I wanted….. to add the extracted information to the subject line of the ticket.  

 

so now the Body in the API call looks like this:

 

{
  "email": "jtavolaro@xxxxx.com",
  "subject": "Termination:  {{E1.result | sanitize_html}} {{E2.result | sanitize_html}} {{E3.result | sanitize_html}} ",
  "status": 6,
  "group_id": 21000044746
}

 

 

Reply