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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...