topic Re: Matching dissimilar field titles with a Subsearch in Splunk Search

Matching dissimilar field titles with a Subsearch

sheltomt — Thu, 29 Jun 2017 18:40:40 GMT

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

Re: Matching dissimilar field titles with a Subsearch

woodcock — Thu, 29 Jun 2017 18:54:33 GMT

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)

Re: Matching dissimilar field titles with a Subsearch

sheltomt — Thu, 29 Jun 2017 19:06:42 GMT

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

Re: Matching dissimilar field titles with a Subsearch

woodcock — Thu, 29 Jun 2017 19:16:53 GMT

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.

Re: Matching dissimilar field titles with a Subsearch

somesoni2 — Thu, 29 Jun 2017 19:18:29 GMT

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]

Re: Matching dissimilar field titles with a Subsearch

sheltomt — Thu, 29 Jun 2017 19:27:28 GMT

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

Re: Matching dissimilar field titles with a Subsearch

sheltomt — Thu, 29 Jun 2017 19:29:04 GMT

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.

Re: Matching dissimilar field titles with a Subsearch

woodcock — Thu, 29 Jun 2017 19:31:06 GMT

So I was dropping the wrong set.