I have a raw the i extract and filter and table them according to Country
_raw
[{"Conutry":"America","State":"Nevada","Building":"Small"},
{"Conutry":"America","State":"Nevada","Building":"Medium"},
{"Conutry":"America","State":"Nevada","Building":"Large"},
{"Conutry":"Canada","State":"Montreal","Building":"Small"},
{"Conutry":"Canada","State":"Montreal","Building":"Medium"},
{"Conutry":"Canada","State":"Montreal","Building":"Large"}
{"Conutry":"Spain","State":"Barcelona","Building":"Small"},
{"Conutry":"Spain","State":"Barcelona","Building":"Medium"},
{"Conutry":"Spain","State":"Barcelona","Building":"Large"},
{"Conutry":"Spain","State":"Barcelona","Building":"Extra_Large"}]
etc....
My Search is:
index=xyz
| sourcepath=xyz
| rename {}.* as *
| eval tmp=mvzip(mvzip(Conutry,State),Building)
| mvexpand tmp | eval Conutry=mvindex(split(tmp,","),0),State=mvindex(split(tmp,","),1),Building=mvindex(split(tmp,","),2)
| table Conutry, State, Building
My Results are grouped by country as follows
Country..../....State..../...Building
America......Nevada........Small
....................Nevada.........Medium
....................Nevada.........Large
Canada.......Montreal......Small
....................Montreal.......Medium
....................Montreal.......Large
Spain.........Barcelona......Small
...................Barcelona......Medium
...................Barcelona......Large
...................Barcelona......Extra_Large
etc....
How do i search or filter out the "Building" Column so that if it contains anything OTHER THAN "Small\Medium\Large", display the results. Note the field "Extra_Large" is NOT the same and can be any word
Expected Results should be
Country..../....State..../...Building
Spain.........Barcelona......Small
...................Barcelona......Medium
...................Barcelona......Large
...................Barcelona......Extra_Large
France.......Paris................Small
...................Paris................Medium
...................Paris................Large
...................Paris................Too_Small
Any help appreciated
Hi @NayneshPatel :
Can you try by mvfilter, her an example that can filter Building:
| makeresults
| eval HRA="Small,Medium,Large,Extra_Large,Too_Small"
| eval HRA=split(HRA,",")
| mvexpand HRA
| stats values(HRA) as HRA
| eval x=mvfilter(NOT (like(HRA,"Medium") OR like(HRA,"Small") OR like(HRA,"Large")))
Hi @NayneshPatel :
Can you try by mvfilter, her an example that can filter Building:
| makeresults
| eval HRA="Small,Medium,Large,Extra_Large,Too_Small"
| eval HRA=split(HRA,",")
| mvexpand HRA
| stats values(HRA) as HRA
| eval x=mvfilter(NOT (like(HRA,"Medium") OR like(HRA,"Small") OR like(HRA,"Large")))
Thanks Tikar
Added "where !=" to remove the blank rows
Appreciate your Help