How can I combine search 1 and search 2 and display the search 2 table only for the top 5 who were in the search 1 table ?

[pavanae]

I have my two searches as below

search 1

index=xyz source=yhg | top 5 student_id

search 2

index=xyz source=yhg | convert ctime(_time) as Date_and_Time|convert timeformat="%m/%d/%Y %H:%M:%S" mktime(_time) as time  |eventstats range(time) as duration by student_id| stats avg(duration) as avgDurationPer_student_id by sudent_id | eval Total_time_spent(out_of_collage)=tostring(ceil(avgDurationPer_student_id), "duration") | table student_id Total_time_spent(out_of_collage) 

Search 1 displays a table for the top 5 student_id's based on the event count

Search 2 displays the result of the total time spent on the website by each student_id as below(lists all the student_id's)

student_id           Total_time_spent(out_of_collage)
X123345                19:39:35
H900639             20:05:58
D900643             17:47:40
V106127                00:00:00

Now how can I combine search 1 and search 2 and display the search 2 table only for the top student_id's who were in the search 1 table?

[niketn]

You can try either one of the following correlation techniques:

1) append
2) appendcols
3) join
4) transaction
5) stats or eventstats
6) subquery

I would assume simplest to be appendcols, but depends on your use case, existing queries and data.

[pradeepkumarg · Accepted solution]

Try this

index=xyz source=yhg [index=xyz source=yhg | top 5 student_id | table student_id]| convert ctime(_time) as Date_and_Time|convert timeformat="%m/%d/%Y %H:%M:%S" mktime(_time) as time |eventstats range(time) as duration by student_id| stats avg(duration) as avgDurationPer_student_id by sudent_id | eval Total_time_spent(out_of_collage)=tostring(ceil(avgDurationPer_student_id), "duration") | table student_id Total_time_spent(out_of_collage)

Basically the idea is you run your search1 as a sub search which will execute first and pass the results to the outer search(search 2) to filter on

https://docs.splunk.com/Documentation/Splunk/6.5.0/SearchTutorial/Useasubsearch