topic Search to group different fields from different events in Splunk Search

Search to group different fields from different events

johnangelo — Tue, 09 Mar 2021 21:49:39 GMT

Hi! So ive been at this for hours attempting to use stats and transactions to do this.

I have two events that look like the following: Event 1: (date) (connection=1234) (op=#) (BIND) (username=[username])

Event 2: (date) (connection=1234) (op=#) (RESULT) (error=49) (INVALID CREDENTIALS)

I want to create a pivot that has it so that usernames and invalid credentials can be grouped... right now I am doing the stats command, but not getting any results because these (username and error=49) are two different events. Unfortunately, these fields do not contain unique values among each other (same connection# is shared with many other events, same op# is shared with many others)

The only thing I can think of is event2 comes directly after event1. Is there a way to group this based on time or perhaps eval?

Any suggestions?

Re: Search to group different fields from different events

ITWhisperer — Wed, 10 Mar 2021 07:11:33 GMT

I assume the connection is still important. Try something like this

| sort connection _time | streamstats current=f window=1 values(user) as user by connection

Re: Search to group different fields from different events

johnangelo — Wed, 10 Mar 2021 18:34:04 GMT

Unfortunately, that did not give me the results I was looking for. For clarity, I am attempting to group userid to failed logins, however, those events are separate from each other.

For example:

[10/Mar/2021:<time>] conn=1000 op=1 BIND dn="uid=bobjim,cn=users,cn=account,dc=random,dc=com" method=111 version=4

10/Mar/2021:<time>] conn=1000 op=1 RESULT err=49 tag=97 nentries=0 etime=0 - Invalid credentials

Re: Search to group different fields from different events

ITWhisperer — Wed, 10 Mar 2021 23:18:52 GMT

What fields are you extracting for the first event? I assumed that the user name has been extracted to a field called field user, but obviously if it has been extracted to a different field, then the streamstats has to be updated accordingly. With this change, do you get the user name on the subsequent event? If not, what do you get?

Re: Search to group different fields from different events

johnangelo — Thu, 11 Mar 2021 15:24:01 GMT

Right now I am using

host=<host> sourcetype=<srctype> | sort connection _time
| streamstats current=f window=1 values(uid) as user by conn

This does return the username, however, I would like the username to be correlated with a failed login attempt and to do a pivot/dashboard on this.

Re: Search to group different fields from different events

ITWhisperer — Thu, 11 Mar 2021 20:15:44 GMT

Guessing from your example logs, the connection is in field conn(?) so you should sort by this rather than connection?

host=<host> sourcetype=<srctype> | sort conn _time | streamstats current=f window=1 values(uid) as user by conn

This should add the user (uid) to the next record in the pipeline for the connection so the fail message should now have a user (uid) associated with it.