topic Re: Is it possible to use inputlookup inside mvfind? in Getting Data In

Is it possible to use inputlookup inside mvfind?

jeremiahc4 — Thu, 17 Sep 2015 14:36:08 GMT

I have a multivalue field which I am trying to search for a list which is coming from an inputlookup (in lieu of hard-coding into mvfind). Is this possible? I've not found any answered questions on this yet. What I want to do is something like below, but the syntax isn't valid. The inputlookup subsearch would need to be formatted to an OR'd list like Trans1|Trans3|Trans4|Trans9|...|TransN.

<search>| stats count min(_time) AS Start max(_time) as End values(Transaction) AS Transactions by correlationField | eval myTransaction=mvfind(Transactions, "[|inputlookup myTransactionLookups | search owner="me" | fields TransactionName]")

Re: Is it possible to use inputlookup inside mvfind?

woodcock — Thu, 17 Sep 2015 14:54:52 GMT

Is field Transactions a multi-valued field?

Re: Is it possible to use inputlookup inside mvfind?

jeremiahc4 — Thu, 17 Sep 2015 15:04:13 GMT

yes, I guess my mocked up names are not as reflective as they should have been. But yes, it's a multivalue field where generally only one of the values will match something in my inputlookup. I want to match that single value from Transactions to my inputlookup value and put it as a new field myTransaction to be summed & graphed, etc...

Re: Is it possible to use inputlookup inside mvfind?

somesoni2 — Thu, 17 Sep 2015 15:54:24 GMT

How many rows are there in lookup ?

Re: Is it possible to use inputlookup inside mvfind?

jeremiahc4 — Thu, 17 Sep 2015 16:03:30 GMT

The largest return from the inputlookup would be about 10 rows currently. Some may only be 1-2.

Re: Is it possible to use inputlookup inside mvfind?

somesoni2 — Thu, 17 Sep 2015 17:57:50 GMT

Assuming number of values in both myTransactionLookups and Transactions field is not very high (say about 2 digits max), and either myTransactionLookups has an extra field which can be returned OR you can add an extra field, say "ReturnedField", then try something like this

<search>| stats count min(_time) AS Start max(_time) as End values(Transaction) AS Transactions by correlationField | mvexpand Transactions | eval owner="me" | lookup myTransactionLookups owner TransactionName as Transactions OUTPUT ReturnedField | where isnotnull(ReturnedField) | stats values(Transactions) as Transactions by Start End correlationField

Re: Is it possible to use inputlookup inside mvfind?

jeremiahc4 — Fri, 18 Sep 2015 12:35:02 GMT

Assuming that should be "mvexpand"? I'm getting an error on "evexpand". The number of returns from myTransactionLookups could be up to 10 and the Transactions field has maybe 4 values max. I'm not sure about the last stats you threw on there, but I seem to be getting what I need without it I think; at least in my test area. I need to go distribute the change to my dashboards in Dev, then push to UAT to validate further.

Thanks! Fingers crossed!

It threw me at first when I pieced this into my search because the mvexpand looked at first take like it was aligning the transactions to the first value in the MV field, but on closer look it duplicates them N times where N is the number of values in the MV field (right?). The "where isnotnull(ReturnedField)" then dumps the duplicates that don't match.

Re: Is it possible to use inputlookup inside mvfind?

somesoni2 — Fri, 18 Sep 2015 13:48:03 GMT

1) Yes, it should be mvexpand,typo corrected.
2) After mvexpand, each transaction will appear as different row, so total rows*N where N is number of mv field count.
3) I assumed that there could be more that 1 Transactions that can appear in the lookup, so I added last stats. You could ignore it if you're sure that's not the case.

Re: Is it possible to use inputlookup inside mvfind?

jeremiahc4 — Tue, 08 Dec 2015 13:44:09 GMT

Late update on this. This did work, but should be noted with a caveat. Don't try this with very large data sets as it will overwhelm the search head. If you are sure your result set is small enough, then it's fine. I ultimately had to go to away from this method for this particular problem due to size of one of the search result sets, but it works for smaller searches.