combine results from index search with fields machine inputlookup

[MH1]

Newbie here. Trying get the results from the index to match result int he inputlookup to only return result from the index.

Been playing around with joins, append, appendcols. Cannot seem to get the results matching the index search. Need some guidance.

| inputlookup Assets
| appendcols
[ search nt_host distinguishedName dns ]
[ search index=win EventCode=4725 src_user="*"
| eval user=replace(user,"[^[:word:]]","") ]
| eval user=nt_host
| stats count by src_user, EventCode, signature, user, nt_host, distinguishedName

[bowesmana]

I feel your pain - it's tough getting started with all the possibilities - anyway, the options you are trying are unlikely to be the right solution.  appendcols is rarely a good solution as it requires the two data sets to line up exactly and join is never a good place to go - it's just not the Splunk way to do things.

If I understand your use case, you are trying to find data from your index 'win' which only relate to the nt_host devices in your lookup file. It's not clear what the matching element in both index and lookup is - you are setting user to nt_host but the previous line you replace user in some way.

Anyway, there are generally two ways to achieve this, depending on your data volume.

1. Using a subsearch

search index=win EventCode=4725 src_user="*" [ 
  | inputlookup Assets
  | rename nt_host as user 
  | fields user ]
``` The subsearch above will restrict the search to user=nt_host ```
| stats count by src_user, EventCode, signature, user
``` And this lookup will then fetch the DN - it can be done after the 
    stats as the data does not change for the group by user ```
| lookup Assets nt_host as user OUTPUT nt_host distinguishedName

2. Using a lookup

search index=win EventCode=4725 src_user="*" 
| stats count by src_user, EventCode, signature, user
``` And this lookup will then fetch the DN - it can be done after the 
    stats as the data does not change for the group by user ```
| lookup Assets nt_host as user OUTPUT nt_host distinguishedName
``` Now remove all the ones that were not in the Assets lookup ```
| where isnotnull(nt_host)

If the data volume in the lookup is large then subsearch can be inefficient and if the lookup represents most of the data set in index, then it's probably more efficient to use the second approach.

However, I am not sure about your 'user' field and whether that is intended to be nt_host

[MH1]

Thanks we are getting closer. The user and nt_host is the link to the two searches index and inputlookup.

The subsearch in the win index the user field has a special character at the end. which I have used this eval command to strip the special character at the end

| eval user=replace(user,"[^[:word:]]","")

search index=win EventCode=4725 src_user="*" [
| inputlookup Assets
| rename nt_host as user
| fields user ]

``` The subsearch above will restrict the search to user=nt_host ```

| stats count by src_user, EventCode, signature, user

``` And this lookup will then fetch the DN - it can be done after the
stats as the data does not change for the group by user ```

| lookup Assets nt_host as user OUTPUT nt_host distinguishedName

search index=win EventCode=4725 src_user="*"
| stats count by src_user, EventCode, signature, user

``` And this lookup will then fetch the DN - it can be done after the
stats as the data does not change for the group by user ```

| lookup Assets nt_host as user OUTPUT nt_host distinguishedName

``` Now remove all the ones that were not in the Assets lookup ```

| where isnotnull(nt_host)

Modified the lookup to include the removal of special character. In the events, shows the correct user impacted. Getting close.

search index=win EventCode=4725 src_user="*"
| eval user=replace(user,"[^[:word:]]","")
| stats count by src_user, EventCode, signature, user
| lookup Assets nt_host as user OUTPUT nt_host distinguishedName
| where isnotnull(nt_host)
| fields src_user, EventCode, signature, user, nt_host, distinguishedName



Index Inputlookup End Goal

src_user EventCode user nt_host distinguishedName src_user EventCode user nt_host distinguishedName
service 4725 device1 device1 CN=device1,OUComputers,OU,Agency service 4725 device1 device1 CN=device1,OUComputers,OU,Agency
service 4725 device2 device2 CN=device2,OUComputers,OU,Agency service 4725 device2 device2 CN=device2,OUComputers,OU,Agency
service 4725 device3 device3 CN=device3,OUComputers,OU,Agency service 4725 device3 device3 CN=device3,OUComputers,OU,Agency
service 4725 device4 device4 CN=device4,OUComputers,OU,Agency service 4725 device4 device4 CN=device4,OUComputers,OU,Agency
service 4725 device5 device5 CN=device5,OUComputers,OU,Agency service 4725 device5 device5 CN=device5,OUComputers,OU,Agency

[bowesmana · Accepted solution]

I'm not sure I understood the steps shown in your reply and what was not there yet, but you have taken the right path in that if your user in the index has extra data at the end, then using a subsearch will not work unless you, for example, add a * character in the user in the lookup, which may or may not be useful in your case.

So, the 

search
| eval...
| stats...
| lookup
| where

approach rather than subsearch is right - where are you not getting what you want?

[MH1]

Here is a diagram of what I am trying to accomplish. Not able to get the last 2 columns of end goal to match

Index				Inputlookup		End Goal
src_user	EventCode	user		nt_host	distinguishedName		src_user	EventCode	user	nt_host	distinguishedName
service	4725	device1		device1	CN=device1,OUComputers,OU,Agency		service	4725	device1	device1	CN=device1,OUComputers,OU,Agency
service	4725	device2		device2	CN=device2,OUComputers,OU,Agency		service	4725	device2	device2	CN=device2,OUComputers,OU,Agency
service	4725	device3		device3	CN=device3,OUComputers,OU,Agency		service	4725	device3	device3	CN=device3,OUComputers,OU,Agency
service	4725	device4		device4	CN=device4,OUComputers,OU,Agency		service	4725	device4	device4	CN=device4,OUComputers,OU,Agency
service	4725	device5		device5	CN=device5,OUComputers,OU,Agency		service	4725	device5	device5	CN=device5,OUComputers,OU,Agency