Splunk Search

Combining separate fields to multi values

thdose
New Member

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.

0 Karma

maciep
Champion

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
0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

thdose
New Member

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

alt text

0 Karma

thdose
New Member

alt text

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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).

0 Karma

thdose
New Member

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"
0 Karma

somesoni2
SplunkTrust
SplunkTrust

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")
0 Karma

thdose
New Member

Sure:

alt text

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try the updated answer.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...