Matching dissimilar field titles with a Subsearch

[sheltomt]

Background is that I'm trying to pull in LDAP full names in from one search, and match that to UID from another search.

My LDAP full name query is:

|rest /servicesNS/-/-/authentication/users splunk_server=local|search NOT title=admin|fields title,realname,type,email

The "matching" field here is "title", and the value I want is "realname"

My other search grabs the top 10 search users for the past 7 days:

index=_audit action="search" search="*" NOT user="splunk-system-user" savedsearch_name="" NOT search="\'|history*" NOT search="\'typeahead*"  user!=NULL user!=admin search_id!="\'subsearch*" |bucket _time span=day |stats count by user | table user count|head 10 | sort -count

The field that returns from this is "user". So what I need is to take the value of "user", match it to previous search's "title", and spit out "realname" so I can sub that in for "user"

I can't find any good information on my exact scenario

[somesoni2 · Accepted solution]

Try like this

 index=_audit action="search" search="*" NOT user="splunk-system-user" savedsearch_name="" NOT search="\'|history*" NOT search="\'typeahead*"  user!=NULL user!=admin search_id!="\'subsearch*" |bucket _time span=day |stats count by user  | sort 10 -count
| join user type=left [ |rest /servicesNS/-/-/authentication/users splunk_server=local|search NOT title=admin|table title,realname,type,email | rename title as user]

[sheltomt]

I have no idea which of you to award solution to. They both work well, and I'll use them in future solutions. I'll give you both points.

[woodcock]

Like this:

index=_audit action="search" search="*" NOT user="splunk-system-user" savedsearch_name="" NOT search="\'|history*" NOT search="\'typeahead*" user!=NULL user!=admin search_id!="\'subsearch*"
|bucket _time span=day
|stats count by user
| table user count
|head 10
| sort -count
| appendpipe [|rest /servicesNS/-/-/authentication/users splunk_server=local
   |search NOT title=admin|fields title realname type email
   | rename title AS user
   | eval DROPME="true"]
| evenstats values(realname) AS realname BY user
| where isnotnull(DROPME)

[sheltomt]

So looking over your solution, I see where I was going wrong on the subsearch.

However, your solution appears to only return values of the subsearch. There is no count from the outer search

I'm trying to get an output of strictly realname and then a count field for how many searches they've done

[woodcock]

I only see subsearch in your subject line. Which is the search and which is the subsearch? Show the combined search and maybe that will help.

[sheltomt]

Ahh, got yours to work much better with last line being "where isnull(DROPME)"

[woodcock]

So I was dropping the wrong set.