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
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
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.
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.
@ITWhisperer Sorry for incorrectly mentioning it as json. But the above format is exactly how I get the data.
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
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}
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?
That is definitely a copy/paste error of using notes.
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
Thank you so so much. I just tried it out and gave the exact visualization I was looking for!
@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}}"
I am not sure what you are asking for - the gear_name field doesn't appear affect the previous search
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
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)
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).
Is it possible that controlVersion does not exist for all results?
That may not be the case. All the run's have controlVersion.
What results are you getting (I don't understand what you mean by duplicates)?
@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
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'.