All Apps and Add-ons
Highlighted

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

Path Finder

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
Tags (1)
Highlighted

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

Splunk Employee
Splunk Employee

Try:

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

This should produce:

 contractId          memberId

   contract1_100     member1_100
                     member2_100
                     member3_100
0 Karma
Highlighted

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

Influencer

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

0 Karma
Highlighted

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

Splunk Employee
Splunk Employee

oops reading comprehension fail 😄

0 Karma
Highlighted

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

Influencer

append this to the end of your search:

... | mvexpand memberId
0 Karma
Highlighted

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

Path Finder

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

contractId memberId

contract1100 member1100,member2100, member3100
contract1100 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

0 Karma
Highlighted

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

Path Finder

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 
0 Karma
Highlighted

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

Influencer

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

0 Karma
Highlighted

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

Path Finder

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

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

|table contractId, memberId

And as you suggested to append mvexpand memberId

0 Karma