How to add new field to a search from results of another search

[garciajbg]

PLEASE BE PATIENT I AM NEW TO THIS

All, I am trying to use the results of a search (search 1) and create a new field in (search 2) to use as a “corresponding” field to identify user credentials. Search 1 field1 needs to correspond with search 2 fiieldX. fieldX in search 2 does not exist. The fields from both searches have different field names however fields 2 and 3 on search 1 has the same results/parameters as field 8 and 11 on search 2.

I tried to use search 1 as a lookup and invoke it however I can’t seem to make that work due to field names not being the same on both searches. Any assistance and guidance for similar searches will be greatly appreciated! Thank you in advance.

Search # 1
index=1 sourcetype=sourcetype1 | fields field1 field2 field3 | table field1 field2 field3 | dedup field2

Search # 2
index=2 sourcetype=souretype2 eventSource=eventSource eventName=Create OR eventName=Delete | fields field4 field5 field6 field7 field8 field9 field10 field11 field11 | dedup field4 | table field5 field6 field7 field8 field9 field10 field11 fieldX | rename field5 field5 as Event field6 as Time field7 Location field8 as Item_Name field9 as IP field10 as User field11 as Role fieldX as “???”

[woodcock]

Like this:

index="2" AND sourcetype="souretype2" AND eventSource="eventSource" AND (eventName="Create" OR eventName="Delete") AND field4="*"
| fields field4 field5 field6 field7 field8 field9 field10 field11 field11
| dedup field4
| table field5 field6 field7 field8 field9 field10 field11 fieldX
| rename field5 AS Event field6 AS Time field7 AS Location field8 AS Item_Name field9 AS IP field10 AS User field11 AS Role fieldX AS [search index=1 sourcetype=sourcetype1 field2="*" | head 1 | return $field2 ]

[woodcock]

Like this:

index=2 AND sourcetype=souretype2 AND eventSource=eventSource AND (eventName=Create OR eventName=Delete) AND [ search index=1 sourcetype=sourcetype1 | stats count BY field2 field3 | table field2 field3 | rename field2 AS field8, field3 AS field11 | format ]

[garciajbg]

Woodcock, thank you so much for your reply. I will try this in the a.m. EST and will post my results. I will make sure to add any changes I may need to make to solve my paticular problem in Hope's it may help someone else having my same issue. Again thanks.

[woodcock]

So what happened; did it do what you need it to do?

[garciajbg]

woodcock, sorry for the late reply as I had to work on this later than initially anticipated due to putting out several fires. The search on index1 seems to not want to work. everything looks fine until I try "search index=1 sourcetype=sourcetype1". It does not want to table the fields for some reason. Can this be because the field names were manually extracted from the search? I had to extract and name new fields from index 1 and gave the correct permissions so the fields can be used by everyone.

[arjunpkishore5]

Not sure if I understand your question correctly. I think this is what you're looking for. Here's my assumption

You need to join search 2 to search 1 on the fields (field8 and field11) on search 2 and fields(field2 and field3) and then fetch field1 from search 1 which becomes fieldX

If this is the case, the following search will work for you.

index=2 sourcetype=souretype2 eventSource=eventSource eventName=Create OR eventName=Delete 
| table field4 field5 field6 field7 field8 field9 field10 field11 
| dedup field4 
| fields - field4 
| rename field5 as Event field6 as Time field7 Location field8 as Item_Name field9 as IP field10 as User field11 as Role 
| strcat Item_Name " - " Role join_field 
| join join_field
    [ search index=1 sourcetype=sourcetype1 
    | table field1 field2 field3 
    | dedup field2 
    | strcat field2 "-" field3 join_field 
    | rename field1 as fieldX]

Here's documentation for join - https://docs.splunk.com/Documentation/Splunk/7.3.2/SearchReference/Join
And for strcat - https://docs.splunk.com/Documentation/SplunkCloud/7.2.7/SearchReference/Strcat

If this is what you're looking for, please upvote and mark as answer.

Cheers

[garciajbg]

arjunpkishore5, I was able to use this query and made minor tweaks to get the results I was looking for. Woodcock, mentioned that using join to filter can have unwanted results. If the fields from search 1 were extracted as "new" fields, can this cause errors? Also, am I correct by saying I am not able to create and use a "lookup" if I had to create/extract new fields from search one? I read, that if the field names do not match then a lookup cannot be used.

[arjunpkishore5]

@garciajbg The problem with join and other set operations in general is that it limits the subset to 50000 records( the default limit in limits.conf unless overridden to a different value) and also has performance penalties. I'm not sure if woodcock meant any other issues. However, this would be applicable only if your dataset is significantly large.

You can use lookup if the fields name are not common, you'll just have to alias them like | lookup <field in lookup> as <field in source data>

[woodcock]

This can be done without join; using it will cause problems.

[garciajbg]

woodcock, you mentioned "join" to filter can create unwanted results and /or issues. Can you explain what you mean by this or provide an example? Thank you...

[woodcock]

It uses subsearches which are subject to limits (defaulting to 50Kish) so when testing it works but when scaling it out, it fails silently, appearing to still work. It is terrible.

[arjunpkishore5]

Well. If I'm using join to filter, yes. it will cause problems. But, as explained in my understanding of the question, the poster wants to fetch field 1 from the search1 , not filter search2 based on search1