how to perform a search that has the result of another search as input

[antonio147]

Hi,
i can't do a search on Splunk where the values ​​are the result of another search.
I search:
index = summary | search ..... | table LINK, OLD_LINK, DATA, ID
the result is:
LINK   | OLD_LINK     | DATE                | ID
100           10                   01/02/21         1
101           11                   02/01/21         2
.........
in the same index now I want to find all those links that are in the OLD LINK field of the previous search and extract the DATA and ID for the link (OLD_LINK)
that is:
LINK  |    OLD_LINK     | DATE                |   ID
10                   -                  10/10/20           99
11                   -                  15/08/20          77

and at the end have a table like:
LINK          | OLD_LINK           | DATE            | ID              | OLD_DATA             | OLD_ID
100                   10                      01/02/21      1                  10/10/20                     99
101                   11                      02/01/21      2                   15/08/20                    77

I tried the JOIN but it doesn't work.
Can you help me?
I state that I am not an ADMIN and I do not have the permissions to create a lookup table, I have to run it in a single query (I think)
Tks
Bye
Antonio

[antonio147]

Hi,
the solutions are equally valid.
I mixed the two using the map and eval ifnotnull to get what I wanted.
My indications have not been very precise, so I believe that both are still satisfactory.
Thanks everyone for the help and the solution.

[antonio147]

there is a way in which, after the first condition that I find all the LINKs that have populated the OLD_LINK and the DATA, I feed a new search the list of all the OLD_LINKs to extrapolate the ID and DATA.
In short, use a variable to say replace this variable with every value of the OLD_LINK field (as if it were a FOR loop)

[ITWhisperer]

You might be able to do this by copying the data depending on whether OLD_LINK has a value

| makeresults 
| eval _raw="LINK,OLD_LINK,DATE,ID
100,10,01/02/21,1
101,11,02/01/21,2
10,,10/10/20,99
11,,15/08/20,77"
| multikv forceheader=1
| fields - _* linecount



| eval OLD_DATE=if(isnotnull(OLD_LINK),null,DATE)
| eval OLD_ID=if(isnotnull(OLD_LINK),null,ID)
| eval DATE=if(isnotnull(OLD_LINK),DATE,null)
| eval ID=if(isnotnull(OLD_LINK),ID,null)
| eval OLD_LINK=if(isnotnull(OLD_LINK),OLD_LINK,LINK)
| eval LINK=if(OLD_LINK==LINK,null,LINK)
| selfjoin OLD_LINK
| fields LINK OLD_LINK DATE ID OLD_DATE OLD_ID

The potential problem here is that you may have some links which haven't been replaced and it is unclear (since they aren't in your examples) what you would want to do in this situation.

[antonio147]

I have links that have a field with the old link.
So I first look for all links that have an OLD_LINK then I have to redo the search on the same LINK field which has a DATA and a populated ID.

Let me give you an example:

I have LINK: 1,2,3,4,5 ........., 100,101, .......
now links 100 and 101 have populated the OLD LINK field which are: 4,5 for example.
Now I have to find the IDs and the DATE of 4,5 to have a final table like this:

LINK OLD_LINK DATA DATA_OLD ID ID_OLD
100 4 10/20 01/19 X A
101 5 12/20 05/2018 Y B

in practice I have to do the search twice on the same LINK field and the criterion is all those links that have OLD_LINK find the starting date and the ID both old and new

[ITWhisperer]

Essentially, this is what my solution does (based on the limited example you have provided) - taking the piece after the blank lines (the piece before the blank lines merely sets up a run anywhere example set of data) - the first two lines effectively copies the date and id if the old link is null, which it would be if it was an old (non-replacement) record. The next two lines clear the date and id from the old record. The next two lines move the link to old link (clearing link in the process). The self join then joins the events by the old link id. Final line just selects the fields you wanted.

Have you tried this? If it doesn't work for you, please explain why not.

[antonio147]

with your solution the LINK, DATE, ID fields are not populated, only the old ones but no "new" data.

only the OLDs are populated

I wrote you the specifications better, I hope it is clearer.
In any case, thanks for the help.
Bye
Antonio

[antonio147]

Hi IT Whisperer,
I tried your suggestion and tried to understand better, even with your explanations.
If I understand correctly, check if OLD_LINK is populated and move the ID and DATE to the OLD variable.
My goal is: of this index = index1 first check that the LINK respect some conditions such as: it must have the ID and DATE field not null.
I do a dedup for LINK and get a list of LINK, OLD_LINK, ID, DATE plus other fields .....
From this list obtained I have to check, always on the same index1, that the values ​​that are in the OLD_LINK field are present in the LINK field and extract the ID and DATE renaming them in OLD.

In practice there are links that have changed value and I have to find when they changed value and before what value did they have?

An example:
LINK = 1 was created in 2019 with an ID = 999
now this LINK has been changed to a new LINK = 100 on 2021 with ID = 888

so I will have: LINK = 100, OLD_LINK = 1, DATA = 2021, ID = 888

every day an event is written even if nothing has changed.
so today I will have in the LINK field both the value 100 and the value 1, with different dates and IDs.

My goal is to extract both the new LINK with the DATE and ID, and the old LINK with its DATA and its ID.

