Extract one row of data from a table

[ewanbrown]

In my source data I have an array of several values, only one of which is of interest. My sample search string is:

index=sourcedata (bean.data.matches{}.category="EXAMPLE") | spath "bean.data.matches{}.id" | search bean.data.matches{}.category="EXAMPLE" | table bean.data.matches{}.category bean.data.matches{}.Id

For simplicity I have limited the query to return one record (normally I'd want them all) I get

bean.data.matches{}.category bean.data.matches{}.Id

TEST 1234
TEST2 4567
EXAMPLE 9999
EXAMPLE2 2345

I want to use this data (as there is a category of type EXAMPLE present) but I only want to use the EXAMPLE 9999.

How can I end up with just this part!

Thanks!

[martin_mueller · Accepted solution]

I'm assuming you basically have data looking like what's generated by this dummy query:

| stats count | eval key = "foo bar baz" | makemv key | eval value = "10 20 30" | makemv value

#   key  value
1   foo     10
    bar     20
    baz     30

And you'd like to have one event with key=bar value=20 out of that. Here's what I'd append to my dummy query:

... | eval keyvalue = mvzip(key, value) | mvexpand keyvalue | rex field=keyvalue "^(?<key>[^,]+),(?<value>.+)$" | search key=bar

First I build a field with mv values such as "bar,20" and split the single event into three based on that field. Then I extract the two fields from my temporary keyvalue field and filter based on the now-singlevalue key.

The same should work for your data, however be careful with field names containing non-standard characters such as dots or curly braces in eval commands. Either rename those fields first, or enclose them in single quotes on the RHS of all eval statements.

[ewanbrown]

Thanks! That is perfect