For each result in a search, make a second search to extract a couple of fields from other records?

[bowesmana]

I have two types of data input

1. login records containing at least Userid, Name, LoginDate
2. user records containing at least Userid, Company

The Userid is common. I am producing a report of the top 20 users logging into the system in a month from data set 1, but want to include the company from data set 2 in each line of the report, so my report will have

Name, Company, Count of logins

I have a search

sourcetype=logins | top showperc=false countfield=Logins limit=20 Name

and that shows the name and count. In order to get the company I have read lots and have so far got

sourcetype=logins | top showperc=false countfield=Logins limit=20 Name | append [ search sourcetype=users | top 1 showperc=false Company by UserId ]

Which is giving me sort of the right information but not correctly combined, i.e. I get the first 20 lines showing Name and Count and then more lines giving me the company name and some other counts and info

[bowesmana]

Perfect, you are my hero forever MuS! Thanks! I can't mark this as the correct answer as it's just a comment. If you write an answer, I'll mark it as the correct one.

[MuS · Accepted solution]

Here we go - Thanks for accepting the answer 😉

first search | join Userid [ second search ]

cheers, MuS

[bowesmana]

OK, I changed append to appendcols, but that's still not right as it's appending unrelated content to the original rows.