Splunk Search

How to set up search so that the events tab matches the logs corresponding to the statistics tab?

gnoriega
Explorer

I've got the following search to identify when a user has more than 20 auth failures.
I'm trying to find a way to remove additional logs of users who have less than 20 auth failures from the Events tab.

For example, I might see in the Statistics tab 1 result indicating that a single user failed 135 times. However in the Events tab I see 145 logs which include 10 additional auth failures of other users that failed less than 20 times.

I only want to see 135 logs in the Events tab corresponding to the 135 results from "| search TotalAuthFailures >= 20". This is so when analysts are drilling down on the alert they're not confused by additional users in the Events raw logs.

How can I do this?

index=main sourcetype="wineventlog" EventCode=4625 (Sub_Status=0xC000006A OR Sub_Status=0xC0000064)
 | eval match=if(match(Account_Name,".*\$"),1,0)
 | eval Description=if(Sub_Status=="0xC0000064","User name does not exist.","User name is correct but the password is wrong.")
 | where  match=0 | stats  count by user,src_ip,src_nt_host,Description
 | rename  count AS "TotalAuthFailures" user AS "User (Origin)" src_ip AS "Source IP Address" src_nt_host AS "Host (Origin)" EventCode AS "Event ID"
 | dedup  "User (Origin)"
 | search TotalAuthFailures >= 20
0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

First, deduping there is giving you invalid results. If there ARE any duplicate users, then you need to either retain them, or sort descending on count before deduping. (Also, as best practices, it's better to do those things before making the names "pretty".)

Make this your base

<search id="base">
    <query>
     index=main sourcetype="wineventlog" EventCode=4625 (Sub_Status=0xC000006A OR Sub_Status=0xC0000064)
      | eval match=if(match(Account_Name,".*\$"),1,0)
      | eval Description=if(Sub_Status=="0xC0000064","User name does not exist.","User name is correct but the password is wrong.")
      | where  match=0 
      | fields user, src_ip, src_nt_host, Description
    </query>
</search>

Make this your second query

<search base="base">
   <query>
   | stats  count AS "TotalAuthFailures" by user, src_ip, src_nt_host, Description
   | search TotalAuthFailures >= 20

   | sort 0 - TotalAuthFailures + user
   | rename COMMENT as "dedup user here if you really need to"

   | rename  
     user AS "User (Origin)", 
     src_ip AS "Source IP Address", 
     src_nt_host AS "Host (Origin)", 
     EventCode AS "Event ID"
   </query>
</search>

Make this your third query

<search base="base">
   <query>
   | eventstats  count AS "TotalAuthFailures" by user, src_ip, src_nt_host, Description
   | search TotalAuthFailures >= 20
   | fields - TotalAuthFailures 
   </query>
 </search>

View solution in original post

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

First, deduping there is giving you invalid results. If there ARE any duplicate users, then you need to either retain them, or sort descending on count before deduping. (Also, as best practices, it's better to do those things before making the names "pretty".)

Make this your base

<search id="base">
    <query>
     index=main sourcetype="wineventlog" EventCode=4625 (Sub_Status=0xC000006A OR Sub_Status=0xC0000064)
      | eval match=if(match(Account_Name,".*\$"),1,0)
      | eval Description=if(Sub_Status=="0xC0000064","User name does not exist.","User name is correct but the password is wrong.")
      | where  match=0 
      | fields user, src_ip, src_nt_host, Description
    </query>
</search>

Make this your second query

<search base="base">
   <query>
   | stats  count AS "TotalAuthFailures" by user, src_ip, src_nt_host, Description
   | search TotalAuthFailures >= 20

   | sort 0 - TotalAuthFailures + user
   | rename COMMENT as "dedup user here if you really need to"

   | rename  
     user AS "User (Origin)", 
     src_ip AS "Source IP Address", 
     src_nt_host AS "Host (Origin)", 
     EventCode AS "Event ID"
   </query>
</search>

Make this your third query

<search base="base">
   <query>
   | eventstats  count AS "TotalAuthFailures" by user, src_ip, src_nt_host, Description
   | search TotalAuthFailures >= 20
   | fields - TotalAuthFailures 
   </query>
 </search>
0 Karma

Skins
Path Finder

Hi @DalJeanis

why do you use eventstats in the 3 query as opposed to stats like the 2nd query ?

thx

0 Karma

to4kawa
Ultra Champion

The results of verbose mode and Drilldown are different, so you don't have to worry about it.

0 Karma

gnoriega
Explorer

I get the same result in the search verbose mode (Events 145) and in my dashboard Drilldown (145 logs). That's how I discovered the issue. When I drilled down I saw the additional users.

0 Karma

to4kawa
Ultra Champion

what's drilldown query?
Is there where command ?

0 Karma

gnoriega
Explorer

In my dashboard I have 2 drilldown panels, the first displays the statistics table with the correct number of auth failures (135). The second drilldown panel in the dashboard shows the events (145). It's in this second panel where I would only like to see the 135 event.

Here's the code for the panels in my dashboard:

  <row>
    <panel>
      <table>
        <title>[Drilldown] Recent statistics for $selected_value$ at $converted_time$</title>
        <search id="base">
          <query>$field_token$</query>
          <earliest>$selected_value_earliest$</earliest>
          <latest>$selected_value_latest$</latest>
        </search>
        <option name="drilldown">cell</option>
      </table>
    </panel>
  </row>
  <row>
   <panel>
      <event>
        <title>[Drilldown] Recent events for $selected_value$ at $converted_time$</title>
        <search base="base">
        </search>
      </event>
    </panel>
  </row>
0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...