splunk external lookup returning multiple rows as a single row

[binoy3012]

Hello,

I'm very new to splunk. I have a task to query an external bug system and display the results in splunk using a pattern search.  I used splunk external lookup script written in python by following the external_lookup.py script as a guide. I'm able to get the results displayed in splunk via this SPL:

| stats count | eval definition="sourcetype=SipAdapterGrp1" | lookup defectlookup definition

This gives me following:

The lookup script queries the bug system for any bugs that has the search string (definition) in it.  The problems is they show up in one single row. I would have expected it to be in 3 separate rows as there are 3 bugs.

I'm thinking this something very simple to correct in the output of the script or somehow modify the SPL cmd? Any advise please?

[bowesmana · Accepted solution]

You have only one 'input' row, i.e. from stats count, so the lookup command is saying find me all the matching entries and add them to this row. That's how lookup works.

You can do this to create new rows in Splunk

| stats count
| eval definition="sourcetype=SipAdapterGrp1" 
| lookup defectlookup definition OUTPUT defect
| mvexpand defect
| lookup defectlookup definition defect

which will first get all defects, then expand the multiple results and then perform the same lookup with defect as an additional constraint

Alternatively, as this is an external lookup, you could do this instead 

| stats count
| eval definition="sourcetype=SipAdapterGrp1" 
| lookup defectlookup definition 
| eval defect=mvzip(mvzip(defect, headline, "#"), state, "#")
| mvexpand defect
| rex field=defect "(?<defect>[^#]*)#(?<headline>[^#]*)#*?(<state>.*)"

which does the single lookup, then

• zips up each of the multivalue fields returned, which appear to be defect, headine and state
  • using a # character to separate the fields
• expands that combined field to create separate events
• separates out the individual fields again

[binoy3012]

Thank you for explaining about the input row. So, since I'm not searching an index in splunk but rather generating a report by doing an external lookup, it appears there is no other way than having one input and then expanding it into new rows, correct?

Also, appreciate the two solutions, but both return duplicate entries for headline and state . First one:

Second one:

[bowesmana]

@binoy3012  ok, so not totally sure what's wrong with the first, but that is fixable using the mvdedup command to remove multi value duplicates, e.g.

| eval headline=mvdedup(headline)

but depending on your data that may not be the right solution.

For 2, my rex regex had a type

| rex field=defect "(?<defect>[^#]*)#(?<headline>[^#]*)#?(<state>.*)"

It had an extra # after the last #, which was wrong, that should fix it

[binoy3012]

@bowesmana  the mvdedeup function worked with your first query.  Thank you!