Adding values from multiple arrays

shonac — Thu, 05 Nov 2020 12:37:29 GMT

Hi,

I am trying to add the values from 2 array functions to get the overall sum.

| eval {1_month_last_day_prior} = case((StartDateEpoch < '1_month_last_day_prior_epoch') AND (StartDateEpoch > '2_month_last_day_prior_epoch'), additionalBillingRate2, StartDateEpoch > '1_month_last_day_prior_epoch', "0",1=1,MRR) | eval {2_month_last_day_prior} = case((StartDateEpoch < '2_month_last_day_prior_epoch') AND (StartDateEpoch > '3_month_last_day_prior_epoch'), additionalBillingRate2, StartDateEpoch > '2_month_last_day_prior_epoch', "0",1=1,MRR)

The below is not working and im not sure what syntax I should be using:

| eval sum='{1_month_last_day_prior}' + '{2_month_last_day_prior}'

Any ideas?

Re: Adding values from multiple arrays

renjith_nair — Thu, 05 Nov 2020 13:50:53 GMT

1_month_last_day_prior,2_month_last_day_prior - are these existing field values (e.g. | eval {aName}=aValue ) or a new variable?

If it's a new variable, you dont need the { } brackets around them

If it's an existing field value as mentioned in Eval Field_names , then it will not work in this way

Re: Adding values from multiple arrays

shonac — Thu, 05 Nov 2020 14:03:42 GMT

Hi @renjith_nair ,

These are existing fields I have created here:

| eval 1_month_last_day_prior=strftime(relative_time('billDateEpoch' ,"@mon-1d"), "%Y-%m-%d") | eval 2_month_last_day_prior=strftime(relative_time('billDateEpoch' ,"-1mon@mon-1d"), "%Y-%m-%d")

Is there any other way I could add the values?

This is how the results look currently, trying to add the 3 values.

Re: Adding values from multiple arrays

renjith_nair — Thu, 05 Nov 2020 14:12:09 GMT

If you just want to add those row values and create a new field , just add this

| addtotals row=t

or just lets know the expected output.

Re: Adding values from multiple arrays

shonac — Thu, 05 Nov 2020 15:03:09 GMT

The issue with this is I have other values in each row, but I only want to sum the first 3 dates:

Re: Adding values from multiple arrays

renjith_nair — Thu, 05 Nov 2020 15:15:51 GMT

Is it always the first 3 dates ? You may use foreach with some conditions.

e.g.

your search|eval total=0,count=0 |foreach 20* [eval total=if(count < 3,total+<<FIELD>>,total+0)|eval count=count+1]

Is it possible to share your current search , you may mask sensitive information if any

Re: Adding values from multiple arrays

shonac — Thu, 05 Nov 2020 17:07:32 GMT

Hi @renjith_nair ,

It will always be the first 3 dates I need to add as these are for the prior period (PPadjustment).
This is my full search, apologies it is extremely long.

