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

[tgeer123]

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?

[somesoni2]

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.

[tgeer123]

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

[tgeer123]

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

[tgeer123]

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.

[tgeer123]

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.

[somesoni2]

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.

[tgeer123]

Invalid value "starttime" for time term 'earliest'

[somesoni2]

Try the updated answer.

[tgeer123]

they are in epoch time 1402587416 1402587403 etc

[somesoni2]

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

[tgeer123]

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.

[tgeer123]

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?

[somesoni2]

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

[tgeer123]

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.

[tgeer123]

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.

[somesoni2]

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