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]

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 

[karu0711]

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

[ITWhisperer]

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

[karu0711]

 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

[richgalloway]

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

index="veracode_test" sourcetype="veracode" 
| lookup Veracode.csv Find_severity | stats count by Severity 
| append [| inputlookup veracode.csv | fields Severity | eval count = 0 ]
stats sum(count) by Severity

[karu0711]

This what I got

[ITWhisperer]

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.

[karu0711]

| inputlookup Veracode.csv | fields Severity | eval count = 0
| join type=left Severity
[|search index="veracode" sourcetype="Veracode" | lookup Veracode.csv find.severity | stats count by Severity]

I was able to do with join command 

Thanks

[somesoni2 · Accepted solution]

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):

index="veracode" sourcetype="Veracode" | lookup Veracode.csv find.severity | stats count by Severity
| append [| inputlookup Veracode.csv | stats count by Severity | eval count = 0]
| stats max(count) as count by Severity

[karu0711]

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]?

[richgalloway]

Create a separate field for sorting.

index="veracode" sourcetype="Veracode" | lookup Veracode.csv find.severity | stats count by Severity
| append [| inputlookup Veracode.csv | stats count by Severity | eval count = 0]
| stats max(count) as count by Severity
| eval sorter = case(Severity="very high", 1, Severity="high", 2, Severity="medium", 3, Severity="Low", 4, Severity="Very Low", 5, 1==1, 99)
| sort + sorter
| fields - sorter

[karu0711]

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.

[richgalloway]

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.

[karu0711]

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>

[karu0711]

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

[richgalloway]

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

[karu0711]

Thanks for the quick reply

[ITWhisperer]

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

[karu0711]

Thanks for quick reply.