Dashboard to show top non-compliance controls

[nehashanker]

Greetings All,

I am very new to splunk and am creating a dashboard to show top non-compliances. For the below data, I want to display top non-compliant controls (example output also mentioned below)

Could anyone please let me know how can I write a search query for the same?

Thanks in advance.

Event_ID: abc1

Compliance_result: Non-Compliant

Eval_results: {

required_tags: {

compliance: Compliant

}

encryption_enabled:{

compliance: Non-Compliant

}

public_access:{

compliance: Compliant

}

policy_enabled:{

compliance: Compliant

}

}

Event_ID: abc2

Compliance_result: Non-Compliant

Eval_results: {

required_tags: {

compliance: Compliant

}

encryption_enabled:{

compliance: Non-Compliant

}

public_access:{

compliance: Non-Compliant

}

policy_enabled:{

compliance: Compliant

}

}

Generate Table in the below format -

Top Non Compliance controls:

public_access - 2

encryption_enabled -1

[ITWhisperer]

Please share the raw events (anonymised of course), not a formatted version, e.g. are your actual event JSON format, or partially, and, if so, is the JSON already extracted into a field? Please use a code block </> to share the raw events so it is more accurate and easier to use

[nehashanker]

additional_metrics: { [+]
   }
   event_id: abc123
   eval_results: { [+]
   }
   compliance_result: Non-Compliant
   timestamp: 2021-10-07 16:27:48.523
   version: jpltest

Upon expanding eval_results - 

eval_results: { [-]
     Required_Tags: { [+]
     }
     Valid_ASV_BA_Tags: { [+]
     }
     check_in_transit_encryption: { [+]
     }
   }

Upon expanding each of the following sections - 

Required_Tags: { [-]
       compliant: Compliant
       controlVersion: 1.0
       evaluationDetails:
       riskRating: Low
     }

Valid_ASV_BA_Tags: { [-]
       compliant: Non-Compliant
       controlVersion: 1.0
       evaluationDetails: Neither the ASV nor BA tag values(case sensitive) were found on the approved list.
       riskRating: Low
     }


check_in_transit_encryption: { [-]
       compliant: Compliant
       controlVersion: 1.0
       evaluationDetails:
       riskRating: Medium
     }

@ITWhisperer Thanks for looking into the issue. Pasted above is the raw event. The event is in json format, which I have got by searching on index name.

[ITWhisperer]

This isn't JSON - the names and values should have double quotes around them (as appropriate) and the [-]/[+] are format shortcuts to show expanded/collapsed collections.

[nehashanker]

@ITWhisperer Sorry for incorrectly mentioning it as json. But the above format is exactly how I get the data.

[ITWhisperer]

It could still be JSON - what you have shown looks very much like formatted JSON. Try this and see what you get in the _raw field (which is the raw event data)

| table _raw

[nehashanker]

