topic How to set up search so that the events tab matches the logs corresponding to the statistics tab? in Splunk Search

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

gnoriega — Fri, 22 May 2020 20:54:07 GMT

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

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

to4kawa — Fri, 22 May 2020 22:16:52 GMT

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

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

gnoriega — Fri, 22 May 2020 23:08:12 GMT

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.

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

to4kawa — Fri, 22 May 2020 23:22:47 GMT

what's drilldown query?
Is there where command ?

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

gnoriega — Mon, 25 May 2020 14:50:02 GMT

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>

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

DalJeanis — Tue, 26 May 2020 17:40:16 GMT

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>

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

Skins — Thu, 28 May 2020 13:44:58 GMT

Hi @DalJeanis

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

thx