How to modify concatenate and split?

[nehashanker]

Hi All, 

I have the below query which gives the columns : Name.    Count.    Percentage.    ControlID

| spath evaluation_results 
| search gear_name
| spath input=evaluation_results  
| foreach *.compliant 
    [| eval Compliance=if('<<FIELD>>'="Compliant",if(isnull(Compliance),"<<MATCHSEG1>>".":".'<<MATCHSEG1>>.controlVersion',mvappend(Compliance,"<<MATCHSEG1>>".":".'<<MATCHSEG1>>.controlVersion')),Compliance)
    | eval NonCompliance=if('<<FIELD>>'="Compliant",NonCompliance,if(isnull(NonCompliance),"<<MATCHSEG1>>".":".'<<MATCHSEG1>>.controlID',mvappend(NonCompliance,"<<MATCHSEG1>>".":".'<<MATCHSEG1>>.controlVersion')))]
| top 50 NonCompliance 
| eval controlVersion=mvindex(split(NonCompliance,":"),1)
| eval NonCompliance=mvindex(split(NonCompliance,":"),0) | rename NonCompliance as "Name"

I have modified the above query to add a new column which shows RiskRating. 

| spath evaluation_results 
| search gear_name
| spath input=evaluation_results  
| foreach *.compliant 
    [| eval Compliance=if('<<FIELD>>'="Compliant",if(isnull(Compliance),"<<MATCHSEG1>>".":".'<<MATCHSEG1>>.controlVersion',"<<MATCHSEG2>>".":".'<<MATCHSEG2>>.riskRating',mvappend(Compliance,"<<MATCHSEG1>>".":".'<<MATCHSEG1>>.controlVersion',mvappend(Compliance,"<<MATCHSEG2>>".":".'<<MATCHSEG2>>.riskRating')),Compliance)
    | eval NonCompliance=if('<<FIELD>>'="Compliant",NonCompliance,if(isnull(NonCompliance),"<<MATCHSEG1>>".":".'<<MATCHSEG1>>.controlID',"<<MATCHSEG2>>".":".'<<MATCHSEG2>>.riskRating',mvappend(NonCompliance,"<<MATCHSEG1>>".":".'<<MATCHSEG1>>.controlVersion',"<<MATCHSEG2>>".":".'<<MATCHSEG2>>.riskRating')))]
| top 50 NonCompliance 
| eval controlVersion=mvindex(split(NonCompliance,":"),1)
| eval riskRating=mvindex(split(NonCompliance,":"),2)
| eval NonCompliance=mvindex(split(NonCompliance,":"),0) | rename NonCompliance as "Name"

Unfortunately the query doesn't execute. Can someone please help with where I got it wrong and what needs to be modified? The output columns should show as below:
Name.    Count.    Percentage.    ControlID.    Risk Rating.

Below is my raw source

| makeresults 
| eval _raw="{\"job_id\": \"abc123\", \"gear_event_id\": \"aaaa\", \"event_id\": \"7cf6-4ff0\", \"execution_start\": \"2021-10-06 13:29:31.143\", \"execution_end\": \"2021-10-06 13:29:50.104\", \"gear_version\": \"3.0.16\",\"gear_name\": \"sns\", \"resource_type\": [\"sns_topic\"], \"event_status\": \"SUCCESS\", \"compliance_result\": \"Compliant\", \"evaluation_results\": {\"Tags\": {\"compliant\": \"Compliant\", \"controlVersion\": \"1.0\", \"evaluationDetails\": \"\", \"riskRating\": \"Low\"}, \"Tags\": {\"compliant\": \"Compliant\", \"controlVersion\": \"1.0\", \"evaluationDetails\": \"not approved\", \"riskRating\": \"Low\"}, \"correlation_id\": \"4362-47fb\", \"service\": \"biosevent\", \"timestamp\": \"2021-10-06 13:29:31.143\", \"version\": \"3.0.16\", \"duration\": 18.961}}"

[somesoni2]

Just run till following search part and see how many values that you see under field NonCompliance (its a multivalued field). Your search with RiskRating field expect 3 values to be present for each row. If there are only two values then your foreach command needs revision.

| spath evaluation_results 
| search gear_name
| spath input=evaluation_results  
| foreach *.compliant 
    [| eval Compliance=if('<<FIELD>>'="Compliant",if(isnull(Compliance),"<<MATCHSEG1>>".":".'<<MATCHSEG1>>.controlVersion',"<<MATCHSEG2>>".":".'<<MATCHSEG2>>.riskRating',mvappend(Compliance,"<<MATCHSEG1>>".":".'<<MATCHSEG1>>.controlVersion',mvappend(Compliance,"<<MATCHSEG2>>".":".'<<MATCHSEG2>>.riskRating')),Compliance)
    | eval NonCompliance=if('<<FIELD>>'="Compliant",NonCompliance,if(isnull(NonCompliance),"<<MATCHSEG1>>".":".'<<MATCHSEG1>>.controlID',"<<MATCHSEG2>>".":".'<<MATCHSEG2>>.riskRating',mvappend(NonCompliance,"<<MATCHSEG1>>".":".'<<MATCHSEG1>>.controlVersion',"<<MATCHSEG2>>".":".'<<MATCHSEG2>>.riskRating')))]
| top 50 NonCompliance