This is the query:
source=Audit earliest=-2d
[search source=Audit | stats count by persistent_id | where count > 20 and isnotnull(persistent_id) | fields persistent_id]
| JOIN max=0 Audit_Id
[ SEARCH source= Applicant ]
| table Audit_Id , FirstName, LastName
It first selects any rows from the audit table that have a not null persistent_id that occurs in the table more than 20 times. Then if any rows that have that persistent_id have turned up in the last 2 days it joins them to the Applicant table and returns a table result with the audit id and the names
The problem is that the join only returns the first match even though the max=0 setting is set.
I am trying to translate this sql query:
SELECT Audit_Id, FirstName, LastName
FROM Audit JOIN Applicant ON Audit_Id
WHERE persistent_id IN
(
SELECT persistent_id from Audit group by persistent_id having count(*)>20 and persistent_id is not null
)
and timestamp > DATEADD(hh, -48, GETDATE());
The sql returns
Audit_Id FirstName LastName
414 joe strummer
422 johny rotten
422 sid vicious
Whereas the splunk search returns
Audit_Id FirstName LastName
414 joe strummer
422 johny rotten
... View more