I'm trying to join the two queries together one which queries the total number of accesses by a student and then the inner search tries to match the student's username to find the faculty they belong to.
If I specify the students username in both of the main and inner search the join command returns the desired result. But if I remove the students username as I want to run this query over all students in a given month, it seems like the join command doesn't match up on the username correctly and then returns no faculty information for most of the records which is incorrect because I know they exist in the summary index. What I want is for all rows in main search to merge with faculty info in the summary index which I know exist but atm there is no faculty information returned when I do this join:
index="app" (connect? OR login?) session!=- source="/srv/home/logfiles.log"
| stats dc(session) AS SessionCount BY url, username
| rename username as USERNAME
| table USERNAME, url, "SessionCount"
| join type=left USERNAME [search index="student-summary" source="SummaryIndex"
| table USERNAME, FACULTY
| dedup FACULTY | mvcombine FACULTY]
This works perfectly (if specifying student username jobloggs):
index="app" (connect? OR login?) session!=- source="/srv/home/logfiles.log" jobloggs
| stats dc(session) AS SessionCount BY url, username
| rename username as USERNAME
| table USERNAME, url, "SessionCount"
| join type=left USERNAME [search index="student-summary" source="SummaryIndex" jobloggs
| table USERNAME, FACULTY
| dedup FACULTY | mvcombine FACULTY]