Here is the response by using | table_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", "resource_type": ["sns_topic"], "event_status": "SUCCESS", "compliance_result": "Compliant", “evaluation_results": {"Required_Tags": {"compliant": "Compliant", "controlVersion": "1.0", "evaluationDetails": "", "riskRating": "Low",}, "Valid_ASV_BA_Tags": {"compliant": "Compliant", "controlVersion": "1.0", "evaluationDetails": "Neither the ASV nor BA tag values(case sensitive) were found on the approved list.", "riskRating": "Low","}, "check_in_transit_encryption": {"compliant": "Compliant", "controlVersion": "1.0", "evaluationDetails": "", "riskRating": "Medium", }, "correlation_id": "4362-47fb", "service": “biosevent", "timestamp": "2021-10-06 13:29:31.143", "version": "3.0.16", "duration": 18.961}

[ITWhisperer]

Some of your double quotes aren't standard double quotes - is this an artefact of your copy/paste or are they really present in the data?

[nehashanker]

That is definitely a copy/paste error of using notes.

[ITWhisperer]

I fixed a few other copy/paste issues and changed one of the compliants to noncompliant

| 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\", \"resource_type\": [\"sns_topic\"], \"event_status\": \"SUCCESS\", \"compliance_result\": \"Compliant\", \"evaluation_results\": {\"Required_Tags\": {\"compliant\": \"Compliant\", \"controlVersion\": \"1.0\", \"evaluationDetails\": \"\", \"riskRating\": \"Low\"}, \"Valid_ASV_BA_Tags\": {\"compliant\": \"Compliant\", \"controlVersion\": \"1.0\", \"evaluationDetails\": \"Neither the ASV nor BA tag values(case sensitive) were found on the approved list.\", \"riskRating\": \"Low\"}, \"check_in_transit_encryption\": {\"compliant\": \"Non-Compliant\", \"controlVersion\": \"1.0\", \"evaluationDetails\": \"\", \"riskRating\": \"Medium\" }, \"correlation_id\": \"4362-47fb\", \"service\": \"biosevent\", \"timestamp\": \"2021-10-06 13:29:31.143\", \"version\": \"3.0.16\", \"duration\": 18.961}}" 
| spath evaluation_results 
| spath input=evaluation_results 
| foreach *.compliant 
    [| eval Compliance=if('<<FIELD>>'="Compliant",if(isnull(Compliance),"<<MATCHSEG1>>",mvappend(Compliance,"<<MATCHSEG1>>")),Compliance)
    | eval NonCompliance=if('<<FIELD>>'="Compliant",NonCompliance,if(isnull(NonCompliance),"<<MATCHSEG1>>",mvappend(NonCompliance,"<<MATCHSEG1>>")))]
| top NonCompliance

[nehashanker]

Thank you so so much. I just tried it out and gave the exact visualization I was looking for!

[nehashanker]

@ITWhisperer  Sorry for opening the old thread again. 

how can I add a new column - gear_name for the below input:
I tried tweaking your query to add a new column but no success. So thought I will ask on this thread again. 

| 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\": {\"Required_Tags\": {\"compliant\": \"Compliant\", \"controlVersion\": \"1.0\", \"evaluationDetails\": \"\", \"riskRating\": \"Low\"}, \"Valid_ASV_BA_Tags\": {\"compliant\": \"Compliant\", \"controlVersion\": \"1.0\", \"evaluationDetails\": \"Neither the ASV nor BA tag values(case sensitive) were found on the approved list.\", \"riskRating\": \"Low\"}, \"check_in_transit_encryption\": {\"compliant\": \"Non-Compliant\", \"controlVersion\": \"1.0\", \"evaluationDetails\": \"\", \"riskRating\": \"Medium\" }, \"correlation_id\": \"4362-47fb\", \"service\": \"biosevent\", \"timestamp\": \"2021-10-06 13:29:31.143\", \"version\": \"3.0.16\", \"duration\": 18.961}}" 

[ITWhisperer]

I am not sure what you are asking for - the gear_name field doesn't appear affect the previous search

[nehashanker]

Sorry for the confusion. I want to display the controlVersion along with the name of the control. 

current output:

NonCompliance(Name)          Count         percentage

Expected output:

NonCompliance(Name)          Count         percentage          controlVersion

[ITWhisperer]

You are going to need to concatenate the control version to the tag name, then split it off afterwards

| 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\": {\"Required_Tags\": {\"compliant\": \"Compliant\", \"controlVersion\": \"1.0\", \"evaluationDetails\": \"\", \"riskRating\": \"Low\"}, \"Valid_ASV_BA_Tags\": {\"compliant\": \"Compliant\", \"controlVersion\": \"1.0\", \"evaluationDetails\": \"Neither the ASV nor BA tag values(case sensitive) were found on the approved list.\", \"riskRating\": \"Low\"}, \"check_in_transit_encryption\": {\"compliant\": \"Non-Compliant\", \"controlVersion\": \"1.0\", \"evaluationDetails\": \"\", \"riskRating\": \"Medium\" }, \"correlation_id\": \"4362-47fb\", \"service\": \"biosevent\", \"timestamp\": \"2021-10-06 13:29:31.143\", \"version\": \"3.0.16\", \"duration\": 18.961}}"
| spath evaluation_results 
| spath 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>>.controlVersion',mvappend(NonCompliance,"<<MATCHSEG1>>".":".'<<MATCHSEG1>>.controlVersion')))]
| top NonCompliance
| eval controlVersion=mvindex(split(NonCompliance,":"),1)
| eval NonCompliance=mvindex(split(NonCompliance,":"),0)

[nehashanker]

Thank you so much for your response. I didn't know that we should concatenate first and then split. This worked, but I am seeing duplicate values (only count and percentages). 

[ITWhisperer]

Is it possible that controlVersion does not exist for all results?

[nehashanker]

That may not be the case. All the run's have controlVersion. 

[ITWhisperer]

What results are you getting (I don't understand what you mean by duplicates)?

[nehashanker]

@ITWhisperer Just checking to see if you had a chance to see the query on why the query to add another column to show RiskRating is not working.

| spath evaluation_results

| spath 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',"<<MATCHSEG2>>".":".'<<MATCHSEG2>>.riskRating')),Compliance)

| eval NonCompliance=if('<<FIELD>>'="Compliant",NonCompliance,if(isnull(NonCompliance),"<<MATCHSEG1>>".":".'<<MATCHSEG1>>.controlVersion',"<<MATCHSEG2>>".":".'<<MATCHSEG2>>.riskRating', mvappend(NonCompliance,"<<MATCHSEG1>>".":".'<<MATCHSEG1>>.controlVersion',"<<MATCHSEG2>>".":".'<<MATCHSEG2>>.riskRating')))]

| top NonCompliance

| eval controlVersion=mvindex(split(NonCompliance,":"),1)

| eval riskRating=mvindex(split(NonCompliance,":"),2)

| eval NonCompliance=mvindex(split(NonCompliance,":"),0)

[nehashanker]

Sorry, I was getting repetitive count of some of the values. That seems to be an error in the data.
However, I tried adding a new column and modified the query which you had sent, but seems to be throwing an error. The intention is to show 'Risk Rating'.

| spath evaluation_results

| spath 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',"<<MATCHSEG2>>".":".'<<MATCHSEG2>>.riskRating')),Compliance)

| eval NonCompliance=if('<<FIELD>>'="Compliant",NonCompliance,if(isnull(NonCompliance),"<<MATCHSEG1>>".":".'<<MATCHSEG1>>.controlVersion',"<<MATCHSEG2>>".":".'<<MATCHSEG2>>.riskRating', mvappend(NonCompliance,"<<MATCHSEG1>>".":".'<<MATCHSEG1>>.controlVersion',"<<MATCHSEG2>>".":".'<<MATCHSEG2>>.riskRating')))]

| top NonCompliance

| eval controlVersion=mvindex(split(NonCompliance,":"),1)

| eval riskRating=mvindex(split(NonCompliance,":"),2)

| eval NonCompliance=mvindex(split(NonCompliance,":"),0)