Hi All,
I'm not very experienced with Splunk searches and I haven't been able to get this working so I'm hoping someone can help me out. What I would like to do is the following:
I've come up with the following search to accomplish the first step:
sourcetype="sso" ValidateUsernamePasswordAgainstLDAP succeeded earliest=-25m latest=-10m | makemv delim="'" _raw | eval ssouser=mvindex(_raw,1) | table ssouser | dedup ssouser
My thinking was that in order to accomplish step (2), I would need to iterate over the results and use a subsearch. Since I came at this from a 'coding' perspective, I figured I'd need to use foreach to create a loop that iterates over the results:
sourcetype="sso" ValidateUsernamePasswordAgainstLDAP succeeded earliest=-25m latest=-10m | makemv delim="'" _raw | eval ssouser=mvindex(_raw,1) | table ssouser | dedup ssouser | foreach ssouser [search sourcetype="duo:authentication"]
It's as if this performs two separate searches, though: The results first show what's found with the subsearch (with nothing in the ssouser column), followed by the list of usernames found in the main search (which appear in the ssouser column). So obviously this isn't the way to go about it. I've since learned that the idea of using a loop isn't really the way to approach this, so instead I just tried a subsearch without foreach:
sourcetype="sso" ValidateUsernamePasswordAgainstLDAP succeeded earliest=-25m latest=-10m | makemv delim="'" _raw | eval ssouser=mvindex(_raw,1) | dedup ssouser | table ssouser [search sourcetype="duo:authentication"]
This returns the following error:
Error in 'table' command: Invalid argument: 'action=success'
So it seems that it's treating the results of the subsearch as an argument. I've been tweaking this search and reading posts by others trying to accomplish similar things but I haven't had any luck moving forward.
Any suggestions?
The subsearch is always preformed first then the results are returned to be used as input to the outer search.
https://docs.splunk.com/Documentation/Splunk/7.0.2/SearchTutorial/Useasubsearch
So you'll want to do your username search as the subsearch and then the outer will be your check to see if they exist. Something like this:
sourcetype="duo:authentication" [sourcetype="sso" ValidateUsernamePasswordAgainstLDAP succeeded earliest=-25m latest=-10m | makemv delim="'" _raw | eval ssouser=mvindex(_raw,1) | fields ssouser ] | dedup ssouser | table ssouser
@kmaron and @richgalloway,
Ah, thanks for the info; that makes sense. I tried both of the suggestions by first testing the subsearches. This one returns a single result that looks like a crazy, multi-page version of the query (rather than just the results):
sourcetype="sso" ValidateUsernamePasswordAgainstLDAP succeeded earliest=-25m latest=-10m | makemv delim="'" _raw | eval ssouser=mvindex(_raw,1) | dedup ssouser | format
But I understand the point of swapping the searches. This one does produce the list of usernames that recently authenticated against our SSO system:
sourcetype="sso" ValidateUsernamePasswordAgainstLDAP succeeded earliest=-25m latest=-10m | makemv delim="'" _raw | eval ssouser=mvindex(_raw,1) | fields ssouser | dedup ssouser | table ssouser
I then just did the search sourcetype="duo:authentication" and confirmed some of the usernames that show up in the previous search also show up in this search. Since they do, I then tried the full search:
sourcetype="duo:authentication" [search sourcetype="sso" ValidateUsernamePasswordAgainstLDAP succeeded earliest=-25m latest=-10m | makemv delim="'" _raw | eval ssouser=mvindex(_raw,1) | fields ssouser | dedup ssouser | table ssouser ]
This produces no results, though. Very odd since I verified that results of the subsearch do appear in the main search if they're done separately.
Edited for formatting.
does the field name ssouser exist for your sourcetype="duo:authentication" search? and have that username as a value?
No, it doesn't! The equivalent field name for sourcetype="duo:authentication" is username; so I changed ssouser to username and it looks like it works. Thanks!
Subsearches execute first so try swapping your searches around like this.
search sourcetype="duo:authentication" [sourcetype="sso" ValidateUsernamePasswordAgainstLDAP succeeded earliest=-25m latest=-10m | makemv delim="'" _raw | eval ssouser=mvindex(_raw,1) | dedup ssouser | format]
The subsearch is always preformed first then the results are returned to be used as input to the outer search.
https://docs.splunk.com/Documentation/Splunk/7.0.2/SearchTutorial/Useasubsearch
So you'll want to do your username search as the subsearch and then the outer will be your check to see if they exist. Something like this:
sourcetype="duo:authentication" [sourcetype="sso" ValidateUsernamePasswordAgainstLDAP succeeded earliest=-25m latest=-10m | makemv delim="'" _raw | eval ssouser=mvindex(_raw,1) | fields ssouser ] | dedup ssouser | table ssouser