topic Re: Finding fields in more than one list (subsearch? append?) in Splunk Search

Finding fields in more than one list (subsearch? append?)

howyagoin — Thu, 27 Oct 2011 06:36:37 GMT

Hi,

I have a list of login events, some which fail, some which succeed. In some cases, the IP address that succeeds is different than the one that failed.

I'm looking for a way to find the failures, extract the account name and IP address, then search for succeeds by the same username and generate a table of user, failure IP and succeed IP.

Both subsearches and append seem like they might help me get there, but I can't seem to find the right combination.

Pretend the log entry looks like:

Login succeeded for fred from 1.2.3.4
Login failure from 5.6.7.8 for fred

I'd like a table that appeared as:

User     Fail     Succeed
fred     5.6.7.8  1.2.3.4

What's the best way to get there?

Thanks!

Re: Finding fields in more than one list (subsearch? append?)

lguinn2 — Thu, 27 Oct 2011 06:46:12 GMT

What if there are multiple succeeds/fails for a single user? Is there a time requirement or a sequence of events requirement?

Sorry to ask more questions, but I have a couple of ideas, and your answers may make my ideas either useful or stupid!

Re: Finding fields in more than one list (subsearch? append?)

kristian_kolb — Thu, 27 Oct 2011 07:16:17 GMT

Hi,

I believe this will work for you, as far as I understand your question. Assuming that you have user names, login-status (i.e. succeeded/failure) and ip-addresses extracted into fields - let's call them 'userID', 'clientIP' and 'loginStatus' for now, you can the execute a search as follows;

 sourcetype=<your_sourcetype> [search loginStatus="failure" | fields userID] | chart values(clientIP) over userID by loginStatus

Please note that I have used different log data to play around with (access_combined logs, charting with clientip, status and method).

Hope this helps,

Kristian

Re: Finding fields in more than one list (subsearch? append?)

howyagoin — Thu, 27 Oct 2011 07:19:08 GMT

Heh, completely valid questions.

In an ideal universe, I'd like a list of mvcombine type output where all of the Fails and all of the Succeeds are lumped together (unique IP addresses), even a delimiter separated list of Failed IPs and Succeed IPs would be fine.

Time isn't so important for this issue as I'm happy with the timerange in the query settling that. I'd love to know how to get the first occurrence of a given IP for each fail/succeed, but realise that may be complicated.

Re: Finding fields in more than one list (subsearch? append?)

howyagoin — Thu, 27 Oct 2011 07:55:08 GMT

Interesting suggestion, I'll have to try that - issue for me at the moment is that the fields are being extracted through "| rex field=_raw ..." statements which is making matching the condition where there has to be a failure event AND a success event for the same user trickier. But I see options here, thanks, will poke at it some more.

Re: Finding fields in more than one list (subsearch? append?)

lguinn2 — Thu, 27 Oct 2011 08:04:30 GMT

Here is what I have, although the result is not formatted as I would like...

I assumed the field names are user and ip for the data that you need.

"Login succeeded" | stats values(ip) as Failed_Login_IPs by user | 
join user type=outer [search "Login failure" | stats values(ip) as Successful_Login_IPs by user ]

This is not fast, although the speed may be okay if you keep the time range relatively small.

Re: Finding fields in more than one list (subsearch? append?)

lguinn2 — Thu, 27 Oct 2011 08:07:48 GMT

Just saw your last comment. You can put the rex commands inline - you will have to extract the fields twice, as the two searches are independent of each other. So add the rex just before each of the stats commands.

There is probably something cool you could do with macros, so that you wouldn't have to actually type out the same rex command twice, but I am too tired now to think it through.

Re: Finding fields in more than one list (subsearch? append?)

kristian_kolb — Thu, 27 Oct 2011 09:53:34 GMT

Hi,
if you cannot (for some reason) make the field extractions permanent, you might be better off with Iguinn's solution below - as I could not get my version to work with rex, since my subsearch returns values needed for the outer search. Then again, there is probably a solution to that as well. I just don't have it.

Re: Finding fields in more than one list (subsearch? append?)

howyagoin — Thu, 27 Oct 2011 20:43:42 GMT

This worked once I remembered to tweak the rex a bit - the only thing I'm not sure of now: How do I only display values which have both succeeded and failed values? If John only fails, or John only succeeds, I care less (for this report)...its when he fails and succeeds during the course of the time-range that I care.

Re: Finding fields in more than one list (subsearch? append?)

lguinn2 — Thu, 27 Oct 2011 21:07:07 GMT

If you want ONLY matches that have both succeeded and failed values, change type=outer to type=inner

That's it!

Re: Finding fields in more than one list (subsearch? append?)

howyagoin — Thu, 27 Oct 2011 21:13:15 GMT

Solved that. Just changed the join type and some magically it's doing the right thing now. Must be the Psychic app that I put on Splunk.