How to modify field values?

[Splunkuser18]

Hi

I have the following fields (FileName and FileSize) that I'd like to turn into the example table below. How can I remove the commas and the opening and closing square brackets from the field values?

FileName
-----------
[]  <--- empty
[yellow.png, orange.jpg, green.gif]
[yellow.png, orange.jpg]

FileSize
-----------
[0]
[4321, 45678, 321]
[4321, 45678]

Table example
------------------------------------------------------
|   FileName      |     FileSize    |   Count
------------------------------------------------------
|   yellow.png    |     4321         |  2
|   orange.jpg    |     45678        |  2
|   green.gif    |  321          |  1

Thanks in advance

[woodcock]

Like this:

Your Search Here
| foreach * [ rex field=<<FIELD>> mode=sed "s/[\s\[\]]//g" | makemv delim="," <<FIELD>> ]
| eval tuple=mvzip(FileName, FileSize)
| fields tuple
| mvexpand tuple
| rex field=tuple "^(?<FileName>[^,]+),(?<FileSize>[^,]+)$"
| stats count avg(FileSize) AS FileSize BY FileName

[woodcock]

Did you try any others? This one is simpler...

[woodcock]

Like this:

Your Search Here
| eval _count=0
| foreach * [ rex field=<<FIELD>> mode=sed "s/[,\[\]]//g"
            | eval _count = _count + if(isnotnull(<<FIELD>>), 1, 0) ]
| rename _count AS count

[renjith_nair · Accepted solution]

Hi @Splunkuser18,

Try this,

"base search to extract FileName and FileSize"|table FileName,FileSize|eval FileName=replace(FileName,"\[|\]","")|eval FileSize=replace(FileSize,"\[|\]","")
|eval FileName=if(FileName=="","EMPTY",FileName)
|makemv delim="," FileName|makemv delim="," FileSize|eval zipped=mvzip(FileName,FileSize)|fields zipped
|mvexpand zipped|eval splitted=split(zipped,",")|eval FileName=mvindex(splitted,0),FileSize=mvindex(splitted,1)|fields FileName,FileSize
|stats count ,values(FileSize) as FileSize by FileName