SQL commands like "IN" or "Exists" is available in Splunk ???

[bansi]

I have researched extensively splunk Documentation and Google to check if Splunk provides features like SQL commands "IN" or "Exists" . The link (Splunk For SQL Users) doesnt talk about IN or Exists so wondering if this feature is available in Splunk.

Here is the search query which is suppose to return multiple rows for contractId, memberId combination as one contractId will have multiple memberIds. But it displays only the first match of memberId and ignores the rest, after adding "max_match=999" it displays all the memberIds but in single row whereas expected result has to be in multiple rows i.e. contractId, memberId combination per row

Here is the XML

<DataNode node-type="Roster">
            <TransactionAttributes>
                <entry key="CONTRACT_ID">contract1_100</entry>
                <entry key="MEMBER_ID">member1_100</entry>
            </TransactionAttributes>            
        </DataNode>
        <DataNode node-type="Roster">
            <TransactionAttributes>
                <entry key="CONTRACT_ID">contract1_100</entry>
                <entry key="MEMBER_ID">member2_100</entry>
            </TransactionAttributes>
            </DataNode>
        <DataNode node-type="Roster">
            <TransactionAttributes>
                <entry key="CONTRACT_ID">contract1_100</entry>
                <entry key="MEMBER_ID">member3_100</entry>
            </TransactionAttributes>            
        </DataNode>

Here is the splunk query

source="Test_Log.txt" 
| xmlkv entry 
| rex "(?i)\<TransactionAttributes\>\<entry key=\"CONTRACT_ID\"\>(?<contractId>[^\<]+)" 
| rex "(?i)\<TransactionAttributes\>\<entry key=\"CONTRACT_ID\"\>\w+\<\/entry\>\<entry key=\"MEMBER_ID\">(?<memberId>[^<]+)" 
|where contractId="123" 
|table contractId, memberId

The query produces following results in single row

contractId            memberId

 contract1_100       member1_100
                     member2_100
                     member3_100

whereas Expected Results is

contractId            memberId

 contract1_100       member1_100
 contract1_100       member2_100
 contract1_100       member3_100

[bansi]

As i didnt get answer to my comment i am using this section to repost the comment

In the above posting. i forgot to mention the complete output was

contractId        memberId
==========        =========
contract1_100     member1_100,
                  member2_100, 
                  member3_100 
contract1_100    <NULL> 

Now using mvexpand as suggested by ziegfried doesnt help much as the last rows disappear moreover it still displays memberIds with contractId in single row whereas i need to display combination of contractId, memberId in single row i.e.

contractId        memberId
==========        =========
contract1_100     member1_100,
contract1_100     member2_100, 
contract1_100     member3_100 

[bansi]

Please see my first posting in which i have pasted XML Log file and exact search i.e.

source="Test_Log.txt" | xmlkv entry | rex "(?i)<TransactionAttributes><entry key=\"CONTRACT_ID\">(?[^<]+)" | rex "(?i)<TransactionAttributes><entry key=\"CONTRACT_ID\">\w+<\/entry><entry key=\"MEMBER_ID\">(?[^<]+)" |where contractId="123" | mvexpand memberId

|table contractId, memberId

And as you suggested to append mvexpand memberId

[ziegfried]

What's the exact search where you've been using mvexpand?

[ziegfried]

append this to the end of your search:

... | mvexpand memberId

[bansi]

Hmmm it didnt help much.In above posting. i forgot to mention the complete output was

contractId memberId

contract1_100 member1_100,member2_100, member3_100
contract1_100 meaning no members which is fine
contract1_100

Now with mvexpand it produces output as expected but the last two rows disappear with NULL MemberIds

[araitz]

Try:

...
|where contractId="123" 
|stats values(memberId) by contractId

This should produce:

 contractId          memberId

   contract1_100     member1_100
                     member2_100
                     member3_100

[araitz]

oops reading comprehension fail 😄

[ziegfried]

I think that's the result he didn't want to produce 😉