topic Re: Combining separate fields to multi values in Splunk Search

Combining separate fields to multi values

thdose — Wed, 05 Oct 2016 15:05:04 GMT

I have 300 match_ fields per event. Here are the first 9 from one event:

match_1="Don\'t Be So Shy" match_2="Imany" match_3="8,980,580" match_4="Human" match_5="Rag\'n\'Bone Man" match_6="763,862" match_7="Closer" match_8=" Chainsmokers" match_9="3,671,646"...etc

I want to structure the result so I'll get triplets of data as:

title="Don\'t Be So Shy" name="Imany" score="8,980,580" 
title="Human" name="Rag\'n\'Bone Man" score="763,862" 
title="Closer" name="Chainsmokers" score="8,980,580"...etc

I am messing around with modulus and mv functions in order to first connect the 3 relating fields and then expand to simulate the triplets as each their event, but with no succes.

Any help would be much appreciated.

Re: Combining separate fields to multi values

somesoni2 — Wed, 05 Oct 2016 16:36:18 GMT

Give this a try

your base search | table match_* | eval temp=1 | untable temp fieldname fieldvalue 
| eval rank=floor((tonumber(mvindex(split(fieldname,"_"),1))+1)/3) | streamstats count as label by rank |
eval label=case(label=1,"title", label=2,"name",true(),"score") | chart values(fieldvalue) over rank by label

Update

your base search | table match_* | eval temp=1 | accum temp| untable temp fieldname fieldvalue 
| eval rank=floor((tonumber(mvindex(split(fieldname,"_"),1))+1)/3) | eval rank=temp."-".rank | streamstats count as label by rank | eval label=case(label=1,"title", label=2,"name",true(),"score") | chart values(fieldvalue) over rank by label | fields - rank

Re: Combining separate fields to multi values

thdose — Wed, 05 Oct 2016 16:54:32 GMT

Thanks. Almost, but not quite. It seems some values get mixed up:

Re: Combining separate fields to multi values

somesoni2 — Wed, 05 Oct 2016 17:05:38 GMT

Try the updated answer.

Re: Combining separate fields to multi values

thdose — Wed, 05 Oct 2016 17:15:02 GMT

Re: Combining separate fields to multi values

somesoni2 — Wed, 05 Oct 2016 18:22:40 GMT

Seems like there are some null values for some of the fields due to which the order of assignment is failing. Can you confirm if that is the case (try to run everything before the last chart command in the search and see the result).

Re: Combining separate fields to multi values

thdose — Wed, 05 Oct 2016 18:49:27 GMT

If can't seem to find any null values. Here are the first fields in the first event:

raw_match_count="300" match_1="Don\'t Be So Shy (Filatov & Karas Remix)" match_2="Imany" match_3="8,980,580" match_4="Human" match_5="Rag\'n\'Bone Man" match_6="763,862" match_7="Closer" match_8="The Chainsmokers Feat. Halsey" match_9="3,671,646" match_10="Dancing On My Own" match_11="Calum Scott" match_12="678,533" match_13="Starboy" match_14="The Weeknd Feat. Daft Punk" match_15="802,807" match_16="Still Falling For You" match_17="Ellie Goulding" match_18="581,123" match_19="The Greatest" match_20="Sia Feat. Kendrick Lamar" match_21="926,971" match_22="Sexual" match_23="NEIKED" match_24="108,857" match_25="Starving" match_26="Hailee Steinfeld & Grey Feat. Zedd" match_27="758,657" match_28="Heathens" match_29="Twenty One Pilots" match_30="3,066,742"

Re: Combining separate fields to multi values

somesoni2 — Wed, 05 Oct 2016 21:19:18 GMT

Can you provide a screenshot of following query? need to see first few lines as they are the one which are not working.

your base search | table match_* | eval temp=1 | accum temp| untable temp fieldname fieldvalue  | eval rank=floor((tonumber(mvindex(split(fieldname,"_"),1))+1)/3) | eval rank=temp."-".rank | streamstats count as label by rank | eval label=case(label=1,"title", label=2,"name",true(),"score")

Re: Combining separate fields to multi values

maciep — Thu, 06 Oct 2016 00:23:59 GMT

Not sure if this will work, but if the data is consistent maybe you can use rex to along with some mv commands

| stats count | eval blah="match_1=\"Don\'t Be So Shy\" match_2=\"Imany\" match_3=\"8,980,580\" match_4=\"Human\" match_5=\"Rag\'n\'Bone Man\" match_6=\"763,862\" match_7=\"Closer\" match_8=\" Chainsmokers\" match_9=\"3,671,646\"" 
| rex field=blah "match\_\d+=(?<title>\"[^\"]+\")\s+match\_\d+=(?<name>\"[^\"]+\")\s+match\_\d+=(?<score>\"[^\"]+\")" max_match=0 
| eval temp = mvzip(title,name) 
| eval temp = mvzip(temp,score) 
| fields temp 
| mvexpand temp 
| rex field=temp "^(?<title>[^,]+),(?<name>[^,]+),(?<score>.+)" 
| fields - temp 
| table title name score

Re: Combining separate fields to multi values

thdose — Thu, 06 Oct 2016 11:07:41 GMT

Sure:

Re: Combining separate fields to multi values

DalJeanis — Wed, 17 May 2017 19:09:20 GMT

The problem is that...

| eval rank=floor((tonumber(mvindex(split(fieldname,"_"),1))+1)/3)

...needs to be...

| eval rank=floor((tonumber(mvindex(split(fieldname,"_"),1))+2)/3)

...and it works just fine.

Run anywhere demonstration code -

| makeresults count=100 
| streamstats count as recno 
| eval recgroup = ceiling((1+recno)/10) 
| eval recgroup="A".if(len(recgroup)=1,"00","0").recgroup
| eval suffix=case(len(recno)=3,recno,len(recno)=2,"0".recno,true(),"00".recno) 
| eval name="name".suffix,song="song".suffix,score=8000000+recno
| eval stuff=mvappend(song,name,score)
| table stuff recgroup
| stats list(stuff) as stuff by recgroup
| mvexpand stuff
| streamstats count as recno
| eval suffix=case(len(recno)=3,recno,len(recno)=2,"0".recno,true(),"00".recno) 
| eval fieldname="match_".recno
| eval {fieldname} = stuff
| stats values(match*) as match*
| rename COMMENT as "The above just generates test data"

| rename COMMENT as "and we try somesoni2's last solution, with +1 changed to +2"
| untable recno fieldname fieldvalue 
| eval rank=floor((tonumber(mvindex(split(fieldname,"_"),1))+2)/3) 
| streamstats count as label by rank 
| eval label=case(label=1,"title", label=2,"name",true(),"score") 
| chart values(fieldvalue) over rank by label

... generating ...

rank   name          score        title
1         name001   8000001   song001  
2         name002   8000002   song002  
3         name003   8000003   song003

... etc