Join 2 queries and obtain count of rows of right side of join

[Kerghan666]

Hi, I am trying to obtain user locked out events (4740) while performing a join with failed password events (4625 logontype 2) and obtain a count of the failed logons on the same day to display with the lockout event.

e.g.

User = ForgetfulFrank

Event = 4740

Failed Password Attempts = 5

User = PasswordPete

Event = 4740

Failed Password Attempts = 2

etc.

using the following query:

index=wineventlog sourcetype=WinEventLog source="WinEventLog:Security" EventCode = 4740 AND user="stuarj*"
| eval User = user
| eval eventTime=strftime(_time, "%Y%m%d")
| eval source_account = mvindex(Account_Name,0)
| eval target_account = mvindex(Account_Name,1)
| join User, eventTime
[search index=wineventlog sourcetype=WinEventLog source="WinEventLog:Security" EventCode=4625 AND Logon_Type=2
| eval UserList=split(user,"@") | eval User=mvindex(UserList,0)
| eval eventTime=strftime(_time, "%Y%m%d")
| eventstats count(RecordNumber) AS "Failed Password Attempts"
| fields UserList, User, eventTime, BPT, "Failed Password Attempts"]
| rename dest_nt_domain AS "Domain", ComputerName AS "Actioned Server", Caller_Computer_Name AS "Lockout source", source_account AS "Admin Account", target_account AS "Account Locked", EventCode AS "Event Code", status AS "Status", "EventCodeDescription" AS "Description", _time AS "Time"
| convert timeformat="%d/%m/%Y %H:%M:%S" ctime("Time")
| table Domain, "Actioned Server", "Lockout source", "Admin Account", "Account Locked", "Failed Password Attempts", "Event Code", Status, Description, Time
| sort Time

Currently the value for Failed Password Attempts is the total number of records returned for the joined query and not specific only for the user it is performing the join on. I have tried including a where clause to ensure only results for the specific user in the first query are returned e.g.

| Where User=user 

but this has not helped. How do I obtain the specific number of records returned in the second query specific to the fields performing the join only.

Easily done in SQL but this seems to behave differently.

[bowesmana]

You need to add a split by clause on your eventstats clause - that is just calculating the count for all rows.

| eventstats count(RecordNumber) AS "Failed Password Attempts" by User

However, it is generally preferable to avoid join in Splunk as there are hit count and time constraints with sub searches. Generally you can always achieve the same result faster using stats

Although this is not your final query, this will given you an example of how stats can be used to get the failed count using the eval as part of the stats with the split by user clause.

index=wineventlog sourcetype=WinEventLog source="WinEventLog:Security" (EventCode = 4740) OR (EventCode=4625 AND Logon_Type=2)
| eval User = if(EventCode=4740, user, mvindex(split(user,"@"),0))
| eval eventTime=strftime(_time, "%Y%m%d")
| eval source_account = mvindex(Account_Name,0)
| eval target_account = mvindex(Account_Name,1)
| eval eventTime=strftime(_time, "%Y%m%d")
| stats sum(eval(if(EventCode=4625,1,0))) AS "Failed Password Attempts" by user

This might help point you in the right direction.