Splunk Dev

How do I make a inputlookup recursive?

cmeinco
Path Finder

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.

Tags (2)
0 Karma
1 Solution

cmeinco
Path Finder

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

View solution in original post

0 Karma

cmeinco
Path Finder

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

0 Karma

lukejadamec
Super Champion

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.

0 Karma

cmeinco
Path Finder

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

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

0 Karma

cmeinco
Path Finder

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?

0 Karma

lukejadamec
Super Champion

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
0 Karma

lukejadamec
Super Champion

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.

0 Karma

cmeinco
Path Finder

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

0 Karma

jspears
Communicator

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

0 Karma

cmeinco
Path Finder

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.

0 Karma

lukejadamec
Super Champion

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

Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...