Archive

Performing Second Search on Results of First

Engager

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:

  1. Perform a search for all successful authentication attempts against our SSO system and extract the usernames.
  2. Perform a search on each username in the results against a different sourcetype and see if any results are returned.
  3. If no result is returned for a particular username, report on that fact.

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?

Tags (1)
0 Karma
1 Solution

Motivator

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 

View solution in original post

Engager

@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.

0 Karma

Motivator

does the field name ssouser exist for your sourcetype="duo:authentication" search? and have that username as a value?

0 Karma

Engager

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!

0 Karma

SplunkTrust
SplunkTrust

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]
---
If this reply helps you, an upvote would be appreciated.
0 Karma

Motivator

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 

View solution in original post

Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!