Splunk Search

How to write search with multiple BY clause in chart/stat column?

GOSWAMIGAURAV
Explorer

Hello,

I have a query which returns Planned_Sprint, Total Hours,Actual Hours,Team,Type. Now i want a stacked bar and line chart , in which the stacked bars are split by Planned_Sprint,Type and the line is only Split by Planned_Sprint.

The X-axis as Planned_Sprint, the Y as (Total Hours)stacked bar and (Actual Hours)line.

Planned_SprintTotal HoursActual HoursTypeTeam
Sp_11020AWWW
Sp_11510BDDD
Sp_1510BRRR
Sp_21015AWWW
Sp_2205ADDD
Sp_21010BTTT
Sp_258CRRR
Sp_32020BTTT

Here is my code for the chart:

| chart sum(Total Hours) AS PLANNED_Hours,sum(Actual Hours) as AC by Planned_Sprint,Type

But in this even the AC gets split by Type. I do not want this, i want something like this:

| chart sum(Total Hours) AS PLANNED_Hours by Planned_Sprint,Type, sum(Actual Hours) as AC by Planned_Sprint

Any suggestion how this can be achieved?

GOSWAMIGAURAV_0-1650450177956.png

 

Labels (3)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

 

| chart sum(Total Hours) AS PLANNED_Hours,sum(Actual Hours) as AC by Planned_Sprint,Type
| fillnull value=0
| eval AC=0
| foreach AC:*
    [| eval AC=AC+'<<FIELD>>']
| fields - AC:*

 

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

 

| chart sum(Total Hours) AS PLANNED_Hours,sum(Actual Hours) as AC by Planned_Sprint,Type
| fillnull value=0
| eval AC=0
| foreach AC:*
    [| eval AC=AC+'<<FIELD>>']
| fields - AC:*

 

0 Karma

GOSWAMIGAURAV
Explorer

@ITWhisperer I just figured, this works only if all or at least 2 "Type" are present. For the final row in my table above,where Planned Sprint = Spr_3,the Actual hours(AC) line does not come up as Spr_3 has only one Type unlike Spr_1 and Spr_2. Although there is data in Actual hours(AC) column.Any help here?

0 Karma

GOSWAMIGAURAV
Explorer

I have figured it out, the null values were causing problems. |Fornull value=0 solves it

0 Karma

isoutamo
SplunkTrust
SplunkTrust

Hi

you should do it something like

<your base search>
| stats sum('Total Hours') AS PLANNED_Hours sum('Actual Hours') as AC by Planned_Sprint

Then select visualisation as bar chart and then there is an Overlay option where you should select AC.

Unfortunately I'm not getting what you want to do with Type or is it even relevant?

r. Ismo 

0 Karma

GOSWAMIGAURAV
Explorer

This will not work..The total hours are split by both Planned_Sprint and "Type" for each team.So the x axis becomes the Planned_Sprint  and the y axis has two things: the sum of "Total Hours" split across various "Types" as stacked bars and the actual Hours Line graph should be just sum(Actual Hours ) split across Planned_Sprint

0 Karma

Software-Simian
Path Finder

Hi Iso,

i just assumed that he wants to do:

Total Hours in case Type is A

Actual Hours in Case Type is B

 

basically he tried using by as a filter...i think

0 Karma

GOSWAMIGAURAV
Explorer

Hello,

I have edited my question with the sample table and expected chart

0 Karma

Software-Simian
Path Finder

Hi,

 

the easiest way would be an append...i am sure that there are multiple solutions out there to get this in one Go....but why bother if it can be achieved by a simple "append"

 

Regards

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...