All Apps and Add-ons

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

bansi
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)

bansi
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

bansi
Path Finder

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

0 Karma

ziegfried
Influencer

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

0 Karma

ziegfried
Influencer

append this to the end of your search:

... | mvexpand memberId
0 Karma

bansi
Path Finder

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

0 Karma

araitz
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

araitz
Splunk Employee
Splunk Employee

oops reading comprehension fail 😄

0 Karma

ziegfried
Influencer

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

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