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

[gnoriega]

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

[DalJeanis · Accepted solution]

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>

[Skins]

Hi @DalJeanis

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

thx

[to4kawa]

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

[gnoriega]

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.

[to4kawa]

what's drilldown query?
Is there where command ?

[gnoriega]

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>