topic Re: How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value? in Splunk Search

How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value?

karu0711 — Mon, 31 Oct 2022 14:07:11 GMT

I use

index= main | lookup test1.csv Severity1 | stats count by Severity

The lookup table have 5 value ( Veryhigh, high, medium, low, verylow) how do I add this to x axis even If I do not have Y axis count for it. I want chart look like

Re: How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value?

ITWhisperer — Mon, 31 Oct 2022 14:12:52 GMT

Re: How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value?

richgalloway — Mon, 31 Oct 2022 14:18:46 GMT

The stats command only counts events that are there. It will not report 'zero' for other values of Severity because the set of such values is infinite (as far as the command knows). There is a workaround, however. Append the full list of severities with a zero count for each.

index= main | lookup test1.csv Severity1 | stats count by Severity [ append [ | inputlookup test1.csv | fields Severity } eval count = 0 ] | stats sum(count) as count by Severity

Re: How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value?

karu0711 — Mon, 31 Oct 2022 15:54:18 GMT

How do I do join this two table and I want all value in Severity and If "count" column has any value show that if not display "0" In this picture has very high and high has "4" rest have "0" so very high and High to display 4 and rest to display 0

Re: How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value?

karu0711 — Mon, 31 Oct 2022 16:01:45 GMT

Thanks for the quick reply

Re: How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value?

karu0711 — Mon, 31 Oct 2022 16:02:19 GMT

Thanks for quick reply.

Re: How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value?

ITWhisperer — Mon, 31 Oct 2022 16:03:55 GMT

It is not possible to see what your search is doing from a photo.

Re: How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value?

karu0711 — Mon, 31 Oct 2022 16:20:55 GMT

1st search

index="veracode_test" sourcetype="veracode" |lookup Veracode.csv Find_severity | stats count by Severity

2nd search

| inputlookup veracode.csv | fileds Severity |eval count = 0

I want join both search.

1 search return count value = 4

2 nd search return count value = 0

I want to join both search to the Severity fields if there no value I want it to be =0

Re: How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value?

richgalloway — Mon, 31 Oct 2022 16:41:20 GMT

I already showed you how to do that. Append the 2nd search to the first then use stats sum(count) to combine them.

Re: How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value?

karu0711 — Mon, 31 Oct 2022 16:50:03 GMT

when I apply the search I get the count as "0" only.

Re: How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value?

karu0711 — Mon, 31 Oct 2022 16:59:16 GMT

This what I got

Re: How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value?

ITWhisperer — Mon, 31 Oct 2022 17:04:32 GMT

You are probably getting those results because the field names do not match your events. If you could share some sample event in a code block </> not a picture, we might be able to help you more.

Re: How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value?

karu0711 — Mon, 31 Oct 2022 17:20:12 GMT

I was able to do with join command

Thanks

Re: How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value?

somesoni2 — Mon, 31 Oct 2022 18:20:27 GMT

This a solution but better/optimal solution is what @richgalloway has provided. Joins are expensive search command in terms of Splunk resources and subsearches have further limitations as well. If above search has worked, the translated Richard's solution would work too (and will work better):

Re: How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value?

karu0711 — Mon, 31 Oct 2022 18:39:02 GMT

I was able to get result using you command but how do I sort the Severity field value in this order

[very high ,high , medium, Low , Very Low]?

Re: How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value?

richgalloway — Mon, 31 Oct 2022 20:01:13 GMT

Create a separate field for sorting.

Re: How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value?

karu0711 — Tue, 01 Nov 2022 15:20:05 GMT

one more request

I also want to customize the x axis value fields such as Very High , High , medium , low diffrent color base. How can I do that? I want similar to this.

Re: How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value?

richgalloway — Tue, 01 Nov 2022 16:59:49 GMT

Try adding a charting.fieldColors statement to the panel XML.

... <panel> <chart> <search> ... </search> <option name="charting.fieldColors">{"Very High": 0xFF0000, "High": 0xFF9900, "Medium":0x0066FF, "Low":0xC4C4C0, "Very Low":0xBEEF00}</option> </panel> ...

You'll need to change the hex numbers to represent the colors you want displayed. See https://www.w3schools.com/colors/colors_picker.asp for color codes.

Re: How do I create bar chart where I want x axis to display all the value in field even If Y axis don't have value?

karu0711 — Tue, 01 Nov 2022 17:07:45 GMT

I add fieldscolor to the panel but still no color change

<dashboard> <label>test Veracode</label> <row> <panel> <title>Severity by flaw</title> <chart> <search> <query>index="veracode_test" sourcetype="Veracode_scan" | lookup Veracode.csv findings{}.severity | stats count by Severity | append [| inputlookup Veracode.csv | fields Severity | stats count by Severity | eval count = 0] | stats max(count) as Total by Severity | eval sorter = case(Severity="Very High", 5, Severity="High", 4, Severity="medium", 3, Severity="Low",2, Severity="Very Low",1,1==1,99) | sort + sorter | fields - sorter</query> <earliest>0</earliest> <sampleRatio>1</sampleRatio> </search> <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option> <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option> <option name="charting.axisTitleX.visibility">visible</option> <option name="charting.axisTitleY.visibility">visible</option> <option name="charting.axisTitleY2.visibility">visible</option> <option name="charting.axisX.abbreviation">none</option> <option name="charting.axisX.scale">linear</option> <option name="charting.axisY.abbreviation">none</option> <option name="charting.axisY.scale">linear</option> <option name="charting.axisY2.abbreviation">none</option> <option name="charting.axisY2.enabled">0</option> <option name="charting.axisY2.scale">inherit</option> <option name="charting.chart">column</option> <option name="charting.chart.bubbleMaximumSize">50</option> <option name="charting.chart.bubbleMinimumSize">10</option> <option name="charting.chart.bubbleSizeBy">area</option> <option name="charting.chart.nullValueMode">gaps</option> <option name="charting.chart.showDataLabels">none</option> <option name="charting.chart.sliceCollapsingThreshold">0.01</option> <option name="charting.chart.stackMode">default</option> <option name="charting.chart.style">shiny</option> <option name="charting.drilldown">none</option> <option name="charting.fieldColors">{"Very High": 0xFF0000, "High": 0xFF9900, "medium":0x0066FF, "Low":0xC4C4C0, "Very Low":0xBEEF00}</option> <option name="charting.layout.splitSeries">0</option> <option name="charting.layout.splitSeries.allowIndependentYRanges">0</option> <option name="charting.legend.labelStyle.overflowMode">ellipsisMiddle</option> <option name="charting.legend.mode">standard</option> <option name="charting.legend.placement">right</option> <option name="charting.lineWidth">2</option> <option name="trellis.enabled">0</option> <option name="trellis.scales.shared">1</option> <option name="trellis.size">medium</option> </chart> </panel> </row> <row>