How to join and read input from csv?

[GhanaRusk]

I've a query

index="main" app="student-api" "tags.path"=/enroll "response"=succcess

which also gives a trace_id

and then I've

index="main" app="student-api"

which gives a student_id.

I want to get the latest timestamp of enrollment (by joining the results) for each student_id (stored in a csv).

The output would look like -

student_id| latest timestamp of enrollment

Please suggest the steps to follow. I tried

index="main" app="student-api" tags.student_id | join type=inner trace_id
[| search index="main" app="student-api" "tags.path"="/enroll" "response"=success]

for the join, but it's not yielding the result. Also how to inputlookup the student_id from csv?

Appreciate your help with this. Thanks

@ITWhisperer @gcusello 

[GhanaRusk]

also inside if should those be == vs =?

[bowesmana]

You don't need to use == 

[bowesmana]

It's not totally clear what 3 bits of data come from each place

index="main" app="student-api" tags.student_id

gives you a student id AND a trace_id?

index="main" app="student-api" "tags.path"="/enroll" "response"=success

gives you a trace_id but no student id

What information do you need from this event if you plan to get the latest timestamp of enrollment from the CSV?

Its a good idea to avoid using join - normally you join datasets in Splunk with the stats command, e.g.

index="main" app="student-api" (tags.student_id OR "tags.path"="/enroll" response=success)
| stats values(student_id) as student_id
        max(eval(if("tags.path"="/enroll" AND response=success, _time, null()))) as last_enrollment by trace_id

This searches both data sets and will give you the last time when an event had a successful enrollment for any given trace id and the value of any associated student id for that trace id.

If you want to look things up from a lookup file use

| lookup mylookup.csv student_id 

where student_id is a field in the lookup and your data

[GhanaRusk]

index="main" app="student-api" tags.student_id
gives you a student id AND a trace_id?

Correct

index="main" app="student-api" "tags.path"="/enroll" "response"=success
gives you a trace_id but no student id

Correct

What information do you need from this event if you plan to get the latest timestamp of enrollment from the CSV?

I get only the student_id from csv

I want the latest date of successful enrollment  for a given student id.

I'll give your query a try - thanks

[bowesmana]

If you have the student id from the event search 

index="main" app="student-api" tags.student_id

what is your purpose for the CSV lookup?

[GhanaRusk]

We'd like to get only the latest enrollment success dates for students in the csv

[bowesmana]

I still don't get it, but maybe I don't need to - your search that searches your index for /enroll - isn't that giving you the latest date?

[GhanaRusk]

I get - 

Error in 'stats' command: The eval expression for dynamic field 'if("tags.path"="/enroll" AND "response"=success, _time, null())' is invalid. Error='Type checking failed. The '==' operator received different types.'.

But if I do this 

"tags.path"=="/enroll" AND "response"=="success"

it gets past that error and runs.

So to explain it say the csv has - student1 and student2
I'd like to get the latest enrollment date for those 2 students. 
Now, since index="main" app="student-api" "tags.path"="/enroll" "response"=success doesn't allow us to filter by student_id, I am trying to get the trace_id from that query, join it with index="main" app="student-api" (which allows us to filter by student_id)  based on that trace_id and then use the 2 ids from the csv to filter on that result.

[bowesmana]

The error was caused by not quoting "success", it should have been

max(eval(if("tags.path"="/enroll" AND response="success", _time, null())))

[GhanaRusk]

thanks that was it. 

Now the query is not yielding any result still - curious where does it "join" by trace_id? is `by trace_id` on stats enough?

[bowesmana]

Please post an example of a data event and the search you are running - also please mask any sensitive information.