Getting Data In

Is it possible to use inputlookup inside mvfind?

jeremiahc4
Builder

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]")
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

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

jeremiahc4
Builder

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma

jeremiahc4
Builder

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.

0 Karma

woodcock
Esteemed Legend

Is field Transactions a multi-valued field?

0 Karma

jeremiahc4
Builder

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

How many rows are there in lookup ?

0 Karma

jeremiahc4
Builder

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...