topic Re: Pie Chart AppendCol Data in Dashboards & Visualizations

Pie Chart AppendCol Data

fbond_diligent — Tue, 18 Aug 2020 12:42:34 GMT

I have successfully used appendcol to gather percentage of successful and failed scans. I would like to make this data into a pie chart for my dashboard.

When I click visualization-> pie chart it is all one color.

Here is my search

index="secops" sourcetype="tenable:sc:vuln" plugin_id=19506 pluginText!="*Host_Scan*" | dedup ip | search pluginText="*Credentialed checks : no*" | stats count(plugin_id) as failed_scans | appendcols [search index="secops" sourcetype="tenable:sc:vuln" plugin_id=19506 pluginText!="*Host_Scan*" | dedup ip | search pluginText="*Credentialed checks : yes*" | stats count(plugin_id) as successful_scans] | eval total_scans=(failed_scans+successful_scans) | eval failed_percent=(failed_scans/total_scans*100) | eval failed_percent=round(failed_percent,2) | eval success_percent=(successful_scans/total_scans*100) | eval success_percent=round(success_percent,2) | table success_percent failed_percent

Thank you in advanced.

Re: Pie Chart AppendCol Data

niketn — Fri, 21 Aug 2020 15:44:11 GMT

[Updated]
Corrected typo for match() condition which does not need *

___________________________

@fbond_diligent as your final pipe if you add transpose command it should show Pie chart (convert result columns to rows).

| transpose

However, there are a lot of performance steps that you need to adopt. https://docs.splunk.com/Documentation/Splunk/8.0.5/Search/Writebettersearches

Filter only required events from index based on pluginText as yes and no.
Perform stats using specific fields to deduplicate instead dedup with all the fields.
Avoid subsearch as it introduces sub-search limitations (which may drop events if you hit limit).
Chain eval as single eval for performance and simple code.

index="secops" sourcetype="tenable:sc:vuln" plugin_id=19506 pluginText!="*Host_Scan*" pluginText IN ("*Credentialed checks : yes*","*Credentialed checks : no*") | stats count as total_scans count(eval(match(pluginText,"Credentialed checks : no"))) as failed_scans count(eval(match(pluginText,"Credentialed checks : yes"))) as successful_scans by ip | stats sum(*_scans) as *_scans | eval failed_percent=round((failed_scans/total_scans*100),2), success_percent=round((success_percent/total_scans*100),2) | fields success_percent failed_percent | transpose 0 header_field="status" | rename "row 1" as percent

Re: Pie Chart AppendCol Data

fbond_diligent — Thu, 20 Aug 2020 12:40:10 GMT

@niketn

Thank you for your reply, I will take a look at the website your provided.

However when I try your search I am getting the following error:

Error in 'stats' command: The eval expression for dynamic field 'eval(match(pluginText,"*Credentialed checks : no*"))' is invalid. Error='Regex: quantifier does not follow a repeatable item'.

Thank you,

Rick

Re: Pie Chart AppendCol Data

niketn — Fri, 21 Aug 2020 15:45:21 GMT

@fbond_diligent sorry for the copy paste issue. match() condition is regex based. So * is not required. Please check updated SPL.