How to merge a search result with multiple fields and a dbquery with multiple columns matching on one (or more) fields or columns?

[rrax619]

I have a table in Oracle that monitors user logins to web apps. When a user accesses the webpage, I see the following in my access log:

192.168.100.12 - user1 [28/Sep/2016:13:11:17 -0700] [ecid: 005FP^^R3NfBh4HLyu_AiWi0x7FV002xSS] "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:45.0) Gecko/20100101 Firefox/45.0" "GET /my_app HTTP/1.1" 302 263 17480 nifit.llnl.gov

and the following in session table:

user1 2016-09-28 11:41:27 2016-09-28 13:11:17 2016-09-28 15:41:27

And I have a field extraction setup for access common that returns ip address (ip_addr), user (username), request context (application)

To see the database session information in Splunk, I use:

|dbquery USER_SESS "select userid, 
                     to_char(login_time,'YYYY-MM-DD HH24:MI:SS') AS created, 
                     to_char(last_accessed,'YYYY-MM-DD HH24:MI:SS') AS last_accessed, 
                     to_char(expiration,'YYYY-MM-DD HH24:MI:SS') AS expiry
                from USER_SESSION

The above query returns:

> USERID CREATED LAST_ACCESSED EXPIRY
> user1 2016-09-28 11:41:27 2016-09-28 13:11:17 2016-09-28 15:41:27

To view the access_common fields, I can use:

index=sds_prod sourcetype=access_common | table ip_addr, username, application

That returns:

> IP_ADDR USERNAME APPLICATION
> 192.168.100.12 user1 /my_app

I would like to merge the two results so I can see a report with IP_ADDR, USERNAME, APPLICATION, LOGIN_TIME, LAST_ACCESSED, EXPIRATION:

> IP_ADDR USERNAME APPLICATION CREATED LAST_ACCESSED EXPIRY
> 192.168.100.12 user1 /my_app 2016-09-28 11:41:27 2016-09-28 13:11:17 2016-09-28 15:41:27

The results from the access common log is matched to the database table on the Username/USERID field/column. For now I am not worried about matching the timestamps (i.e., the timestamps in the access common log is within the created/expiry time in database) as I am only looking at the last 24 hours.

I was looking into subsearch but I am not clear how that works with a search + dbquery combination!

[javiergn · Accepted solution]

Probably not the most efficient option but if your data volumes are not huge you should be able to achieve what you want with this;

 |dbquery USER_SESS "select userid, 
                      to_char(login_time,'YYYY-MM-DD HH24:MI:SS') AS created, 
                      to_char(last_accessed,'YYYY-MM-DD HH24:MI:SS') AS last_accessed, 
                      to_char(expiration,'YYYY-MM-DD HH24:MI:SS') AS expiry
                 from USER_SESSION
| join userid [
    | search index=sds_prod sourcetype=access_common earliest=-1d
    | stats count by ip_addr, username, application
    | fields - count
    | rename username as userid
]

Simply change the type of your join to left if you want to include everything from the dbquery output even if it doesn't find a match.

Thanks,
J

[rrax619]

Thanks! That allowed me to create a single report that displays the apps the user is accessing and information on their session! We only have a few hundred users and low 100k page views to handle and it works fast.

The left join was definitely what I was looking for. I added | join type=left USERID to the query and uppercased USERID for the query to work. I believe Oracle DB connector returns column headers in caps and it doesn't match otherwise.