That is:
LINK = 100, OLD_LINK = 1, DATE = 2021, OLD_DATE = 2019, ID = 888, OLD_ID = 999

This is what I can't do 🙂
Tks

[ITWhisperer]

This run-anywhere example does exactly what you are asking for. Try copying to a new search and running it a line at a time to see what each line does.

| makeresults 
| eval _raw="LINK,OLD_LINK,DATE,ID,OTHER
1,,2019,999,abc
100,1,2021,888,def
1,,2019,999,abc
100,1,2021,888,def"
| multikv forceheader=1
| fields - _* linecount


| dedup LINK
| eval OLD_DATE=if(isnotnull(OLD_LINK),null,DATE)
| eval OLD_ID=if(isnotnull(OLD_LINK),null,ID)
| eval DATE=if(isnotnull(OLD_LINK),DATE,null)
| eval ID=if(isnotnull(OLD_LINK),ID,null)
| eval OLD_LINK=if(isnotnull(OLD_LINK),OLD_LINK,LINK)
| eval LINK=if(OLD_LINK==LINK,null,LINK)
| selfjoin OLD_LINK
| fields LINK OLD_LINK DATE ID OLD_DATE OLD_ID OTHER

[antonio147]

I have tried both yours with the raw file and it works perfectly.
when I run it with my data it only pulls out 35 lines but without the OLD_ID, no value on this field.
the extrapolated events must be 940 instead it shows only 35. 😞

I don't know wy....

[ITWhisperer]

Can you share some anonymised real data for the events you are expecting to get matched and aren't working so we can try and figure out why it isn't working for them?

[ericjorgensenjr]

You should be able to use the map command to accomplish this. Depending on what your data looks like it might make more sense to replace the 'table' commands with 'stats values(...)'. But in general you're looking at something like:

index=summary earliest=-30d latest=now | search ..... | table LINK, OLD_LINK, DATA, ID | map [ search index=summary LINK=$OLD_LINK$ earliest=-60d latest=-30d | rename DATE as OLD_DATE, ID AS OLD_ID, LINK as OLD_LINK | eval DATE="$DATE$", ID="$ID$", LINK="$LINK$" ] | table LINK OLD_LINK DATE ID OLD_DATA OLD_ID

[antonio147]

Hi ericjorgensenjr

I tried your solution with MAP unfortunately it didn't give me any event.
Returned this message:

The following messages were returned by the search subsystem:

• info : No matching fields exist.
• warn : The search result count (902) exceeds maximum (10), using max. To override it, set maxsearches appropriately.

Thanks for your help, I hope to find a solution 🙂

[ericjorgensenjr]

2 things to look at:

- Set maxmatches=9999 in your map command | map maxmatches=9999 [ ... ]

- In the search I proposed I made some assumptions about earliest and latest in the searches, check these values to make sure they line up with where you expect the data to occur

[antonio147]

maxmatches = 9999 does not work.
I found maxsearches but this also gives me an error
Error in 'map' command: Unable to find saved search 'maxmatches = 9999'.
Error in 'map' command: Unable to find saved search 'maxsearches = 9999'.

[antonio147]

most likely there are more than 10,000 events, perhaps this could be the problem.
In your solution I did not understand why you use eval LINK = "$ LINK $" what is it for?
Forgive my ignorance 🙂

I try to set maxmatches = 9999

In the search I proposed I made some assumptions about earliest and latest in the searches, check these values to make sure they line up with where you expect the data to occur  -> (I didn't quite understand what you mean)

I hope I have explained my research well .......
I have to find both the new and the old LINKs and of these old ones find DATA and ID to have at the end

LINK, OLD_LINK, DATA, OLD_DATA, ID, OLD_ID, ... and other fields that are only in the first search.

Tks
Bye
Antonio

[ericjorgensenjr · Accepted solution]

Here's another wag at it, make sure you run the search over a long enough time period to pick up all the relevant events.

The Link="$LINK" is a way to pass the original link value into the subsearch. For more information on the map command see: https://docs.splunk.com/Documentation/SplunkCloud/8.1.2101/SearchReference/Map

index=summary  <you need to insert any other necessary search terms here>  | table LINK, OLD_LINK, DATA, ID | dedup LINK | map [ search index=summary <you need to insert any other necessary search terms here> LINK="$OLD_LINK$" | rename DATE as OLD_DATE, ID AS OLD_ID, LINK as OLD_LINK | eval DATE="$DATE$", ID="$ID$", LINK="$LINK$" ] maxsearches=9999 | table LINK OLD_LINK DATE ID OLD_DATE OLD_ID

Hope this helps!

[antonio147]

Hi,
i'm trying your solution, but it doesn't return anything like table just events, which are the same number it returns to me without the map.
I think we are there as a search, the problem that I don't display the OLD_LINK and other OLd values that I need.
I'm trying to modify your instructions to see step by step what they do.
Thanks for the info where LINK - "$ OLD_LINK $" searches me on the LINK field with all the OLD_LINK values found in the first one.
If I understand correctly the steps are:
1) I do my research obtaining only the values and fields that interest me
2) on this list obtained, I execute the MAP where I say that LINK must be equal to every value you find on OLD_LINK
3) return me the values of the first search by adding the fields found in the second search.
CORRECT??
TKS