topic Can you please help me in creating a table with multiple rows of the same field using stats or some other code? in Splunk Search

Can you please help me in creating a table with multiple rows of the same field using stats or some other code?

dinu1701 — Wed, 30 Sep 2020 03:51:11 GMT

I need to display multiple rows having the same PART_NUMBER value for each FLIT_COMPONENTS and AMOUNT

sourcetype=flit_info ------ has PART_NUMBER, PLANT_CODE, PALLET_TYPE, DISPATCH_UNIT , CAPACITY , FLIT_RULE_ID

sourcetype=flit_comp ----------- has AMOUNT, UPDATED_BY

both of these sourcetypes have only one common field which is "ID" in sourcetype="flit_info" AND "FLIT_INSTRUCTION_ID" in sourcetype="flit_comp" . I was able to do stats by combining these two fields in a field only.

Code I have used -

index=ibldc-db sourcetype="flit_info" OR sourcetype="flit_comp"
| dedup ID
| rename FLIT_INSTRUCTION_ID as IDc
| eval IDs=coalesce(IDc,ID)
| makemv IDs
| stats list(PART_NUMBER) as PART_NUMBER list(PLANT_CODE) as PLANT_CODE list(PALLET_TYPE) as PALLET_TYPE list(CAPACITY) as CAPACITY list(DU) as DISPATCH_UNIT list(BUNDLE_COMPONENT) as FLIT_COMPONENTS list(AMOUNT) as AMOUNT list(RULE_ID) as FLIT_RULE_ID list(UPDATED_BY) as UPDATED_BY by IDs
| table PART_NUMBER PLANT_CODE PALLET_TYPE CAPACITY FLIT_COMPONENTS AMOUNT DISPATCH_UNIT FLIT_RULE_ID UPDATED_BY

Results are like -

What is expected is that the PART_NUMBER should fill all the rows wherever the FLIT_COMPONENTS are present

mulitple rows for each FLIT_COMPONENTS , that is the same values of the PART_NUMBER and the rest of the columns should be individual rows so that if we download the report in excel each row will have PART_NUMBER and FLIT_COMPONENTS

I need your inputs on how to build this query?

Re: Can you please help me in creating a table with multiple rows of the same field using stats or some other code?

kamlesh_vaghela — Wed, 29 Jan 2020 13:36:36 GMT

@dinu1701

Can you please try by adding below search into your search?

| eval tmp=mvzip(mvzip(FLIT_COMPONENTS, AMOUNT), UPDATED_BY) 
| mvexpand tmp
| eval FLIT_COMPONENTS=mvindex(split(tmp,","),0), AMOUNT=mvindex(split(tmp,","),1), UPDATED_BY=mvindex(split(tmp,","),2) 
| fields - tmp

Re: Can you please help me in creating a table with multiple rows of the same field using stats or some other code?

dinu1701 — Wed, 30 Sep 2020 03:51:22 GMT

@kamlesh_vaghela Thanks for your reply

When I execute this query I get

The search job has failed due to an error. You may be able view the job in the Job Inspector

only this I am able to execute -
| eval tmp=mvzip(mvzip(FLIT_COMPONENTS, AMOUNT), UPDATED_BY)

which creates a subset like this -
10,20,users
30,20,reddu

I need a subset which attaches to PART_NUMBER because its from a different

Re: Can you please help me in creating a table with multiple rows of the same field using stats or some other code?

kamlesh_vaghela — Wed, 29 Jan 2020 14:35:41 GMT

@dinu1701

Ooh, my bad. silly mistake. I have updated search. can you please try that?

Re: Can you please help me in creating a table with multiple rows of the same field using stats or some other code?

13tsavage — Wed, 30 Sep 2020 03:51:41 GMT

You are adding an extra component that is not needed if you are wanting to create a table to show PART_NUMBER, FLIT_COMPONENTS and AMOUNT.

According to Splunk Docs, list() returns a list of up to 100 values of the field X as a multivalue entry. That is why you are getting multiple entries FLIT_COMPONENTS and AMOUNT for a specific field PART_NUMBER.

I recommend using rename instead of stats list(field) as new_field if you are just changing field names to show in the table. You also have a by IDs at the end of your stats but then do not keep it in your table command.

I would try to look at this search:
**
index=ibldc-db sourcetype="flit_info" OR sourcetype="flit_comp"
| dedup ID
| rename FLIT_INSTRUCTION_ID as IDc, DU as DISPATCH_UNIT, BUNDLE_COMPONENT as FLIT_COMPONENTS, RULE_ID as FLIT_RULE_ID
| eval IDs=coalesce(IDc, ID)
| makemv IDs
| table PART_NUMBER PLANT_CODE PALLET_TYPE CAPACITY FLIT_COMPONENTS AMOUNT DISPATCH_UNIT FLIT_RULE_ID UPDATED_BY
**