| inputcsv gen_billable_conns.csv | search Client="*" | lookup gen_billing_discount_rates.csv ClientName as Client OUTPUT Premium, Discount | fillnull value=1 Premium, Discount | eval StartDateEpoch=strptime('Client Handover Date', "%Y-%m-%d"), DecomDateEpoch=strptime('Decommission Date', "%Y-%m-%d") | eval StartYear=strftime(StartDateEpoch, "%Y"), StartMonth=strftime(StartDateEpoch, "%b"), StartDay=strftime(StartDateEpoch, "%d"), DecomYear=strftime(DecomDateEpoch, "%Y"), DecomMonth=strftime(DecomDateEpoch, "%b"), DecomDay=strftime(DecomDateEpoch,"%d") | eval billingDate="01-10-2020", billDateEpoch=strptime(billingDate, "%d-%m-%Y") | eval jiraMRR=MRR | eval 1_month_last_day=strftime(relative_time('billDateEpoch' ,"@mon-1d"), "%d") | eval 2_month_last_day=strftime(relative_time('billDateEpoch' ,"-1mon@mon-1d"), "%d") | eval 3_month_last_day=strftime(relative_time('billDateEpoch' ,"-2mon@mon-1d"), "%d") | eval totalDaysInPreviousQuarter='1_month_last_day'+'2_month_last_day'+'3_month_last_day' | eval daysBetweenHandoverAndBill=round((billDateEpoch-StartDateEpoch)/60/60/24, 0), daysBetweenDecomAndBill=round((billDateEpoch-DecomDateEpoch)/60/60/24, 0) | eval adjustedMRR=if(daysBetweenHandoverAndBill<=365, MRR*Premium, MRR*Discount) | eval adjustedBillingRate=exact(adjustedMRR*3) | eval requiresAdjustedMRR=if('Deviation from Base MRR'="Yes", "Yes", "No") | eval MRR=if(requiresAdjustedMRR="Yes", adjustedMRR, MRR) | eval DRR=(MRR*3/totalDaysInPreviousQuarter) | eval normalBillingRate=exact(MRR*3) | eval additionalBillingRate=exact((DRR*daysBetweenHandoverAndBill)+NRR) | eval billingCredit=exact(DRR*daysBetweenDecomAndBill) | eval requiresAdditionalBillingRate=if(daysBetweenHandoverAndBill <= totalDaysInPreviousQuarter, "Yes", "No") | eval requiresBillingCredit=if(daysBetweenDecomAndBill <= totalDaysInPreviousQuarter, "Yes", "No") | eval billingCharge=if(requiresAdditionalBillingRate="Yes", (normalBillingRate+additionalBillingRate), normalBillingRate) | eval billingCharge=if(requiresAdjustedMRR="Yes" AND requiresAdditionalBillingRate="Yes", (adjustedBillingRate+additionalBillingRate), 'billingCharge') | eval billingCharge=if(billingCredit>0, 0,'billingCharge') | fillnull value=0 billingCredit | eval QCharge=billingCharge-billingCredit | eval NRR=if(requiresAdditionalBillingRate="Yes", NRR, 0) | rename billingCharge as quarterlyCharge, billingCredit as quarterlyCredit, QCharge as totalCost | eval 1_month_last_day_prior=strftime(relative_time('billDateEpoch' ,"@mon-1d"), "%Y-%m-%d") | eval 2_month_last_day_prior=strftime(relative_time('billDateEpoch' ,"-1mon@mon-1d"), "%Y-%m-%d") | eval 3_month_last_day_prior=strftime(relative_time('billDateEpoch' ,"-2mon@mon-1d"), "%Y-%m-%d") | eval 1_month_last_day_current=strftime(relative_time(billDateEpoch,"+1mon@mon-1d"), "%Y-%m-%d") | eval 2_month_last_day_current=strftime(relative_time(billDateEpoch,"+2mon@mon-1d"), "%Y-%m-%d") | eval 3_month_last_day_current=strftime(relative_time(billDateEpoch,"+3mon@mon-1d"), "%Y-%m-%d") | eval 1_month_last_day_prior_epoch=relative_time('billDateEpoch' ,"@mon-1d") | eval 2_month_last_day_prior_epoch=relative_time('billDateEpoch' ,"-1mon@mon-1d") | eval 3_month_last_day_prior_epoch=relative_time('billDateEpoch' ,"-2mon@mon-1d") | eval 4_month_last_day_prior_epoch=relative_time('billDateEpoch' ,"-3mon@mon-1d") | eval 1_month_last_day_current_epoch=relative_time(billDateEpoch,"+1mon@mon-1d") | eval 2_month_last_day_current_epoch=relative_time(billDateEpoch,"+2mon@mon-1d") | eval 3_month_last_day_current_epoch=relative_time(billDateEpoch,"+3mon@mon-1d") | eval 4_month_last_day_current_epoch=relative_time(billDateEpoch,"+4mon@mon-1d") | eval start_month_last_day_epoch=relative_time(StartDateEpoch,"+1mon@mon-1d") | eval decom_month_last_day_epoch=relative_time(StartDateEpoch,"+1mon@mon-1d") | eval daysBetweenHandoverAndEOM=round(('start_month_last_day_epoch'-StartDateEpoch)/60/60/24, 0), daysBetweenDecomAndEOM=round(('decom_month_last_day_epoch'-DecomDateEpoch)/60/60/24, 0) | eval additionalMonthlyRate=round((DRR*daysBetweenHandoverAndEOM),2), creditMonthlyRate=round((DRR*daysBetweenDecomAndEOM),2) | eval {1_month_last_day_prior} = case((StartDateEpoch < '1_month_last_day_prior_epoch') AND (StartDateEpoch > '2_month_last_day_prior_epoch'), additionalMonthlyRate, StartDateEpoch > '1_month_last_day_prior_epoch', "0",1=1,MRR) | eval {2_month_last_day_prior} = case((StartDateEpoch < '2_month_last_day_prior_epoch') AND (StartDateEpoch > '3_month_last_day_prior_epoch'), additionalMonthlyRate, StartDateEpoch > '2_month_last_day_prior_epoch', "0",1=1,MRR) | eval {3_month_last_day_prior} = case((StartDateEpoch < '3_month_last_day_prior_epoch') AND (StartDateEpoch > '4_month_last_day_prior_epoch'), additionalMonthlyRate, StartDateEpoch > '3_month_last_day_prior_epoch', "0",1=1,MRR) | eval {1_month_last_day_current} = case((StartDateEpoch < '1_month_last_day_current_epoch') AND (StartDateEpoch > '1_month_last_day_prior_epoch'), additionalMonthlyRate, StartDateEpoch > '1_month_last_day_current_epoch', "0",1=1,MRR) | eval {2_month_last_day_current} = case((StartDateEpoch < '2_month_last_day_current_epoch') AND (StartDateEpoch > '1_month_last_day_current_epoch'), additionalMonthlyRate,StartDateEpoch > '2_month_last_day_current_epoch', "0",1=1,MRR) | eval {3_month_last_day_current} = case((StartDateEpoch < '3_month_last_day_current_epoch') AND (StartDateEpoch > '2_month_last_day_current_epoch'), additionalMonthlyRate,StartDateEpoch > '3_month_last_day_current_epoch', "0",1=1,MRR) | table "Circuit ID", Key, Summary, "Billing Notes", NRR, MRR, "Client Handover Date", "Decommission Date", quarterlyCharge, quarterlyCredit, totalCost, Currency, PPadjustment, 20*

topic Re: Adding values from multiple arrays in Splunk Search

Adding values from multiple arrays

Re: Adding values from multiple arrays

Re: Adding values from multiple arrays

Re: Adding values from multiple arrays

Re: Adding values from multiple arrays

Re: Adding values from multiple arrays

Re: Adding values from multiple arrays