Discrepany in "Count"

[willadams]

My requirement is to detect login attempts by a disabled user. Typically this could be found using eventcode 4768 and result code 0x12. I wanted to enhance this to look specifically for a user that has been "disabled" in AD. With this in mind, I wrote the following search that joins 2 indexes that contains the information and it gets the results.

index=windows EventCode=4768 AND Result_Code="0x12" | bucket _time span=5m | stats count by Account_Name | where count > 8
| rename Account_Name AS SamAccountName
| join type=inner [search index=adinfo source=csv:ad Enabled=false | fields SamAccountName]
| table *

While this is good information and achieves my objective, I noted a discrepancy in the counts for certain users. If I drop the join and just use a query such as

index=windows EventCode=4768 AND Result_Code="0x12" | bucket _time span=5m | stats count by Account_Name | where count > 8

I will get results. However what I have noted that some users (not all) show a discrepancy in count. For example

User1 (join shows count of 10 | non-join shows count of 10)
User2 (join shows count of 15 | non-join shows count of 16)
User3 (join shows count of 3400 | non-join shows count of 3459)
User4 (join shows count of 9 | non-join shows count of 9)
User5 (join shows count of 45 | non-join shows count of 45)
User6 (join shows count of 98 | non-join shows count of 99)

I unable to figure out why this discrepancy exists. I also did find that if I then view a particular user (for example User3 from the join statistics) and drill down I eventually end up with the same numbers that I would find in the non-join search.

[gcusello]

Hi willadams,
there's a limit of 50,000 results in subsearches, so maybe you don't have all the results.
So please, try a different approach:

(index=windows EventCode=4768 AND Result_Code="0x12") OR ( index=adinfo source=csv:ad Enabled=false)
| rename Account_Name AS SamAccountName
| stats dc(index) AS index_num count BY SamAccountName
| where index_num>1 AND count>8

If you want to add some field, add them to the stats command with the value(my_field) AS my_field option.

In your main search, if you don't use _time in the following stats, you don't need the bin command.

Ciao.
Giuseppe

[willadams]

I am trying to add the additional values in but it doesn't seem to output to any statistics or visualisation to view the data. All the data is visibile in "Events" (if I run in Verbose mode). Here is what I constructed from the above query

(index=windows EventCode=4768 AND Result_Code="0x12" Account_Name!="*$") OR ( index=adinfo source=csv:ad Enabled=false)
| rename Account_Name AS SamAccountName
| stats dc(index) AS index_num count BY SamAccountName values (SamAccountName) AS User
| where index_num>1 AND count>8
| table User count

I also tried to then do the following

(index=windows EventCode=4768 AND Result_Code="0x12" Account_Name!="*$") OR ( index=adinfo source=csv:ad Enabled=false)
| rename Account_Name AS SamAccountName
| stats dc(index) AS index_num count BY SamAccountName 

| stats values (SamAccountName) AS user
| where index_num>1 AND count>8
| table user count

I also then tried the following to try and get some data displaying. Again in Verbose mode I can see the events are picked up but I don't have any statistical views etc to view what I need

(index=windows EventCode=4768 AND Result_Code="0x12" Account_Name!="*$") OR ( index=adinfo source=csv:ad Enabled=false)
| rename Account_Name AS SamAccountName
| stats dc(index) AS index_num count BY SamAccountName 
| stats values(user) AS User 
| stats values(src_ip) AS src_ip 
| stats values(dest_nt_host) AS dest_nt_host 
| stats values(dest_ip) AS dest_ip
| where index_num>1 AND count>8
| table user src_ip dest_nt_host dest_ip

(index=windows EventCode=4768 AND Result_Code="0x12" Account_Name!="*$") OR ( index=adinfo source=csv:ad Enabled=false)
| rename Account_Name AS SamAccountName
| stats dc(index) AS index_num count BY SamAccountName values(user) AS user
| where index_num>1 AND count>8
| table user

[gcusello]

Hi willadams,
when you say "| stats values (SamAccountName) AS user" you are meaning "| rename SamAccountName AS user", is i t correct?
If not, beware because after a stats command you can use only the fields of the stats, in your case only user, so you haven't more index_num and count that you use in the following rows.

Then beware to the stats command "| stats dc(index) AS index_num count BY SamAccountName values (SamAccountName) AS User" it isn't correct, there are two errors: the BY clause must be at the end of the row and you cannot have a space between values and parenthesis.
The correct one is "| stats dc(index) AS index_num values(SamAccountName) AS User count BY SamAccountName".

In the following tries there's the same problems of stats fields: you can use only the fields of the previous stats command.

Ciao.
Giuseppe

[Sukisen1981]

if you add max=0 like this, what happens index=windows EventCode=4768 AND Result_Code="0x12" | bucket _time span=5m | stats count by Account_Name | where count > 8
| rename Account_Name AS SamAccountName
| join max=0 type=inner [search index=adinfo source=csv:ad Enabled=false | fields SamAccountName]
| table *

[willadams]

Adding the max=0 does seem to help as where there is a match the count for each index is identical. However there is one where the count shows a difference of "1". For example User7 shows 1495 (non-join) and 1496 (join). This may be due to an anomaly I haven't picked up (i.e. something else that is contributing to that event) but not entirely sure.

Would it be better to not use join and use stats? The problem is I have no idea how to convert this join to stats, as I would like to make this search more efficient.

[Sukisen1981]

hi @willadams
As @gcusello mentions, try using the stats query if you are sure you are not joining over 50k results then the query with max=0 should be correct, and the discrepancy could be due to the execution time.
Try the stats and see if it matches your needs

[willadams]

I checked when I got in this morning and found that the number of events that are being returned by the join is just under 600000 events, way above the subsearch limit of 50000. I will steer towards using the stats command and see if this gets me the data I want.

The non-join search returns under 10000 events by itself.