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!

Introducing Ingest Actions: Filter, Mask, Route, Repeat

WATCH NOW Ingest Actions (IA) is the best new way to easily filter, mask and route your data in Splunk® ...

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...