Mvexpand

[SN1]

Hello I have this search

| inputlookup defender_onboard.csv
| fillnull value=NA
| search Region="***" 4LetCode="*"
| search NOT [inputlookup ex_sou.csv| fields DeviceName]
| search NOT [inputlookup ex_defender.csv | fields DeviceName]
| table DeviceName Region DeviceType OSType OSVersion

now i am getting this result

i want region to be expanded to get individual row.

[livehybrid]

Hi @SN1 

Are you wanting to get rid of duplicates? e.g. so that only EMEA only appears once for bar-t1001.homag-group?

If so I think the following might help

| stats values(*) AS * by DeviceName

Please let me know how you get on and consider adding karma to this or any other answer if it has helped.
Regards

Will

[kiran_panchavat]

@SN1 

To expand the Region field into individual rows for each value, you can use the makemv command to convert the Region field into a multivalue field (if it's not already) and then use mvexpand to generate a row for each value.

makemv: Converts the Region field into a multivalue field using the comma as a delimiter.

mvexpand: Expands each multivalue Region into separate rows.

| makeresults count=5
| eval DeviceName = "mt_20736887n11.homag.com", Region = "NA,EMEA", DeviceType = "Workstation", OSType = "Windows10", OSVersion = "10.0"
| append [| makeresults count=1 | eval DeviceName = "par-t-1801.homag-group", Region = "EMEA", DeviceType = "Workstation", OSType = "Linux", OSVersion = "null"]
| append [| makeresults count=1 | eval DeviceName = "usbrelais.homag.com", Region = "NA", DeviceType = "Workstation", OSType = "Windows10", OSVersion = "10.0"]
| makemv delim="," Region
| mvexpand Region
| table DeviceName Region DeviceType OSType OSVersion