Table 1 -Student_id Student_name Address
101 john Bang
105 han Singa
102 vish India
103 ram Lond
Table 2 - Student_name Status marks
students are attempting for exams multiple times , need to extract only failed student details, can any one help
Student_name Status marks
john fail 30
han fail 10
ram fail 20
vish Pass 50
han Pass 90
ram Pass 50
The output should as below after combining data from both logs - as ram as passed in second attempt should not display
Student_id Address Student_name Status marks
101 Bang john fail 30
105 Singa han fail 10
... search to gather entries from both sources e.g. index=Table1 OR index=Table2
| stats values(Student_Id) as Student_Id, values(Address) as Address, values(Status) as Status, values(marks) as marks by Student_name
| eval failed=if(isnull(mvfind(Status,"Pass")),"fail", "Pass")
| where failed="fail"
| table Student_id, Address, Student_name, Status, marks
... search to gather entries from both sources e.g. index=Table1 OR index=Table2
| stats values(Student_Id) as Student_Id, values(Address) as Address, values(Status) as Status, values(marks) as marks by Student_name
| eval failed=if(isnull(mvfind(Status,"Pass")),"fail", "Pass")
| where failed="fail"
| table Student_id, Address, Student_name, Status, marks