Dashboards & Visualizations

correlating events from two sourcetypes, with a time range and field match

tgeer123
Path Finder

table Sessions contains user, ip, starttime and endtime.
table Activity contains ip, _time, miscdata

I will select a timerange (working fine)
I will select a user from a pulldown (working fine)
The resulting table give me all their Sessions during that timeframe (working fine)

Here is the part I can't seem to get-
I need to iterate through each row of those results and find all the events in Activity where the ip matches, and the event falls between starttime and end time of that row.

Basically giving me all Activity for that User, during that timeframe so I can do statistics on them.

I can show the data for a specific session, just not all of them.

The session ip's are reused, which is why I have to initially pull a list of user sessions.

Ideas?

Tags (1)
0 Karma

somesoni2
Revered Legend

Try something like this (assuming Activity is a sourcetype, update as per how you use]

sourcetype=Activity [search sourcetype=Sessions | eval earliest=starttime | eval latest=endtime | table ip, earliest,latest | format "" "" "" "" "" ""] | table ip, _time, miscdata

This will get all the combination of ip, startime and endtime from Sessions table and pass it as filter to Activity table, but instead of one single session parameters, it will pass all the rows.

0 Karma

tgeer123
Path Finder

Ok so I have this semi working. The problem now is that when there is a time range picker set, it overwrites the earliest and latest in the url, and returns wrong events

0 Karma

tgeer123
Path Finder

AHHHHH FORMAT "(" "(" " " ")" "OR" ")" 🙂 Splunk doesn't like those AND's

0 Karma

tgeer123
Path Finder

eh pesky character limit! To clarify changes above- instead of using a table generated from a scheduled search (Sessions)- I used the search itself. That seems to have fixed the starttime value problem.

0 Karma

tgeer123
Path Finder

changed some things

sourcetype=usersess | transaction user startswith="STARTED" endswith="ENDED" keepevicted=true | search src_ip="*" | eval earliest=_time | eval latest=_time+duration | table src_ip, earliest, latest | format

I can get this return:
(( earliest="1399635764" AND latest="1399635975" AND src_ip="192.168.101.223") OR (earliest="1397581824" AND latest="1397581834" AND src_ip="192.168.101.212"))

but putting it all together - I get this error:
Error in 'search' command: Unable to parse the search: 'AND' operator is missing a clause on the left hand side.

0 Karma

somesoni2
Revered Legend

Check if the data from below query has correct value for starttime and endtime field.

sourcetype=Sessions | eval earliest=starttime | eval latest=endtime | table ip, earliest,latest.

0 Karma

tgeer123
Path Finder

Invalid value "starttime" for time term 'earliest'

0 Karma

somesoni2
Revered Legend

Try the updated answer.

0 Karma

tgeer123
Path Finder

they are in epoch time 1402587416 1402587403 etc

0 Karma

somesoni2
Revered Legend

What is the format of values for starttime and endtime? can you post some sample values?

0 Karma

tgeer123
Path Finder

eh...and now this one...
Error in 'search' command: Unable to parse the search: 'AND' operator is missing a clause on the left hand side.

I'm using exactly what I posted above. I haven't put this in a new view, was trying to work it out in a search first.

0 Karma

tgeer123
Path Finder

I think I figured out no results..fields were not extracted properly..however I now get this error:
Invalid value "starttime" for time term 'earliest'

Do I need to do some sort of conversion?

0 Karma

somesoni2
Revered Legend

can you post the search query that you're using to show data from Activity and Sessions?

0 Karma

tgeer123
Path Finder

sourcetype=activity [search sourcetype=sessions | eval earliest=starttime | eval latest=endtime | table src_ip, earliest, latest | format] | table src_ip, _time, miscdata

returned no results.

0 Karma

tgeer123
Path Finder

The view has a pulldown that filters the sessions by user, thus producing a sessions results table.

Then I use sideview utils Redirector module to redirect to the session drilldown view. I pass the arguments:

ip=$ip$
earliest=$starttime$
latest=$endtime$

I need to create a new view, but instead of drilldown to session, I need to gather all the events for each of the users sessions, and create some stats visuals.

0 Karma

somesoni2
Revered Legend

Can you post your query where you can show data for a specific session?

0 Karma
Get Updates on the Splunk Community!

New Case Study Shows the Value of Partnering with Splunk Academic Alliance

The University of Nevada, Las Vegas (UNLV) is another premier research institution helping to shape the next ...

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...