Splunk Search

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

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

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

View solution in original post

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

View solution in original post

Path Finder

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

0 Karma