RPZ DNS Firewall Syslog based Report - Multiple Groupings and Geo Location

[#1]

I've updated my RPZ syslog report to add in some more grouping options and add in the ability to match the IP back to our facility using the IPAM, Network EA's. I left this code in even though it is a custom CSV based lookup table as there is no direct way to get from the reporting tool to the networks’ EA's just to show what is possible.
If you have forwarder chains in your Infoblox grid, you may have forwarded queries showing up multiple times depending on how your RPZ’s are scanning forwarded queries. The radio button to exclude Infoblox member will remove this noise from the reports.
The headings in the screen shots should be self-explanatory. The facility column in our case gets us to a physical location and local contact so this report can be further parsed to go directly to a regional or local security team if desired.
The Geo Map is just the eye candy that can be exported and put up on a monitor in a NOC or “manager” report. It again is generated using the CSV lookup tables of our internal network to get to a physical location, latitude and longitude.
The code before the CSV lookups are a hack as there is no CIDR lookup option in Infoblox’s version of Splunk so I just match on the first 3 octects. Before I import the CSV lookup table, I take any network that is larger than a /24 and list all the /24’s in it, duplicating the data.
If anyone finds a way to easily do a CIDR match in Infoblox’s Splunk, let me know. It would greatly increase the supportability of this solution.

<form>
  <label>1-DNS Top RPZ Hits V4</label>
  <description>Correct summary over time.
Added RPZ zone sorting
Added Grouping
Added Site</description>
  <fieldset submitButton="true" autoRun="true">
    <input type="time" token="time">
      <label>Time</label>
      <default>
        <earliest>-1d</earliest>
        <latest>now</latest>
      </default>
    </input>
    <input type="multiselect" token="rpz_zone_str">
      <label>RPZ Zone</label>
      <prefix>(</prefix>
      <suffix>)</suffix>
      <valuePrefix>RPZHit="*</valuePrefix>
      <valueSuffix>"</valueSuffix>
      <delimiter> OR </delimiter>
      <choice value="rpz-custom-local">Local-RPZ-Only</choice>
      <choice value="ransomware.rpz.infoblox.local">Ransomware by Name</choice>
      <choice value="base.rpz.infoblox.local">Base by Name</choice>
      <choice value="antimalware.rpz.infoblox.local">Antimalware by Name</choice>
      <choice value="fresh-domain.surbl.rpz.infoblox.local">Fresh Domain Names</choice>
      <choice value="dhs-ais-ip.rpz.infoblox.local">DHS by IP</choice>
      <choice value="antimalware-ip.rpz.infoblox.local">Antimalware by IP</choice>
      <choice value="exploitkit-ip.rpz.infoblox.local">Exploitkit by IP</choice>
      <choice value="bot-ip.rpz.infoblox.local">Bots by IP</choice>
      <choice value="dhs-ais-domain.rpz.infoblox.local">DHS by Name</choice>
      <choice value="tor-exit-node-ip.rpz.infoblox.local">TOR Exit Node by IP</choice>
      <choice value="rpz.infoblox.local">All-Infoblox-Feeds</choice>
      <default>All-Infoblox-Feeds</default>
      <initialValue>All-Infoblox-Feeds</initialValue>
    </input>
    <input type="dropdown" token="topn">
      <label>Top N</label>
      <choice value="5">5</choice>
      <choice value="10">10</choice>
      <choice value="20">20</choice>
      <choice value="50">50</choice>
      <choice value="100">100</choice>
      <choice value="200">200</choice>
      <choice value="250">250</choice>
      <choice value="500">500</choice>
      <default>500</default>
      <initialValue>500</initialValue>
    </input>
    <input type="text" token="domain_name">
      <label>Domain Name</label>
      <default>All</default>
      <change>
        <condition value="All">
          <set token="domain_name_str">*</set>
        </condition>
        <condition value="*">
          <set token="domain_name_str">Query="$value$"</set>
        </condition>
      </change>
    </input>
    <input type="multiselect" token="members">
      <label>Members</label>
      <prefix>(</prefix>
      <suffix>)</suffix>
      <valuePrefix>goodhost="</valuePrefix>
      <valueSuffix>"</valueSuffix>
      <delimiter> OR </delimiter>
      <search>
        <query>index=ib_dns_summary report=si_dns_rpz_hits
               | stats count by orig_host</query>
        <earliest>$time.earliest$</earliest>
        <latest>$time.latest$</latest>
      </search>
      <fieldForLabel>orig_host</fieldForLabel>
      <fieldForValue>orig_host</fieldForValue>
      <choice value="*">All</choice>
      <default>*</default>
    </input>
    <input type="text" token="rpz_entry">
      <label>RPZ Entry</label>
      <default>All</default>
      <change>
        <condition value="All">
          <set token="rpz_entry_str">*</set>
        </condition>
        <condition value="*">
          <set token="rpz_entry_str">RPZ_QNAME="$value$"</set>
        </condition>
      </change>
    </input>
    <input type="radio" token="IBMembers" searchWhenChanged="true">
      <label>Include Infoblox Members as Clients</label>
      <choice value="| noop ">Yes</choice>
      <choice value="| lookup nios_member_ip_lookup MEMBER_IP as QuerySource OUTPUT  host as CLIENT_NAME| where isnull(CLIENT_NAME) ">No</choice>
      <default>Yes</default>
      <initialValue>Yes</initialValue>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>Grouped By Query Source</title>
      <table>
        <search>
          <query>index=* sourcetype=ib:syslog CEF: $members$ $rpz_zone_str$ $domain_name_str$  $IBMembers$          
            | eval KeepFields = _time + "::" + Query + "::" + QueryType + "::" + RPZHit             
            | fields QuerySource,KeepFields             
            | stats Count as QueryCount values(*) as * by QuerySource            
            | mvexpand KeepFields              
            | eval KeepFields=split(KeepFields,"::")         
            | eval Time=mvindex(KeepFields,0)             
            | eval Query=mvindex(KeepFields,1)
            | eval QueryType = mvindex(KeepFields,2)         
            | eval RPZHit = mvindex(KeepFields,3)         
            | fields - KeepFields            
            | stats count as QuerySourceQueryCount values(Time) as Time values(QueryType) as QueryType values(RPZHit) as RPZHit values(QueryCount) as QueryCount by QuerySource,Query  
            | convert ctime(Time) as Time           
            | lookup dnslookup clientip as QuerySource output clienthost as QuerySourceName    
            | eval QuerySourceName=coalesce(QuerySourceName,QuerySource)         
            | eval first3=split(QuerySource,".") 
            | eval first3=mvindex(first3, 0, 2) |eval first3=mvjoin('first3',".")     
            | lookup subnettosite first3subnet as first3      
            | table QuerySourceName,facility,Query,Time,QueryType,RPZHit,QuerySourceQueryCount,QueryCount  
            | sort - QueryCount</query>
          <earliest>$time.earliest$</earliest>
          <latest>$time.latest$</latest>
          <progress>
            <condition>
              <unset token="conditional_value"></unset>
            </condition>
          </progress>
        </search>
        <option name="wrap">true</option>
        <option name="rowNumbers">true</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">row</option>
        <option name="count">20</option>
        <drilldown>
          <condition field="Domain Name">
            <set token="rpz_entry_or_domain_field">domain</set>
            <set token="rpz_entry_or_domain_value">$row.Domain Name$</set>
            <unset token="conditional_value"></unset>
          </condition>
          <condition field="RPZ Entry">
            <set token="rpz_entry_or_domain_field">rpz</set>
            <set token="rpz_entry_or_domain_value">$row.RPZ Entry$</set>
            <unset token="conditional_value"></unset>
          </condition>
          <condition field="*">
            <set token="conditional_value">$row.Client ID$</set>
            <unset token="rpz_entry_or_domain_field"></unset>
            <unset token="rpz_entry_or_domain_value"></unset>
          </condition>
        </drilldown>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <title>Grouped By Query Name</title>
      <table>
        <search>
          <query>index=* sourcetype=ib:syslog CEF: $members$ $rpz_zone_str$ $domain_name_str$ $IBMembers$
            | eval KeepFields = _time + "::" + QuerySource + "::" + QueryType + "::" + RPZHit 
            | fields Query,KeepFields 
            | stats Count as QueryCount values(*) as * by Query  
            | mvexpand KeepFields 
            | eval KeepFields=split(KeepFields,"::") 
            | eval Time=mvindex(KeepFields,0)
            | eval QuerySource=mvindex(KeepFields,1)
            | eval QueryType = mvindex(KeepFields,2)
            | eval RPZHit = mvindex(KeepFields,3)
            | fields - KeepFields 
            | stats count as QuerySourceQueryCount values(Time) as Time values(QueryType) as QueryType values(RPZHit) as RPZHit values(QueryCount) as QueryCount values(QuerySource) as QuerySource by Query
            | convert ctime(Time) as Time
            | lookup dnslookup clientip as QuerySource output clienthost as QuerySourceName 
            | eval QuerySourceName=coalesce(QuerySourceName,QuerySource)
            | table Query,QuerySourceName,RPZHit,QueryType,QuerySourceQueryCount,QueryCount 
            | sort - QueryCount</query>
          <earliest>$time.earliest$</earliest>
          <latest>$time.latest$</latest>
          <progress>
            <condition>
              <unset token="conditional_value"></unset>
            </condition>
          </progress>
        </search>
        <option name="wrap">true</option>
        <option name="rowNumbers">true</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">row</option>
        <option name="count">10</option>
        <drilldown>
          <condition field="Domain Name">
            <set token="rpz_entry_or_domain_field">domain</set>
            <set token="rpz_entry_or_domain_value">$row.Domain Name$</set>
            <unset token="conditional_value"></unset>
          </condition>
          <condition field="RPZ Entry">
            <set token="rpz_entry_or_domain_field">rpz</set>
            <set token="rpz_entry_or_domain_value">$row.RPZ Entry$</set>
            <unset token="conditional_value"></unset>
          </condition>
          <condition field="*">
            <set token="conditional_value">$row.Client ID$</set>
            <unset token="rpz_entry_or_domain_field"></unset>
            <unset token="rpz_entry_or_domain_value"></unset>
          </condition>
        </drilldown>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <chart>
        <title>Todays Hit Count vs Same Day Last Week</title>
        <search>
          <query>index=* sourcetype=ib:syslog CEF: $members$ $rpz_zone_str$ $domain_name_str$
             | multikv
            | eval ReportKey="Today" 
            | append [search index=* sourcetype=ib:syslog CEF: $members$ $rpz_zone_str$ $domain_name_str$ earliest=-192h latest=-144h
            | multikv 
            | eval ReportKey="Last Week"
            | eval _time=_time+60*60*24*7] 
            | timechart span=30m COUNT by ReportKey</query>
          <earliest>$time.earliest$</earliest>
          <latest>$time.latest$</latest>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.data.count">10000</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">90</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.enabled">0</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">area</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.showDataLabels">none</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">stacked</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option>
        <option name="charting.legend.placement">right</option>
        <option name="charting.axisTitleY.text">Hits per Hour</option>
        <option name="charting.chart.overlayFields">"Last Week"</option>
      </chart>
    </panel>
  </row>
  <row>
    <panel>
      <map>
        <search>
          <query>index=* sourcetype=ib:syslog CEF: $members$ $rpz_zone_str$ $domain_name_str$  $IBMembers$          
            | eval KeepFields = _time + "::" + Query + "::" + QueryType + "::" + RPZHit             
            | fields QuerySource,KeepFields             
            | stats Count as QueryCount values(*) as * by QuerySource            
            | mvexpand KeepFields              
            | eval KeepFields=split(KeepFields,"::")         
            | eval Time=mvindex(KeepFields,0)             
            | eval Query=mvindex(KeepFields,1)
            | eval QueryType = mvindex(KeepFields,2)         
            | eval RPZHit = mvindex(KeepFields,3)         
            | fields - KeepFields            
            | stats count as QuerySourceQueryCount values(Time) as Time values(QueryType) as QueryType values(RPZHit) as RPZHit values(QueryCount) as QueryCount by QuerySource,Query  
            | convert ctime(Time) as Time           
            | lookup dnslookup clientip as QuerySource output clienthost as QuerySourceName    
            | eval QuerySourceName=coalesce(QuerySourceName,QuerySource)         
            | eval first3=split(QuerySource,".") 
            | eval first3=mvindex(first3, 0, 2) |eval first3=mvjoin('first3',".")     
            | lookup subnettosite first3subnet as first3      
            | eval NameplusSite= tostring(QuerySourceName) + " -- " + tostring(facility)
                  | geostats latfield=lat longfield=long globallimit=0 maxzoomlevel=11 sum(QueryCount) by  NameplusSite</query>
          <earliest>$time.earliest$</earliest>
          <latest>$time.latest$</latest>
        </search>
        <option name="mapping.type">marker</option>
      </map>
    </panel>
  </row>
</form>

[#2]

Thanks DEvans for sharing this.