How to create a search using data from csv and grabbing the latest timestamps for multiple queries?

[GhanaRusk]

I've a couple of queries - 

index="main"app="student-api" "tags.studentId"=3B70E5 message="Id and pwd entered correctly" | sort _time desc

and

index="main" app="student-api" "tags.decision"=SOP_REQUIRED "tags.studentId"=3B70E5 | sort _time desc

I'd like to grab just the latest timestamp from both the results (and status code from one of them). However I'd like to do this reading the tags.studentId from a csv file (the fieldname is student_id and has ~100 entries). So the output should look like - 

student_id| latest timestamp from 1st query| latest timestamp from 2nd query|status code from 2nd query

I installed Lookup Editor.  Please let me know what next steps to follow (if there is alternative to Lookup Editor please suggest that too). 

Thanks

[ITWhisperer]

Try something like this

index="main"app="student-api" (message="Id and pwd entered correctly" OR "tags.decision"=SOP_REQUIRED) [|inputlookup students.csv
  | fields tags.studentId]
| stats latest(eval(if(message=="Id and pwd entered correctly",_time,null()))) as first_timestamp latest(eval(if('tags.decision'=="SOP_REQUIRED",_time,null()))) as second_timestamp latest(eval(if('tags.decision'=="SOP_REQUIRED",status,null()))) as status by tags.studentId

[GhanaRusk]

thanks - this is not yielding results - I think we've to use the student_id in csv - where are we doing that?
(I mean the csv header is student_id while the query needs tags.studentId)

[ITWhisperer · Accepted solution]

Try it like this then

index="main"app="student-api" (message="Id and pwd entered correctly" OR "tags.decision"=SOP_REQUIRED) [|inputlookup students.csv
  | fields student_id
  | rename student_id as "tags.studentId"]
| stats latest(eval(if(message=="Id and pwd entered correctly",_time,null()))) as first_timestamp latest(eval(if('tags.decision'=="SOP_REQUIRED",_time,null()))) as second_timestamp latest(eval(if('tags.decision'=="SOP_REQUIRED",status,null()))) as status by tags.studentId

[GhanaRusk]

thanks - getting closer - I tried 

index="main"app="student-api" (message="Id and pwd entered correctly" OR "tags.decision"=SOP_REQUIRED) [|inputlookup students.csv
  | fields student_id
  | rename student_id as "tags.studentId"]
| stats latest(eval(if(message=="Id and pwd entered correctly",_time,null()))) as first_timestamp latest(eval(if('tags.decision'=="SOP_REQUIRED",_time,null()))) as second_timestamp by tags.studentId

 the studentId is showing up but the timestamps are empty - I ran the query separately and I see the events showing up as expected.

[GhanaRusk]

Also I just tried 

index="main" app="student-api" tags.studentId=3B70E5 (message="Id and pwd entered correctly" OR "tags.decision"=SOP_REQUIRED) | stats latest(_time) 

and the timestamp came up like 1675785328.602059
Do we need to do some conversion?

[GhanaRusk]

I could fix it - thanks @ITWhisperer - your solution pointed me to the right direction

[gcusello]

HI @GhanaRusk 

good for you, see next time!

Please accept one answer for the other people of Community

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉

[GhanaRusk]

I did - thanks

[gcusello]

Hi @GhanaRusk,

if you want only the latest event, you can add Head 1 to your search:

index="main"app="student-api" "tags.studentId"=3B70E5 message="Id and pwd entered correctly" 
| sort _time desc
| head 1

I don't understand what you mean when you say: "I'd like to do this using data from a csv file".

Anyway, you can correlate the two searches using the stats command:

index="main"app="student-api" (("tags.studentId"=3B70E5 message="Id and pwd entered correctly") OR ("tags.decision"=SOP_REQUIRED "tags.studentId"=3B70E5)) 
| stats 
   latest(eval(if(message="Id and pwd entered correctly",_time,""))) AS  latest_timestamp_from_1st_query
   latest(eval(if("tags.decision"=SOP_REQUIRED,_time,""))) AS latest timestamp_from_2nd_query
   last(eval(if("tags.decision"=SOP_REQUIRED,status_code,""))) AS status_code
   BY student_id

Ciao.

Giuseppe

[GhanaRusk]

Thanks - I meant I've a csv with student ids that I'd like to use as input to the query

[GhanaRusk]

@gcusello thoughts on this one?