topic Re: Create a stacked bar chart, conflating values from multiple fields in Dashboards & Visualizations

Create a stacked bar chart, conflating values from multiple fields

sjb300 — Wed, 08 Nov 2017 09:50:20 GMT

Each event in my data set falls into one of two categories:
1) Has a field called "os_platform" and a field called "parameters.From"
2) Has a field called "os" and a field called "params.from"

I would like to generate a stacked bar chart where there is one bar per value of either os or os_platform (whichever is present for each event), and where each bar is split into a segment for each value of parameters.From or params.from (whichever is present for each event).

What would a query look like which does this?

This data...

...should produce a chart that looks like this...

Re: Create a stacked bar chart, conflating values from multiple fields

niketn — Wed, 08 Nov 2017 15:32:43 GMT

@sjb300, can you add a mock screenshot of what you need and some sample data for 4 fields as to how they would appear in event?

Re: Create a stacked bar chart, conflating values from multiple fields

sjb300 — Wed, 08 Nov 2017 15:48:41 GMT

@niketnilay Done

Re: Create a stacked bar chart, conflating values from multiple fields

cmerriman — Wed, 08 Nov 2017 17:10:47 GMT

you'll need something like

|eval os_type=coalesce(os,os_platform)
|eval param=coalesce('parameters.From','params.from')
|chart count by param os_type

Re: Create a stacked bar chart, conflating values from multiple fields

sjb300 — Wed, 08 Nov 2017 17:19:19 GMT

Using that example, if I replace line 3 with "chart count by os_type" then it produces a bar chart by OS. If I do "chart count by param" instead, it says "No results found", even though I have not added anything that should filter the results. What's going on?

Re: Create a stacked bar chart, conflating values from multiple fields

sjb300 — Wed, 08 Nov 2017 17:23:32 GMT

Can this be because parameters and params are arrays?

Re: Create a stacked bar chart, conflating values from multiple fields

sjb300 — Wed, 08 Nov 2017 18:02:24 GMT

Found something that works, although somewhat inelegant:

| spath output=from1 path=params.From
| spath output=from2 path=parameters.from
| eval from=coalesce(from1,from2)
| eval os=coalesce(os,os_platform)
| chart count by os, from

Re: Create a stacked bar chart, conflating values from multiple fields

cmerriman — Wed, 08 Nov 2017 18:15:51 GMT

try using either |eval param=coalesce("parameters.From","params.from") or |eval param=coalesce(parameters.From,params.from)
Splunk can be picky about field names with . and spaces. See if param is a field now.

Re: Create a stacked bar chart, conflating values from multiple fields

niketn — Wed, 08 Nov 2017 18:45:59 GMT

@cmerriman, your first query for coalesce() with single quotes for field name is correct. While creating the chart you should have mentioned |chart count over os_type by param. Please correct the same it should work.

@sjb300 please try out the following run anywhere search with sample data from the question. This is on similar lines as Clara, however, while performing coalesce(), it reuses one of the existing fields instead of creating new ones. Also fields - is added to remove other fields after coalesce().

|  makeresults
|  eval "Event id"=1,"parameters.From"="A","os_platform"="x86"
|  append 
    [|  makeresults
     |  eval "Event id"=2,"parameters.From"="A","os_platform"="x86"]
|  append 
    [|  makeresults
     |  eval "Event id"=3,"params.from"="B","os"="Android"]
|  append 
    [|  makeresults
     |  eval "Event id"=4,"params.from"="B","os"="iOS"]
|  append 
    [|  makeresults
     |  eval "Event id"=5,"params.from"="A","os"="iOS"]
|  append 
    [|  makeresults
     |  eval "Event id"=5,"params.from"="A","os"="iOS"]
| table "Event id" "parameters.From" "params.from" "os_platform" "os"
| eval params.from=coalesce('parameters.From','params.from')
| eval os=coalesce('os_platform','os')
| fields - "parameters.From" "os_platform"
| chart count over os by "params.from"

Re: Create a stacked bar chart, conflating values from multiple fields

cmerriman — Wed, 08 Nov 2017 19:40:54 GMT

the chart command does not need over <<FIELD1>> by <<FIELD2>> to work. it understand that by <<FIELD1>> <<FIELD2>> is theoverandby` automatically.

|makeresults|eval data="id=1,from=A,os_platform=x86 id=2,from=A,os_platform=x86 id=3,from1=B,os=Android id=4,from1=B,os=iOS id=5,from1=A,os=iOS id=6,from1=A,os=iOS"|makemv data|mvexpand data|rename data as _raw |kv|rename from as "parameters.From" from1 as "params.from"
|eval os_type=coalesce(os,os_platform)
 |eval param=coalesce('parameters.From','params.from')
 |chart count by os_type param

Re: Create a stacked bar chart, conflating values from multiple fields

niketn — Wed, 08 Nov 2017 19:44:54 GMT

@cmerriman, yes but your query should work. It worked for me as well. Could it be that field names parameters.From and params.from are something else in raw data?

@sjb300, can you check if the following works

<YourBaseSearch>
| table "parameters.From" "params.from"