issue with join and matching multiple lines

[jmsiegma]

Need a second set of eyes on this one please..

I am trying to create a report for Failed VPN connections on a cisco ASA. But I want to be able to not only detect that the user's auth failed, but also retrieve the SRC IP address for that user when it failed.

I am using these two lines from the ASA logs as an example:

[search index="cisco_asa" (log_level=6) (error_code=113005)]

May 9 15:03:47 10.80.185.11 May 09 2013 15:03:47: %ASA-6-113005: AAA user authentication Rejected : reason = AAA failure : server = 10.1.1.1 : user = USER_A

[Search index="cisco_asa" log_level=7 error_code=715046]

May 9 15:03:47 10.80.185.11 May 09 2013 15:03:47: %ASA-7-715046: Group = VPN_GROUP, Username = USER_A, IP = 208.1.1.100, constructing qm hash payload

So the idea is to join these together like this:

index="cisco_asa" (log_level=6) (error_code=113005) USER_A | rename user as Username | join user usetime=true earlier=false [search index="cisco_asa" log_level=7 error_code=715046 USER_A | fields src_ip | head 1 ]

This works just fine as long as I am searching for a single user. src_ip will get populated with the proper IP address for that failed user's session.

My problem comes into play when I search for all users in the last 24 hours, as an example using this search string:

index="cisco_asa" (log_level=6) (error_code=113005) | rename user as user_id | join
user usetime=true earlier=false [search index="cisco_asa" log_level=7 error_code=715046 | head 1] | lookup geoip clientip as IP | top limit=100 showcount=false showperc=false _time host user_id src_ip client_country client_region client_city client_lat client_lon| rename host as "ASA IP", user_id as "User ID", src_ip as "Public IP", client_country as "Country", client_region as "Region", client_city as "City", _time as "Time", client_lat as "LAT", client_lon as "LON" | sort -Time | convert ctime(Time)

What happens here is that the 'inner' search happens, and then it will overwrite all found users after the fact, and the entire table that is outputed will display the proper time for a failed login, but will display the exact same user and data for all of them.

Anyone have any idea where I am going wrong? What could I be missing?

Been chasing my tail on this, and I'm still cutting my teeth on splunk.. So all help welcome.

Thank you.

[jmsiegma · Accepted solution]

From everyone's suggestions this is what I came up with, Thank you

index="cisco_asa" (log_level=6 OR log_level=7) (error_code=113005 OR error_code=715046) * | transaction user user_id startswith="AAA failure" endswith="constructing" maxevents=2 | lookup geoip clientip as IP | top limit=100 showcount=false showperc=false _time host user_id src_ip client_country client_region client_city client_lat client_lon| rename host as "ASA IP", user_id as "User ID", src_ip as "Public IP", client_country as "Country", client_region as "Region", client_city as "City", _time as "Time", client_lat as "LAT", client_lon as "LON" | sort -Time | convert ctime(Time)

[ytamura]

not sure if you're actually trying to join by user_id and not user? don't know how your fields are parsed. it might be your problem if the first part of the join doesn't have any "user" fields, and you're trying to join it with just 1 event in the second part of your search.

consider using a subsearch (search for all users with failed logins in an inner search and then search for IPs) or transaction like bmacias84 said. good luck!

[bmacias84]

consider using transaction command this seems like a great use of the command.