How to filter out specific rows in a table when the values come from JSON list ?

[bugnet]

Hi all,

I have a table like the one below, with a column containing 'Type', 'Value', 'alert id'.
the problem is that the columns Type and Value coming from JSON list and I can't find a way to select specific values.
I want to show in the table specific values with the same 'id'.

Search:

index="alerts" | spath "MISP{}.FullContext{}.Event.Galaxy{}.GalaxyCluster{}.value" | spath id | spath "MISP{}.FullContext{}.Event.Galaxy{}.GalaxyCluster{}.type" | rename "MISP{}.FullContext{}.Event.Galaxy{}.GalaxyCluster{}.type" as Type | rename "MISP{}.FullContext{}.Event.Galaxy{}.GalaxyCluster{}.value" as Value | table Type Value id | where Type="sector"

Results: (I want to show only the sector Type)

[kamlesh_vaghela]

@bugnet

Can you please share sample event with masking values AND expected results??

[bugnet]

Expected results:

https://ibb.co/RNxsJtY

[kamlesh_vaghela]

@bugnet

Can you please try this?

index="alerts" 
| spath "MISP{}.FullContext{}.Event.Galaxy{}.GalaxyCluster{}.value" 
| spath id 
| spath "MISP{}.FullContext{}.Event.Galaxy{}.GalaxyCluster{}.type" 
| rename "MISP{}.FullContext{}.Event.Galaxy{}.GalaxyCluster{}.type" as Type 
| rename "MISP{}.FullContext{}.Event.Galaxy{}.GalaxyCluster{}.value" as Value 
| eval temp=mvzip(Type,Value) 
| mvexpand temp 
| eval Type = mvindex(split(temp,","),0), Value = mvindex(split(temp,","),1) 
| where Type="sector" 
| table Type Value id

[Richfez]

You asked it (at the end of your search) for events where Type = sector, and both returned events do INDEED have "sector" in their Type. What it is that you want instead - to return only the portions that actually correspond to the "sector" field?

E.g. you would like a list that has 7 rows in it, corresponding to the 7 "sector" values?

Does that sound right?

[bugnet]

@rich7177 Yes, I want to return only the portions that actually correspond to the "sector" field.