Fecth the field based on other field condition

[myusufe71]

Hi,

I need help to fetch field based on other field condition.

I have lookup table  as below,

NAME STATE

abc-a-0 host1 master

abc-a-1 host2 local

abc-a-2 host3 local

abc-b-0 host4 local

abc-b-1 host4 local

abc-b-2 host4 local

I want to retrieve abc-a-* NAME based on STATE which it is as master. The master STATE is dynamic, it will be abc-b-* group also sometimes.

Example:

NAME HOST STATE

abc-a-0 host1 local

abc-a-1 host2 local

abc-a-2 host3 local

abc-b-0 host4 local

abc-b-1 host5 master

abc-b-2 host6 local

The problem is,

1. Retrieve the current master STATE if it is abc-a-* or abc-b* NAME
2. Then fetch 3 NAMEs based on condition if it is abc-a-* or abc-b-*

[ITWhisperer]

Assuming your names follow the apparent pattern you have shown, you could do something like this

| eval name_prefix=mvjoin(mvindex(split(NAME,"-"),0,1),"-")
| eventstats values(eval(if(STATE="master",STATE,null()))) as master by name_prefix
| where master="master"