Guides - Creating Report Data Within a Runbook (Matching and Merging to Existing Alert Tickets)

Creating Report Data Within a Runbook (Matching and Merging to Existing Alert Tickets)

This guide has multiple versions available:

<div><div><div><div><div><div><div><div><style>p { margin: 0; }span.fr-emoticon.fr-emoticon-img { background-repeat: no-repeat !important; font-size: inherit; height: 1em; width: 1em; min-height: 20px; min-width: 20px; display: inline-block; margin: -0.1em 0.1em 0.1em; line-height: 1; vertical-align: middle; } span.fr-emoticon { font-weight: normal; font-family: "Apple Color Emoji", "Segoe UI Emoji", "NotoColorEmoji", "Segoe UI Symbol", "Android Emoji", "EmojiSymbols"; display: inline; line-height: 0; } blockquote { border-left: solid 2px #5e35b1; color: #5e35b1; margin-left:0; padding-left:5px;}blockquote blockquote{ border-color: #00bcd4; color: #00bcd4;}blockquote blockquote blockquote{ border-color: #43a047; color: #43a047;} table.grid{ border-collapse: collapse;} table.grid td, table.grid th { border: 1px solid #ddd;} .fr-fic.fr-dib{ display: block; margin: 5px auto;}.fr-fic.fr-dib.fr-fir{ text-align: right; margin: 5px 0 5px auto;}.fr-fic.fr-dib.fr-fil{ text-align: left; margin: 5px auto 5px 0;}.fr-fic.fr-dii{ float: none; margin: 5px auto;}.fr-fic.fr-dii.fr-fil{ float: left; margin: 5px auto;}.fr-fic.fr-dii.fr-fir{ float: right; margin: 5px auto;}img.fr-dib.fr-fir { margin-right: 0; text-align: right;}img.fr-dib.fr-fil { margin-left: 0; text-align: left;}img.fr-dib { margin: 5px auto; display: block; float: none;}img.fr-bordered { box-sizing: content-box; border: solid 5px #CCC;}img.fr-shadow { box-shadow: 10px 10px 5px 0px #cccccc;}img.fr-rounded { border-radius: 10px; -moz-border-radius: 10px; -webkit-border-radius: 10px; -moz-background-clip: padding; -webkit-background-clip: padding-box; background-clip: padding-box;}</style><div><style> p { margin: 0; } span.fr-emoticon.fr-emoticon-img { background-repeat: no-repeat !important; font-size: inherit; height: 1em; width: 1em; min-height: 20px; min-width: 20px; display: inline-block; margin: -0.1em 0.1em 0.1em; line-height: 1; vertical-align: middle; } span.fr-emoticon { font-weight: normal; font-family: "Apple Color Emoji", "Segoe UI Emoji", "NotoColorEmoji", "Segoe UI Symbol", "Android Emoji", "EmojiSymbols"; display: inline; line-height: 0; } blockquote { border-left: solid 2px #5e35b1; color: #5e35b1; margin-left: 0; padding-left: 5px; } blockquote blockquote { border-color: #00bcd4; color: #00bcd4; } blockquote blockquote blockquote { border-color: #43a047; color: #43a047; } table.grid { border-collapse: collapse; } table.grid td, table.grid th { border: 1px solid #ddd; } .fr-fic.fr-dib { display: block; margin: 5px auto; } .fr-fic.fr-dib.fr-fir { text-align: right; margin: 5px 0 5px auto; } .fr-fic.fr-dib.fr-fil { text-align: left; margin: 5px auto 5px 0; } .fr-fic.fr-dii { float: none; margin: 5px auto; } .fr-fic.fr-dii.fr-fil { float: left; margin: 5px auto; } .fr-fic.fr-dii.fr-fir { float: right; margin: 5px auto; } img.fr-dib.fr-fir { margin-right: 0; text-align: right; } img.fr-dib.fr-fil { margin-left: 0; text-align: left; } img.fr-dib { margin: 5px auto; display: block; float: none; } img.fr-bordered { box-sizing: content-box; border: solid 5px #CCC; } img.fr-shadow { box-shadow: 10px 10px 5px 0px #cccccc; } img.fr-rounded { border-radius: 10px; -moz-border-radius: 10px; -webkit-border-radius: 10px; -moz-background-clip: padding; -webkit-background-clip: padding-box; background-clip: padding-box; } </style><div>In this guide we will cover:- Creating Report data within a Runbook- Worked Example - have email alerts merge based on alert ID Creating Report Data within a Runbook Report queries can be within integration runbooks to obtain data from your Halo database to be used as part of the runbook.  This guide will cover how to configure and pull database information using report queries from within a runbook as well as a use case of this feature. First navigate to Configuration > Custom Objects > Custom Integrations this is where you can configure runbooks. <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjM1ZGI3NjA5LTZjOTQtNDAxZS1hNjBiLWE5NWZjOTFkN2IzNSJ9.0ScECN_iZD5k0y5M43RApxKnQI5TknUtGZ7AkSzG7KE" class="fr-fic fr-fil fr-dib" style="width: 919px;">Fig 1. Custom integrations page Click into integration runbooks and create a new or choose an existing runbook (at this point you should have authenticated into your own Halo instance, guide: <a href="https://usehalo.com/haloitsm/guides/2267" target="_blank" rel="noopener noreferrer">Authorising API Access into Your Own Halo Instance</a>). Add a step to the runbook with type 'Action' and action type 'Run a SQL query on the Halo database'.  <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImE5YWU1MDMyLTI2ZTgtNDM2ZC04YWU5LTY1ZjdiZjhkZGYzZiJ9.FdY9rvqD_2-6NedYsBQwWuPONF_kTOKRmvkW28OvUZ4" class="fr-fic fr-fil fr-dib" width="1380" style="width: 1382px; height: 689.813px;" height="690">Fig 2. Runbook step to run report (SQL) query When selected you will be able to enter a SQL query against this step in the 'SQL Script/Stored Procedure' field. This query will be run on your database when the runbook reaches this step. Variables can than be created or updated based on the output of the query, these variables can then be used elsewhere in the runbook. Allowing runbook variables to change dynamically based on the current data in your Halo database.  In the figure 3 example I have a query set to run that obtains the ticket ID (FAULTID), the ID of the primary linked asset to this ticket (DID), and the data in the field CFWarrantyExpiry (this is a custom field against assets). The report includes the variable <<TicketID>> which stores the ID of the ticket that triggered the runbook. The query is filtering results limited to this ticket.<img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjFiNDk4N2Q4LTQ1ZmEtNGI0Ni04YmVmLTYzZjQ0OWJiNDdiNyJ9.ceSwFsFINsXC9I21d8ZeLybT6MBFfas4J3i_Uhh_rxs" class="fr-fic fr-fil fr-dib" width="1263" style="width: 1265px; height: 664.811px;" height="665">Fig 3. SQL query to obtain device (asset) information  Note: Runbook level variables can be used as part of SQL queries. I would like to use the data from the field CFWarrantyExpiry elsewhere in my runbook (perhaps to check if the asset is currently still under warranty). So now, a variable will need to be created that stores this information. To do this, scroll down to the 'Runbook-level variable mappings' table and add a new entry, select *Add a new runbook-level* variable to configure a new variable. <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImIwZjdhMWVjLTVlOTYtNDVhNi04MjZjLWRkMzU2YzZmZjU5YyJ9.PytnYeol_GTPjymGlEnuQdt54sIqE59pJqmlcYQemJQ" class="fr-fic fr-fil fr-dib" width="1248" style="width: 1250px; height: 463.545px;" height="464">Fig 4. Add a new runbook level variable The output of the report will be stored in the response object, therefore variables can be configured based on this object and it's properties.  In the below example I have created a variable called ' WarrantyExpiry', which will contain data from the CFWarrantyExpiry property of the response object.  <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjAzOWJiNDVlLWQ5NWYtNGIwNS1iMWEyLTQ5OWUxNmExNjZiOSJ9.E_8WpHs9DiyH9PNxNezIw5Zi8HAayApbvpvvi1B4tLg" class="fr-fic fr-fil fr-dib" width="570" style="width: 572px; height: 726.994px;" height="727">Fig 5. Runbook Level variable I can now call use the data in this variable elsewhere in the runbook by calling <<WarrantyExpiry>>. If the SQL query returns multiple rows, these will be stored in the response object as an array. You can then store the whole response object in a variable to iterate through each array in a following runbook step.  Note: If doing this, ensure the variable has the data type 'Array'.  <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImUxZDQ4Mzk0LTEwYTctNGVhZi1iZjYyLWU3ZDA1Y2EyYjRiZCJ9.SIR3qgjjYlHM83lPTtggaFJzgMt4CvxIGIdawsWwvUc" class="fr-fic fr-fil fr-dib" width="1383" style="width: 1385px; height: 531.616px;" height="532">Fig 6. Array Iteration step Worked Example- have email alerts merge based on alert IDWhen using a custom alerting tool in Halo, you may have multiple tickets come in that should be tied to the same alert. This is achievable by using a mixture of integration runbooks, email rule and ticket custom fields. To begin configuring, create a custom field of entity "Ticket" which will be how we store the 3rd party software's ticket ID. <iframe src="https://scribehow.com/embed/Creating_a_New_Alert_Ticket_ID_Custom_Field__ClS2btiEQ2O1j-jM6AquvA" width="100%" height="640" allowfullscreen="" frameborder="0">                                                   </iframe> Then create an email rule of type "Alert Via Email", which will be used to capture the Alert Ticket ID from the incoming alert email. If you are unfamiliar with email rules and how to match information in the body to a custom field in Halo, please check out this guide: <a href="https://usehalo.com/haloitsm/guides/1910" target="_blank" rel="noopener noreferrer">Email Rules</a>. The ticket type to use for the email rule is likely best set to a ticket type specific to the alerting software you are using.  Set the from address or multiple from addresses via using semi colon (;) separated values. and then match onto the custom field for the Alert Ticket ID. For example the email body may come through like this:<img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjcyNjQwNGY1LWEyYjgtNDA0Ny04ODRkLTlhMmFkODI2MDBmOSJ9.E0g7n6UIwbeXbX3OLMp-q4Rvl0eBvZLXUjQrGxi5xs8" class="fr-fic fr-fil fr-dib">Fig 7. Example email body To set the AlertTicketID field to be populated with the actual ID from the email, we can use the following field mapping on the email rule (mappings will look for the value in either the subject or body of the email). <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImNlNzBmNWUxLTJmNGQtNGU0NC1hNTg0LWU1MGNiMGU1NmEwZSJ9.8izI6OY07iXpa3tSK9uD6m-Wz9kRlNKl974WT12_OvU" class="fr-fic fr-fil fr-dib" style="width: 582px;">Fig 8. Email rule field mapping to populate custom field with data from email This will map our value to our tickets. Make sure the custom field is on the field list of the ticket type. <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6Ijc4OWE1Njk1LWVmYjUtNDEzYi04MTc2LTg0OWIwZWNlMWU4MSJ9.AXX6cAT13eyFBdBSbXMdWUAg4zAU3NziSaQyKfGsjfo" class="fr-fic fr-fil fr-dib" style="width: 657px;"> Fig 9. Custom field on ticket type Now when my email comes in and creates an alert ticket, the custom field will be populated with the ticket ID from the alerting software. <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImIwZTQ1MWM3LWM0OWMtNDRiZi1hZTYwLWY2MjZkN2ZmZGE4NyJ9.zrc1ZgjDIA6bglcP1BIoxOvEhznalfvENJ1_bHerXtA" class="fr-fic fr-fil fr-dib">Fig 10a. Custom field populated with ID from alerting software <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjlkYzJmMzc0LTYwOGItNGZiZS04ZGI0LTM3MDIzM2I4NzMyMiJ9.WtJ7eLnkMhDw1HMYgFnffr3sAszbj6NkoHYV94vA-xE" class="fr-fic fr-fil fr-dib">Fig 10b. Custom field populated with ID from alerting software The use case of having this ID field populate is to create a runbook to make sure that all future tickets where this AlertTicketID is found, is merged into the previous ticket that also had that AlertTicketID. Now that the basic configuration has been configured, the next step is to create the runbook for the new alert tickets that come in with the same AlertTicketID. Configuring the Runbook for Future AlertsFirst navigate to Configuration > Custom Objects > Custom Integrations and create a new runbook, set the following event and details from within the "Details" tab. <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjkwNGM3MDk5LWFkMDEtNGI5NC1hYTAwLWY5MzI3ZDY5MDVlMCJ9.SWiTJICcsH1fy6FQYmaG5zS0n9RsLjw62gS-yFoCEJQ" class="fr-fic fr-fil fr-dib" style="width: 1215px; height: 661.643px;" width="1213" height="662">Fig 11. Runbook setup The event condition being that the ticket type is "Alert" or whichever ticket type you have set on the email rule (this is best set to a unique ticket type for the alerting software, so that the event is only triggered on relevant tickets. <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjE5ZDRjMDczLTBjMDQtNGNjMC1iMDE4LTQ3N2I1ODAxMWFjNSJ9.jwoYLB-QvbAUhRBczSZQMa_FGlpbJJqLy0GKNDmS7a0" class="fr-fic fr-fil fr-dib">Fig 12. Event condition- New ticket logged The first step of the runbook will be for gathering the ticket information, in this case the ID of the ticket in Halo and the Alert Ticket ID. Click on the Add a Step button from within the flow chart of the runbook. <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImI1MGM1MTk3LTRlZjItNGZjMi05NDJhLWRmZWM2MTU5NzljZSJ9.PmzG5F-o78XnG_feOK_OsgdHRrUyPnUE8SuzOJeBQ0A" class="fr-fic fr-fil fr-dib">Fig 13. Add step button Within the new step, set the Type to Action and the Action Type to "Execute an Integration Method" and then click on "New Method" and create the following method, with the correct data types set for the output variables. <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImY0M2U1Mjk0LTEzODEtNDQyNi04YjIyLTIzNzg0YjU0MGZlZiJ9.4xjGXGwjfqPXycbOJsKMMqNDZoxIxmkpM0_KBVIlBAw" class="fr-fic fr-fil fr-dib" style="width: 1290px; height: 388.61px;" width="1288" height="389"> Fig 14. Method exampleThe Output Variables Values:response^id!response^customfields[name=CFAlertTicketID]value! The second step of the runbook will need to run a SQL query on the database. Do this by adding a new step with action type 'Run a SQL query on the Halo database'.  <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImE1ZDBjZTVjLWQ5NDQtNGNkMy1hY2IzLTdiNWUwOWU5ZDg4YiJ9.jGIF5nUvWF294mZfi3sMmvg3JDcdxf6RSaMKG055Qyw" class="fr-fic fr-fil fr-dib" width="1147" height="666"> Fig 15. Run SQL query on database This will get the ticket that was created the earliest out of all tickets with that matching Alert Ticket ID. The output taken from the report will be the existing Halo ticket, this should be created the following way. <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImZhOGI3YWM1LWVhNDMtNDUzOC05ODE2LTFjMGRlZDFkMDdmZCJ9.RqFuMBY4dGzSOrafmxZX332xnSyOdxjJckEoxi_TDW0" class="fr-fic fr-fil fr-dib" width="1415" style="width: 1417px; height: 190.097px;" height="190"> Fig 16. configure the output taken from the report Then the existing alert ticket can be given a condition, which if it meets, then the existing ticket will be updated with the information of the new ticket. The conditional step will be configured the following way. <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjBmZTEwMmJkLWMwZWMtNDMyYi05ZjhkLTIzNDI0MjIwMTc5OSJ9.FmgMK6_ItSNGjzhxsz5zXxiLZIZh18BnF69MGEZgTh0" class="fr-fic fr-fil fr-dib" style="width: 1113px;">Fig 17. condition for alert If there is a value returned, then the next step will be an action to send the information form the new ticket to the existing alert ticket. <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImI5YjIyN2UwLTg2NTktNGU3OS1hNjY3LWM4M2ExMWZjZmNhYiJ9.18lxzIbAoysnBWM0_XWmIlY58BT46PffjmiKGs2I_ac" class="fr-fic fr-fil fr-dib" style="width: 1223px; height: 617.716px;" width="1221" height="618">Fig 18. Action to send the information form the new ticket to the existing alert ticket You can also send an additional update to the new ticket to close it off and give the explanation as to why. <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6Ijc1NmE3NDgzLWVhYTgtNGIzMi1iNzYzLTQ4Y2ZlZTQ4Mjg0NCJ9.FNerZtt0RjJqEAr0SgZ9S1HkLtS1XEgebNsKbTi0NVE" class="fr-fic fr-fil fr-dib">Fig 19. Action to send additional update to closed ticket If the condition is not met, it is also a good idea to leave an automated note. <img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjY4MTJiOGFhLTEwYjgtNGU0Yi05MGU1LTZjMWQ4YTliYjc5ZSJ9.U-Vvt9gYu376eA8TO7TLTNXe-DZL9R8ErQgdVqrPh-U" class="fr-fic fr-fil fr-dib">Fig 20. Action to leave automated note if condition not met </div></div></div></div></div></div></div></div></div></div>

Feedback

Guide name here

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

One unified platform. Every team. Every workflow.

Tailored solutions for every industry.

Powerful capabilities, endless possibilities.

Browse Guides

Platform

Use Cases

Resources

Halo

Subscribe to Halo for latest news

Follow