topic Re: Create a search using data from csv and grabbing the latest timestamps for multiple queries in Splunk Search

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

GhanaRusk — Wed, 15 Feb 2023 20:52:57 GMT

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

Re: Create a search using data from csv and grabbing the latest timestamps for multiple queries

GhanaRusk — Wed, 15 Feb 2023 07:25:34 GMT

@gcusello thoughts on this one?

Re: Create a search using data from csv and grabbing the latest timestamps for multiple queries

gcusello — Wed, 15 Feb 2023 07:39:18 GMT

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

Re: Create a search using data from csv and grabbing the latest timestamps for multiple queries

ITWhisperer — Wed, 15 Feb 2023 09:39:38 GMT

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

Re: Create a search using data from csv and grabbing the latest timestamps for multiple queries

GhanaRusk — Wed, 15 Feb 2023 18:14:21 GMT

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

Re: Create a search using data from csv and grabbing the latest timestamps for multiple queries

GhanaRusk — Wed, 15 Feb 2023 18:26:27 GMT

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)

Re: Create a search using data from csv and grabbing the latest timestamps for multiple queries

ITWhisperer — Wed, 15 Feb 2023 18:33:38 GMT

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

Re: Create a search using data from csv and grabbing the latest timestamps for multiple queries

GhanaRusk — Wed, 15 Feb 2023 18:52:18 GMT

thanks - getting closer - I tried

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

Re: Create a search using data from csv and grabbing the latest timestamps for multiple queries

GhanaRusk — Wed, 15 Feb 2023 19:53:47 GMT

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?

Re: Create a search using data from csv and grabbing the latest timestamps for multiple queries

GhanaRusk — Wed, 15 Feb 2023 20:25:48 GMT

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

Re: Create a search using data from csv and grabbing the latest timestamps for multiple queries

gcusello — Thu, 16 Feb 2023 07:26:49 GMT

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 😉

Re: Create a search using data from csv and grabbing the latest timestamps for multiple queries

GhanaRusk — Thu, 16 Feb 2023 17:54:25 GMT

I did - thanks