topic Re: Join and read input from csv in Splunk Search

How to join and read input from csv?

GhanaRusk — Wed, 22 Feb 2023 15:28:34 GMT

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

Re: Join and read input from csv

bowesmana — Wed, 22 Feb 2023 06:51:03 GMT

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

Re: Join and read input from csv

GhanaRusk — Wed, 22 Feb 2023 07:04:15 GMT

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

Re: Join and read input from csv

GhanaRusk — Wed, 22 Feb 2023 07:28:33 GMT

also inside if should those be == vs =?

Re: Join and read input from csv

bowesmana — Wed, 22 Feb 2023 07:29:58 GMT

You don't need to use ==

Re: Join and read input from csv

bowesmana — Wed, 22 Feb 2023 07:31:23 GMT

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?

Re: Join and read input from csv

GhanaRusk — Wed, 22 Feb 2023 07:32:40 GMT

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

Re: Join and read input from csv

bowesmana — Wed, 22 Feb 2023 07:38:04 GMT

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?

Re: Join and read input from csv

GhanaRusk — Wed, 22 Feb 2023 07:53:18 GMT

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.

Re: Join and read input from csv

bowesmana — Wed, 22 Feb 2023 08:17:26 GMT

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

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

Re: Join and read input from csv

GhanaRusk — Wed, 22 Feb 2023 08:45:16 GMT

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?

Re: Join and read input from csv

bowesmana — Wed, 22 Feb 2023 09:42:43 GMT

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