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?
| 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"?
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!
@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.
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.
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.
@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
@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
@dinu1701
Ooh, my bad. silly mistake. I have updated search. can you please try that?
@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