db connect with lookup only returning first value it finds in lookup when feed by dbquery

[rdownie]

If I use:

|dbquery mydb "select userid from mydb.people where username = 'jsmith' | rename userid as UID | lookup mydb.systems UID output SYSTEMNAME | table UID,SYSTEMNAME

This only returns the first value it finds from the lookup output when it should be returning many systems for that user. Is this expected, normally the lookup command returns all that match, how do I get it to return them all in this case?

Thanks,

[stefano_guidoba]

Hi,

in Manager > Lookups > Lookups Definition find your lookup, open it, then check the Advanced Options flag and change the value in there (It should be 1 by default).

Regards,
Stefano

[rdownie]

when you run a database lookup, if there are multiple rows that it matches, does it only return the first row and if so, is there a way to get it to return all rows?
lookup user output system
if a user has multiple systems, I would like it to return them all. Is there a way to do this? I tried mvexpand but it is not a multivalued fields that it is returning, it is multiple matches on the lookup..
Any help would be greatly appreciated...

[rdownie]

Unfortunately I don't have the luxary of creating custom sourcetypes. The lookup only appears to give only one result when the field is passed in from a dbquery. I have other queries where the field is passed via a sourcetype output and the lookup outputs all expected rows.

[stefano_guidoba]

To verify that, put the data extracted from your dbquery command in an index with a custom sourcetype. Then write something like
index=mydb sourcetype=users | lookup mydb.systems UID as userid OUTPUT SYSTEMNAME
and check if you still have only one result.

[rdownie]

I changed the Maximum matches from the default to 100 and still only returning 1 value? This appears to only happen when taking its input from the dbquery command.