in the table part you can feel free to add or remove whatever fields you want to show at the end of your search.

Hope this helps!

Re: Can you please help me in creating a table with multiple rows of the same field using stats or some other code?

dinu1701 — Wed, 30 Sep 2020 04:09:10 GMT

@kamlesh_vaghela Still I am getting the same table
when I do stats all the for one part_number row there are multiple values of FLIT_COMPONENT and AMOUNT.

Though the code you shared is correct, what I need is multiple rows of PART_NUMBER matching each row of FLIT_COMPONENT

Expected -

PART_NUMBER | FLIT_COMPONENT | AMOUNT
XXXXXXXX | 17 | 1
XXXXXXXX | 18 | 2

Observed result -
PART_NUMBER | FLIT_COMPONENT | AMOUNT
XXXXXXXX | 17 | 1
| 18 | 2

one row of PART_NUMBER is combined in the same row with multiple amounts for FLIT_COMPONENT and AMOUNT

Re: Can you please help me in creating a table with multiple rows of the same field using stats or some other code?

dinu1701 — Wed, 30 Sep 2020 04:09:13 GMT

@13tsavage .
Still I am getting the same table
when I do stats all the for one part_number row there are multiple values of FLIT_COMPONENT and AMOUNT.

Though the code you shared is correct, what I need is multiple rows of PART_NUMBER matching each row of FLIT_COMPONENT

Expected -

PART_NUMBER | FLIT_COMPONENT | AMOUNT
XXXXXXXX | 17 | 1
XXXXXXXX | 18 | 2

Observed result -
PART_NUMBER | FLIT_COMPONENT | AMOUNT
XXXXXXXX | 17 | 1
| 18 | 2

one row of PART_NUMBER is combined in the same row with multiple amounts for FLIT_COMPONENT and AMOUNT.

Re: Can you please help me in creating a table with multiple rows of the same field using stats or some other code?

to4kawa — Mon, 10 Feb 2020 13:31:49 GMT

| stats list(PART_NUMBER) as PART_NUMBER list(PLANT_CODE) as PLANT_CODE list(PALLET_TYPE) as PALLET_TYPE list(CAPACITY) as CAPACITY list(DU) as DISPATCH_UNIT list(BUNDLE_COMPONENT) as FLIT_COMPONENTS list(AMOUNT) as AMOUNT list(RULE_ID) as FLIT_RULE_ID values(UPDATED_BY) as UPDATED_BY by IDs
| eval tmp=mvzip(FLIT_COMPONENTS,AMOUNT)
| fields - FLIT_COMPONENTS,AMOUNT
| mvexpand tmp
| eval FLIT_COMPONENTS=mvindex(split(tmp,","),0) ,AMOUNT=mvindex(split(tmp,","),1)
| table PART_NUMBER PLANT_CODE PALLET_TYPE CAPACITY FLIT_COMPONENTS AMOUNT DISPATCH_UNIT FLIT_RULE_ID UPDATED_BY

| eval IDs=coalesce(IDc,ID)
| makemv IDs

What Is IDs ?
mekamv splits by space. so, "XXXXX XXXX"?

Re: Can you please help me in creating a table with multiple rows of the same field using stats or some other code?

13tsavage — Wed, 30 Sep 2020 04:04:51 GMT

I never had stats in my search, so I am not sure how you are getting the same table.

What you are looking for then is

index=ibldc-db sourcetype="flit_info" OR sourcetype="flit_comp"
| dedup ID
| rename FLIT_INSTRUCTION_ID as IDc, DU as DISPATCH_UNIT, BUNDLE_COMPONENT as FLIT_COMPONENTS, RULE_ID as FLIT_RULE_ID
| eval IDs=coalesce(IDc, ID)
| makemv IDs
| table PART_NUMBER, FLIT_COMPONENTS, AMOUNT

Feel free to add whatever fields after table as you would like. Especially if you want the IDs field in the table or not.

Re: Can you please help me in creating a table with multiple rows of the same field using stats or some other code?

13tsavage — Mon, 10 Feb 2020 15:24:21 GMT

If the table you are wanting to present at the end does not have all the fields then you do not need to add the eval IDs, makemv IDs or dedup ID because that is just extra work for your search. The question to ask is what exactly you want as your result, then tailor your search to fit your results.