Splunk Search

Why am I not getting expected results using the chart command with the syntax "chart sum(fieldA) over FieldB by index"?

nekbote
Path Finder

Hi All,

I am having issue with the search below. Hope you can point out where i am going wrong.

index=index1 OR index=index2 OR index = index3  1234 OR 12345 OR 123456
| fields ORDERDATE,ORDERDATE,ORDERCREATEDATE,MERCHLINETOT,MERCHLINETOT,MERCHLINETOT,index
| eval fldNow=now() 
| eval ORDERCREATEDATE1=substr(ORDERCREATEDATE,7,10) 
| eval age=((ORDERCREATEDATE1-fldNow)/60)/60/24
| eval orderlinedate1=strptime(substr(ORDERDATE,1,8), "%Y%m%d") 
| eval age1=((orderlinedate1-fldNow)/60)/60/24
| eval orderlinedate2=strptime(substr(ORDERDATE,1,8), "%Y%m%d") 
| eval age2=((orderlinedate2-fldNow)/60)/60/24
| where (age <0 OR age1 <0  OR age2 <0 )
| eval age_group=case(age>-8 AND age<0,"Less than 7 days late",age>-15 AND age<-7," 7 and 14 days late",age>-22 AND age<-14,"between 14 and 21 days late",age<-21 ,"more than 21 days late")
| eval age_group1=case(age1>-8 AND age1<0,"Less than 7 days late",age1>-15 AND age1<-7," 7 and 14 days late",age1>-22 AND age1<-14," between 14 and 21 days late",age1<-21 ,"more than 21 days late")
| eval age_group2=case(age2>-8 AND age2<0,"Less than 7 days late",age2>-15 AND age2<-7,"7 and 14 days late",age2>-22 AND age2<-14," between 14 and 21 days late",age2<-21 ,"more than 21 days late")
| eval grouped_fields=coalesce(MERCHLINETOT,MERCHLINETOT,MERCHLINETOT)
| chart sum(MERCHLINETOT) over age_group2 by index 
| rename index1 as "StaleA", index2 as "StaleB", index3  as "StaleC"

age= AGE OF 1234 = -1.5
age1=AGE OF 12345 = -2.5
age2=AGE OF 123456 = -3.5

ALL THE ABOVE AGES fall under "Less than 7 days late" age_group category.

Problem:

When I use:

 chart sum(MERCHLINETOT) over age_group2 by index

or

chart sum(MERCHLINETOT) over age_group1 by index 

I see the result only for StaleA and StaleB

age_group1            StaleA   StaleB
Less than 7 days late   79.70   95.92

When I use:

 chart sum(MERCHLINETOT) over age_group by index 

I see the result only for StaleC

age_group               StaleC  
Less than 7 days late   99.70

Why am i not seeing the results for all 3 [StaleA , StaleB , Stale C] ? What am i missing? Any guidance is really appreciated.

Thank you.

Expected result

age_group               StaleA   StaleB   StaleC
Less than 7 days late    79.70  95.92   99.70

Kindly let me know if you need more information.

Tags (1)
0 Karma
1 Solution

stephane_cyrill
Builder

Hi nekbote,
your are using many sources of data ( many index) .
Try to COALESCE all the values of age_group1,age_group,........... in one global_age_group and do the chart sum(...) over global_age_group

View solution in original post

stephane_cyrill
Builder

Hi nekbote,
your are using many sources of data ( many index) .
Try to COALESCE all the values of age_group1,age_group,........... in one global_age_group and do the chart sum(...) over global_age_group

nekbote
Path Finder

Thanks alot Stephane....that solved my problem....!!!

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 ...