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