Advanced Ticketing Analytics Using Your SIEM

By Tony Lee

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(?&lt;dv_priority&gt;.*)" | rex field=dv_severity "\d\s-\s(?&lt;dv_severity&gt;.*)" | 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="&quot;Waiting on External&quot;">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="&quot;SOC Level 1&quot;">SOC Level 1</choice>
      <choice value="&quot;SOC Level 2&quot;">SOC Level 2</choice>
      <choice value="&quot;SOC Level 3&quot;">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 &gt; 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 &gt; 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 &gt; 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 &gt; 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>

Detecting DNS Tunneling Using Subdomain Frequency Analysis

By Tony Lee

For those that may not be familiar, DNS tunneling is the process of embedding data inside DNS requests and responses. It is typically used by a threat actor as a last resort command and control (C2) channel due to the size limitations of DNS packets. One trade-off for the limited size and slow speed is that it is one of the stealthiest and most difficult C2 channels to detect--thus it is quite effective at  maintaining persistence even after other C2 is discovered and eliminated. Due to the prevalence of DNS and its generally trusted (not inspected) status in most networks, most organizations have little-to-no visibility, however this article will cover just one of the many methods of detecting this stealthy C2.  The first step is to collect the DNS logs and send them to a central log aggregation platform such as Splunk--which is what our example uses, but feel free to apply it to your platform of choosing.

Figure 1:  Visual explanation of DNS Tunneling

Query Length

After collecting the logs, we will next look for abnormally long DNS queries. Technically the longest allowable domain name per the RFC is 253 characters (including the dots)--thus we can encode enough data in each fully qualified domain name (FQDN) to constitute C2, but it does require creating longer DNS queries. The example search below assumes that we are storing the DNS logs in an index called dns and log format of Microsoft Windows AD DNS (hence the sourcetype), but this will also work for BIND and other DNS data.  Our example checks for a DNS query length greater than 50 characters--but it can be tuned to whatever works best in your environment. Keep in mind that if the number is too low, you will see too many false positives.  If it is too high, you may miss some tunneling or other strangeness that you want to investigate.  We recommend that you start at 50 and adjust as needed.

index=dns sourcetype="MSAD:NT6:DNS" NOT (query="_ldap*" OR query="*.in-addr.arpa." OR query="*.ip6.arpa") | where len(query)>50

Note:  Be sure to check that your DNS query is actually contained in the "query" field

Subdomain Frequency Analysis

If the above search worked and returned some results, we have met the prerequisites.  Taking the search above a step further we are going to parse out the domain portion and use statistical analysis to give us the following columns:

• The domain controlled by the attacker
• Potential number of victims querying this domain
• Number of subdomains
• Number of times the attacker-controlled domain was queried
• List of potential victims
• List of the subdomains that were queried

index=dns sourcetype="MSAD:NT6:DNS" NOT (query="_ldap*" OR query="*.in-addr.arpa." OR query="*.ip6.arpa") | where len(query)>50 | rex field=query ".*?\.(?<domain>.*)\.$" | stats dc(src_ip) AS NumSourceIPs, dc(query) AS NumSubDomains, count(query) AS DomainQueryCount, values(src_ip) AS SourceIPs, values(query) AS Subdomains by domain | sort - NumSubDomains | where NumSubDomains>1

Figure 1:  Fields provided by the search string above

Now look for anything that stands out as being suspicious.  Keep in mind some of this may be legitimate traffic, but you may also find some known bad domains as you research the results.

Defense Strategy

As mentioned in the introduction, DNS tunneling can be tough to defend against.  However, follow these tips and it will increase the difficulty of attacker success.

• Keep a simple DNS infrastructure.  The more complicated and chained, the more difficult it is to detect this issue
• Do not allow hosts to go directly to the Internet for DNS (block all but the DNS servers at the firewall and monitor for any deviation)
• Send all DNS logs (Microsoft AD, BIND, etc.) to a centralized logging platform
• Run searches (such as the one provided in this article) to continuously monitor and alert on anomalies
• Use threat intelligence to augment your DNS tunneling searches to alert against known malicious domains

Conclusion

There are many ways of detecting potential DNS tunneling and this is just one of them. Even if you don't catch a threat actor in your network, you may end up discovering something else strange.  Content distribution networks (CDNs), threat actors, marketing software, spyware, and more may turn up in your search results.  Maybe some or all of those are a concern to your team.  You won't know until you take a look though.  Happy Searching!

References

Good explanation of DNS tunneling found at:  https://hackersterminal.com/dns-tunneling/ -- Kudos for their graphic as well--it is one of the clearest we found on-line.  Nice work.