The Problem with Ticketing Systems
Regardless of the vendor, ticketing systems are a double-edged sword. At the extremes they have immense power and utility when used correctly, but they can also overwhelm users and mislead decision makers when used incorrectly. My personal observations indicate that many implementations seem to be stuck in an in between state of partially useful without providing much insight. However, it isn’t the product’s fault per say because anything out of the box cannot efficiently solve all problems. Ticketing systems are multipurpose tools so they need a bit of customization to achieve their full potential. But this customization requires a good (but difficult to find) ticketing system develop, right? Maybe not! Take a look at our example below using ServiceNow and Splunk and let us know if you are doing something similar with other platforms. |
| Figure 1: Example Splunk dashboard processing and displaying ServiceNow tickets |
Possible Solution
All is not lost if you don’t have a good ticketing system developer. If you are sending the ticket data to your SIEM, you can build those dashboards and gain awesome insight within your single pane of glass. Some ideas for useful insights are:- Total number of tickets
- Opened and assigned
- Unassigned
- Waiting on third party
- Ticket priority
- State of tickets
- Oldest tickets
- Most affected user
- Most affected group/department
- Responder handling the most tickets
- Top close code
Bonus Round
In addition to building the statistical visibility above, filters can be quite useful in narrowing down the information you are seeking. This also allows you to pivot to more complex outliers. The following filters have proven quite useful for us:- Time range
- Wild card search
- Ticket state
- Ticket substate
- Assignment group
- Priority
Extra Credit
After creating the filters above, we recommend adding the ability to pivot back to the ticketing system to check out ticket details or possibly take action on tickets. Such as the ability to change the status of a ticket with a single click. These work flow efficiencies prevent copy and paste errors and shave off serious time and effort from an already overloaded responder. Our example dashboard contains multiple places where a responder may pivot back.Conclusion
If you are in need of greater insight into tickets and their status, creating that insight in a SIEM such as Splunk may not be a bad idea. Even if you have a ticketing system developer, sometimes they just need some ideas to get started on dashboard development and this may be just what they need. This article is meant to provide ideas and even a jumpstart if Splunk and ServiceNow are in use in your environment. We hope it saves you some time—feel free to leave feedback in the section below.Note: In our ServiceNow / Splunk example, we used the existing Cylance ServiceNow Technology Add-on. That said, not all fields are always properly parsed – especially if they are longer fields and use characters that may break the parsing. It will get you 95% of the way there though.
Dashboard Code
The following dashboard assumes that the appropriate logs are being collected and sent to Splunk. Additionally, the dashboard code assumes an index of snow. Feel free to adjust as necessary. Splunk dashboard code provided below:<form>
<label>ServiceNow Tickets</label>
<description>Limited to INSERT_YOUR CI Type</description>
<search id="Base_Search">
<query>index=snow sourcetype="snow:incident" $wild$ u_ci_autofill=YOUR_AUTOFILL_ID | dedup number | eval DaysOpen=round((now()-strptime(dv_opened_at, "%m-%d-%Y"))/86400,2) | rex field=dv_priority "\d\s-\s(?<dv_priority>.*)" | rex field=dv_severity "\d\s-\s(?<dv_severity>.*)" | table dv_opened_at, DaysOpen, dv_opened_by, dv_closed_at, dv_closed_by, number, dv_incident, dv_state, dv_substate, dv_close_code, dv_priority, dv_severity, dv_u_affected_user, dv_cmdb_ci, dv_malware_url, dv_approval, dv_assigned_to, dv_assignment_group, dv_short_description, close_notes | search $dv_priority$ $assignment_group$ $substate$ $dv_state$</query>
<earliest>$time.earliest$</earliest>
<latest>$time.latest$</latest>
</search>
<fieldset submitButton="false" autoRun="true">
<input type="time" token="time" searchWhenChanged="true">
<label>Time Range</label>
<default>
<earliest>-24h@h</earliest>
<latest>now</latest>
</default>
</input>
<input type="text" token="wild" searchWhenChanged="true">
<label>Wildcard Search</label>
<default>*</default>
</input>
<input type="dropdown" token="dv_state">
<label>Ticket State</label>
<choice value="NOT dv_state=Canceled NOT dv_state=Closed NOT dv_state=Resolved">Not Canceled, Closed, Resolved</choice>
<choice value="*">All</choice>
<choice value="dv_state=New">New</choice>
<choice value="dv_state=Analysis">Analysis</choice>
<choice value="dv_state=Contain">Contain</choice>
<choice value="dv_state=Cancelled">Cancelled</choice>
<choice value="dv_state=Closed">Closed</choice>
<choice value="dv_state=Review">Review</choice>
<choice value="dv_state=Resolved">Resolved</choice>
<default>NOT dv_state=Canceled NOT dv_state=Closed NOT dv_state=Resolved</default>
<initialValue>NOT dv_state=Canceled NOT dv_state=Closed NOT dv_state=Resolved</initialValue>
</input>
<input type="multiselect" token="substate">
<label>Ticket Substate</label>
<choice value="*">All</choice>
<choice value=""Waiting on External"">Waiting on External</choice>
<choice value="SOC">SOC</choice>
<default>*</default>
<initialValue>*</initialValue>
</input>
<input type="multiselect" token="assignment_group">
<label>Assignment Group</label>
<choice value="*">All</choice>
<choice value=""SOC Level 1"">SOC Level 1</choice>
<choice value=""SOC Level 2"">SOC Level 2</choice>
<choice value=""SOC Level 3"">SOC Level 3</choice>
<valuePrefix>dv_assignment_group=</valuePrefix>
<delimiter> OR </delimiter>
<default>*</default>
<initialValue>*</initialValue>
</input>
<input type="multiselect" token="dv_priority">
<label>Priority</label>
<choice value="*">All</choice>
<choice value="Critical">Critical</choice>
<choice value="High">High</choice>
<choice value="Moderate">Moderate</choice>
<choice value="Low">Low</choice>
<default>*</default>
<initialValue>*</initialValue>
<delimiter> OR dv_priority=</delimiter>
<prefix>dv_priority=</prefix>
<valuePrefix>"</valuePrefix>
<valueSuffix>"</valueSuffix>
</input>
</fieldset>
<row>
<panel>
<single>
<title>Total Tickets</title>
<search base="Base_Search">
<query>| stats count</query>
</search>
<option name="drilldown">all</option>
<option name="refresh.display">progressbar</option>
</single>
</panel>
<panel>
<single>
<title>Open and Assigned</title>
<search base="Base_Search">
<query>| search dv_state!=Cancelled dv_state!=Closed NOT dv_assigned_to="" | stats count</query>
</search>
<option name="drilldown">all</option>
</single>
</panel>
<panel>
<single>
<title>Open and Not Assigned</title>
<search base="Base_Search">
<query>| search dv_state!=Cancelled dv_state!=Closed dv_assigned_to="" | stats count</query>
</search>
<option name="drilldown">all</option>
</single>
</panel>
<panel>
<single>
<title>Waiting on External</title>
<search base="Base_Search">
<query>| where dv_substate="Waiting on External" | stats count</query>
</search>
<option name="drilldown">all</option>
</single>
</panel>
</row>
<row>
<panel>
<chart>
<title>Priority</title>
<search base="Base_Search">
<query>| top limit=0 dv_priority</query>
</search>
<option name="charting.chart">pie</option>
</chart>
</panel>
<panel>
<chart>
<title>Top State</title>
<search base="Base_Search">
<query>| top limit=0 dv_state</query>
</search>
<option name="charting.chart">pie</option>
</chart>
</panel>
<panel>
<chart>
<title>Top CI</title>
<search base="Base_Search">
<query>| top limit=0 dv_cmdb_ci</query>
</search>
<option name="charting.chart">pie</option>
</chart>
</panel>
</row>
<row>
<panel>
<chart>
<title>Longest Open Tickets > 7 days (Click to View Directly in Service Now)</title>
<search base="Base_Search">
<query>| stats values(DaysOpen) by number | rename values(DaysOpen) AS DaysOpen | where DaysOpen > 7</query>
</search>
<option name="charting.axisTitleX.visibility">visible</option>
<option name="charting.axisTitleY.visibility">collapsed</option>
<option name="charting.axisY.scale">linear</option>
<option name="charting.chart">bar</option>
<option name="charting.chart.showDataLabels">all</option>
<option name="charting.chart.stackMode">default</option>
<option name="charting.drilldown">all</option>
<option name="charting.layout.splitSeries">0</option>
<option name="charting.legend.labelStyle.overflowMode">ellipsisEnd</option>
<option name="charting.legend.placement">right</option>
<drilldown>
<link target="_blank">https://INSERT_YOUR.service-now.com/nav_to.do?uri=incident.do?sysparm_query=number%3D$row.number$</link>
</drilldown>
</chart>
</panel>
<panel>
<chart>
<title>Longest Open Tickets > 7 days (Click to View in Splunk)</title>
<search base="Base_Search">
<query>| eval info=dv_assigned_to + " - " + number | stats values(DaysOpen) by info | rename values(DaysOpen) AS DaysOpen | sort - DaysOpen | where DaysOpen > 7</query>
</search>
<option name="charting.axisTitleX.visibility">collapsed</option>
<option name="charting.axisTitleY.visibility">collapsed</option>
<option name="charting.axisY.scale">linear</option>
<option name="charting.chart">bar</option>
<option name="charting.chart.showDataLabels">all</option>
<option name="charting.chart.stackMode">default</option>
<option name="charting.drilldown">all</option>
<option name="charting.layout.splitSeries">0</option>
<option name="charting.legend.labelStyle.overflowMode">ellipsisEnd</option>
<option name="charting.legend.placement">right</option>
</chart>
</panel>
</row>
<row>
<panel>
<table>
<title>Top dv_u_affected_user</title>
<search base="Base_Search">
<query>| top limit=0 dv_u_affected_user</query>
</search>
<option name="drilldown">cell</option>
</table>
</panel>
<panel>
<table>
<title>Top dv_assigned_to</title>
<search base="Base_Search">
<query>| top limit=0 dv_assigned_to</query>
</search>
<option name="drilldown">cell</option>
</table>
</panel>
<panel>
<table>
<title>Top dv_assignment_group</title>
<search base="Base_Search">
<query>| top limit=0 dv_assignment_group</query>
</search>
<option name="drilldown">cell</option>
</table>
</panel>
<panel>
<table>
<title>Top dv_close_code</title>
<search base="Base_Search">
<query>| top limit=0 dv_close_code</query>
</search>
<option name="drilldown">cell</option>
</table>
</panel>
</row>
<row>
<panel>
<table>
<title>Details (Click the row to visit ServiceNow directly)</title>
<search base="Base_Search">
<query/>
</search>
<option name="dataOverlayMode">none</option>
<option name="drilldown">cell</option>
<option name="percentagesRow">false</option>
<option name="rowNumbers">true</option>
<option name="totalsRow">false</option>
<option name="wrap">true</option>
<format type="color" field="dv_incident">
<colorPalette type="list">[#65A637,#6DB7C6,#F7BC38,#F58F39,#D93F3C]</colorPalette>
<scale type="threshold">0,30,70,100</scale>
</format>
<format type="color" field="dv_substate">
<colorPalette type="map">{"Waiting on External":#6A5C9E}</colorPalette>
</format>
<format type="color" field="dv_priority">
<colorPalette type="map">{"High":#D93F3C,"Medium":#F7BC38,"Low":#6DB7C6}</colorPalette>
</format>
<drilldown>
<link target="_blank">https://INSERT_YOUR.service-now.com/nav_to.do?uri=incident.do?sysparm_query=number%3D$row.number$</link>
</drilldown>
</table>
</panel>
</row>
</form>
