How do I make a inputlookup recursive?

[cmeinco]

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.

[cmeinco · Accepted solution]

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?)

[lukejadamec]

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.

[cmeinco]

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

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

[cmeinco]

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?

[lukejadamec]

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

[lukejadamec]

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.

[cmeinco]

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).

[jspears]

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. 🙂

[cmeinco]

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.

[lukejadamec]

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