Splunk Search

Problem With keeping relevant data in the same row while expanding multi value fields

sharif_ahmmad
Explorer

Hello,

i have been trying to expand multi value fields from different source-type. Problem is that when i do expand link between relevant data gets lost. I have described a similar scenario below. Any help would be greatly appreciated.

Say i have below table for example. where field value of desk_id & Counter_id is similar.

Also Counter_ID & Customer_name belongs to Source A where Desk_id & Purchased_item belong to Source B. Customer_ID field present in both source A & B.

Customer_Id           Counter_ID    Customer_Name              Desk_ID      Purchased_Item
    121                         1   Jesse                                1           Pen
                                 2      Jesse                               1          Pencil
                                 3      Jesse                                2         Fountain Pen
                                                                              3        Paper

I want my output table to look like this

       Expected Output  


Customer_Id             Counter_ID              Customer_Name     Desk_ID                       Purchased_Item
      121                      1                       Jesse           1                      Pen
      121                       1                         Jesse        1                       Pencil
      121                       2                      Jesse           2                     Fountain Pen
      121                       3                       Jesse          3                          Paper

but unfortunately my current output looks like this

                                 Current Output
 Customer_Id             Counter_ID             Customer_Name     Desk_ID                       Purchased_Item
      121                      1                       Jesse           1                      Pen
      121                       2                         Jesse        2                       Pencil
      121                       3                      Jesse           3                     Fountain Pen

My Code is below for your references

Source A OR Source B

| fields Customer_ID,Counter_ID,Desk_ID,Customer_Name,Purchased_Item


| stats list( Counter_ID) as  Counter_ID,  list(Customer_Name) as Customer Name,list(Desk_ID) as Desk_ID,stats list(Purchased_Item) as Purchased_Item by Customer_ID


| table Customer_ID,Counter_ID,Customer_Name,Desk_ID,Purchased_Item,


| eval total= mvzip(Counter_ID,Customer_Name) | eval total= mvzip(total,Desk_ID) |eval total= mvzip(total,Purchased_Item) | mvexpand total| makemv total delim="," | eval Counter_ID=mvindex(total,0)| eval Customer_Name=mvindex(total,1)| eval Desk_ID=mvindex(total,2)| eval Purchased_Item=mvindex(total,-1)

Any tips or help would be greatly appreciated.

0 Karma

to4kawa
Ultra Champion
Source A OR Source B
 | stats values(Customer_Name) as Customer_Name list(Counter_ID) as Counter_ID list(Desk_ID) as Desk_ID list(Purchased_Item) as Purchased_Item by Customer_ID
 | eval _counter = mvrange(0,mvcount(Purchased_Item))
 | stats list(*) as * by _counter
 | foreach * [ eval <<FIELD>> = case(mvcount(<<FIELD>>)=1,<<FIELD>>, true(), mvindex(<<FIELD>>,_counter)) ]
 | fields Customer_ID,Counter_ID,Desk_ID,Customer_Name,Purchased_Item

hi, @sharif_ahmmad
how about this?

niketn
Legend

@sharif_ahmmad your data is missing the correlation between Customer_Name and Purchased_ID for through the counter. Please add that details for the community to assist you better.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

sharif_ahmmad
Explorer

@niketnilay hi, i added more details. Kindly Let me know if it make sense now.

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