topic Re: Math Within addtotals in Splunk Search

Math Within addtotals

icrit — Wed, 06 Sep 2017 16:47:10 GMT

I'm trying to do some math with the values calculated in the addtotals command and put them back into the same line but I can't seem to figure out how to do that.

| eval daysEmployed = round((end - HireDate)/86400,0)
| eval daysCanWork = if(HireDate <= start, numWorkDay, round((daysEmployed/7)*5,0))
| eval posibleHours=(daysCanWork-numHolidays) * 8
| eval totWorked=Billable+NonBillable
| eval perBill=Billable/posibleHours
| eval perNonBill=NonBillable/posibleHours
| eval perWorked=totWorked / posibleHours
| eval Billable=round(Billable,2)
| eval NonBillable=round(NonBillable,2)
| eval totWorked=round(totWorked,2)
| eval perBill=round(perBill,2)*100
| eval perNonBill=round(perNonBill,2)*100
| eval perWorked=round(perWorked,2)*100
| addtotals col=true labelfield=Employee Label="Totals" posibleHours Billable NonBillable totWorked
| eval perWorked=perWorked + "%"
| eval perBill=perBill + "%"
| eval perNonBill=perNonBill + "%"
| sort -perBill
| eval HireDate=strftime(HireDate,"%m/%d/%Y")
| rename "Billing Class" as "Job Title", posibleHours as "Possible Hrs Worked", Billable as "Billable Time", NonBillable as "Non Billable time", totWorked as "Total Hrs Worked", perBill as "% Billable Time Worked", perNonBill as "% Non Billable Time Worked", perWorked as "Total % of time Worked", HireDate as "Date Hired"
| fields Employee, "Job Title", "Date Hired", "Possible Hrs Worked", "Billable Time", "Non Billable time", "Total Hrs Worked", "% Billable Time Worked", "% Non Billable Time Worked", "Total % of time Worked"

I'd like to do the following:

Billable / totworked = perBill
NonBillable / totWorked = perNonBill
possibleHours / totWorked = perWorked

If I just add the perBill,perNonBill,perWorked I just get the sums of those columns which is not what I'm trying to acomplish.

Re: Math Within addtotals

cmerriman — Wed, 06 Sep 2017 17:34:45 GMT

will the following work:

... | addtotals col=true labelfield=Employee Label="Totals" posibleHours Billable NonBillable totWorked
 |eval perBill=if(Employee="Totals",round(Billable / totworked,2)*100."%",perBill."%")
 |eval perNonBill=if(Employee="Totals",round(NonBillable / totWorked,2)*100."%",perNonBill."%")
 |eval perWorked=if(Employee="Totals",round(possibleHours / totWorked,2)*100."%",perWorked."%")
 | sort -perBill
 | eval HireDate=strftime(HireDate,"%m/%d/%Y")
 | rename "Billing Class" as "Job Title", posibleHours as "Possible Hrs Worked", Billable as "Billable Time", NonBillable as "Non Billable time", totWorked as "Total Hrs Worked", perBill as "% Billable Time Worked", perNonBill as "% Non Billable Time Worked", perWorked as "Total % of time Worked", HireDate as "Date Hired"
 | fields Employee, "Job Title", "Date Hired", "Possible Hrs Worked", "Billable Time", "Non Billable time", "Total Hrs Worked", "% Billable Time Worked", "% Non Billable Time Worked", "Total % of time Worked"

Re: Math Within addtotals

icrit — Wed, 06 Sep 2017 17:53:45 GMT

That worked flawlessly! Thank you for your help!