topic Re: How do I make a inputlookup recursive? in Splunk Dev

How do I make a inputlookup recursive?

cmeinco — Mon, 30 Sep 2013 17:39:56 GMT

I have an lookup file: (parts.csv)

partid, parentpartid
part01,
part02,part01
part03,part01
part04,part02

I need to join this data with this data: (mysearch)

day, partid, partevent, count
9/30/2013,part02,creation,23
9/30/2013,part04,update,30

Selfjoin does not appear to work as described (what little description exists). Here is my first hack attempt:

(my search) | join type=left parentpartid overwrite=f [|inputlookup parts.csv|rename parentpartid as parentpartid_lvl1|rename partid as parentpartid] 
| join type=left parentpartid_lvl1 overwrite=f [|inputlookup parts.csv|rename parentpartid as parentpartid_lvl2|rename partid as parentpartid_lvl1] 
| join type=left parentpartid_lvl2 overwrite=f [|inputlookup parts.csv|rename parentpartid as parentpartid_lvl3|rename partid as parentpartid_lvl2] 
| join type=left parentpartid_lvl3 overwrite=f [|inputlookup parts.csv|rename parentpartid as parentpartid_lvl4|rename partid as parentpartid_lvl3]

I can then filter the results by using this statement:

| where parentpartid=tmp_partid OR parentpartid_lvl1=tmp_partid OR parentpartid_lvl2=tmp_partid OR parentpartid_lvl3=tmp_partid OR parentpartid_lvl4=tmp_partid

Note: I put my initial part id into my search by doing this as part of (mysearch): "| eval tmp_partid=part02"

I need to be able to do this up to around 40 times (maybe more), but I'm happy to put a cap on it. Any guidance or recommendation on creating a custom join or inputlookup.

Re: How do I make a inputlookup recursive?

lukejadamec — Mon, 30 Sep 2013 18:01:11 GMT

Not sure I understand. You want to add the parentpartid field to your output table?
day, partid, parentpartid, partevent, count

Re: How do I make a inputlookup recursive?

jspears — Mon, 30 Sep 2013 18:14:41 GMT

How do you feel about trying transaction instead? Something like...

search | inputlookup parts.csv | transaction partid parentpartid | search parentpartid=tmp_partid

I think this will get you all the lists that contain the parentpartid you search for. I don't have any way to test this at the moment. 🙂

Re: How do I make a inputlookup recursive?

lukejadamec — Mon, 30 Sep 2013 19:17:47 GMT

You can do this without transaction. This will pull the parentpartid that matches the partid and create the parentpartid field.

search | inputlookup parts.csv partid AS partid OUTPUT parentpartid AS parentpartid | stats count by day, parentpartid, partid, partevent

Re: How do I make a inputlookup recursive?

cmeinco — Mon, 30 Sep 2013 21:48:56 GMT

this isn't working; it's limited to a first level lookup; whereas I'm looking to grab a full tree from the root partid and find all parts under that root partid for inclusion in my output.

Re: How do I make a inputlookup recursive?

cmeinco — Mon, 30 Sep 2013 21:49:55 GMT

I think you mean lookup, not inputlookup. This does not appear to be working; as with the other comment; this is not recursively including all children of the tmp_partid (root search element).

Re: How do I make a inputlookup recursive?

lukejadamec — Mon, 30 Sep 2013 22:50:10 GMT

Lookup works if it is defined. If it is not defined then you can specify a file in the lookup folder with inputlookup.
Sorry for misunderstanding your question. This answer does not do it for you, but I'm still not really sure what you're trying to do.
Instead of posting code for your output, how about an example of what you want the output to look like based on the inputs you've already provided.

Re: How do I make a inputlookup recursive?

cmeinco — Mon, 28 Sep 2020 14:52:09 GMT

answers appears to be broken, can't add edit right now; Here is more about my search:

(my search)= sourcetype=part_events | eval tmp_partid="part02"

(output)= | stats sum(count) as hits by partid | sort - hits

I want all the events where partid is a child of part2; no matter how deep into the hierarchy. So if I passed in part01 as tmp_partid, I would expect to get everything.

Should I be looking at macros to do this recursion for me?

Re: How do I make a inputlookup recursive?

cmeinco — Mon, 30 Sep 2013 22:57:19 GMT

Example of output: (tmp_partid="part01")
part02,23
part04,30

If there were multiple part02 entries, these would be sum'd with stats.

Re: How do I make a inputlookup recursive?

lukejadamec — Mon, 30 Sep 2013 23:05:23 GMT

I'd have to test this, but you might be looking at it backwards.
Rename partid as parentid, call the lookup for parentids and output partids for that parent. In the lookup definitions, you can specify the maximum number of matches.
To make it less confusing, change the header names in the csv file.
Like I said, I need to test this.

Re: How do I make a inputlookup recursive?

cmeinco — Thu, 10 Oct 2013 01:22:39 GMT

I decided this wouldn't recurse more than 99 times, so I wrote a quick shell script to generate a macro (parent_part_lookup):

join partid [|inputlookup parts.csv | join type=left parentpartid overwrite=f [|inputlookup parts.csv|rename parentpartid as parentpartid_lvl1|rename partid as parentpartid] | join type=left parentpartid_lvl1 overwrite=f [|inputlookup parts.csv|rename parentpartid as parentpartid_lvl2|rename partid as parentpartid_lvl1] | join type=left parentpartid_lvl2 overwrite=f [|inputlookup parts.csv|rename parentpartid as parentpartid_lvl3|rename partid as parentpartid_lvl2] | join type=left parentpartid_lvl3 overwrite=f [|inputlookup parts.csv|rename parentpartid as parentpartid_lvl4|rename partid as parentpartid_lvl3] ... | join type=left parentpartid_lvl98 overwrite=f [|inputlookup parts.csv|rename parentpartid as parentpartid_lvl99|rename partid as parentpartid_lvl98] | join type=left parentpartid_lvl99 overwrite=f [|inputlookup parts.csv|rename parentpartid as parentpartid_lvl100|rename partid as parentpartid_lvl99] ] | where parentpartid=tmp_partid OR parentpartid_lvl1=tmp_partid OR parentpartid_lvl2=tmp_partid OR parentpartid_lvl3=tmp_partid OR ... OR parentpartid_lvl98=tmp_partid OR parentpartid_lvl99=tmp_partid

There has to be a better way; likely starting with some macro cleanup and parameterization. Note: I first had everything formatted and easy to read, but the macro had an error and I had to remove the line breaks (bug?)