How do I add an Average column to this stats table?

[mjm295]

I have this query to create a stats table:

index=star_aws sourcetype=aws:ec2 State=running | dedup InstanceID | rename InstanceSize as Instance_Type 
| eval Operating_System = case(match(OS,"RHE.+"), "RHEL", match(OS,"win.+"), "Windows", match(OS,"Win.+"), "Windows", 1=1, "Linux")  
| fields InstanceID Instance_Type OS Operating_System Service StarName     
| join type=left max=1 Instance_Type Operating_System [ | inputlookup aws_price.csv ] 
| eval PricePerYr=round(PricePerYr,2) 
| eval totalPerYr=(PricePerYr * 1.5)
| stats sum(PricePerYr) as "Compute  OPEX (US$/yr)", sum(totalPerYr) as "Total OPEX inc Ancillaries(storage, backup etc)", count as "Instance Count", by Service 

Stats table output is in the attached screen shot

How can I add a 5th column showing Average total cost per instance?
IE Column 4 divided by column 5?
Instances are different sizes/costs

Thanks
Mark

[mjm295 · Accepted solution]

AH nailed it

 | stats sum(PricePerYr) as "Compute  OPEX (US$/yr)", sum(totalPerYr) as "Total OPEX", count as "Instance Count" by Service | eval Avg_total_cost_per_instance=('Total OPEX' / 'Instance Count' )

Simple

[inventsekar]

not sure, but please check this -

index=star_aws sourcetype=aws:ec2 State=running | dedup InstanceID | rename InstanceSize as Instance_Type 

 | eval Operating_System = case(match(OS,"RHE.+"), "RHEL", match(OS,"win.+"), "Windows", match(OS,"Win.+"), "Windows", 1=1, "Linux")


 | fields InstanceID Instance_Type OS Operating_System Service StarName


 | join type=left max=1 Instance_Type Operating_System [ | inputlookup aws_price.csv ] 

 | eval PricePerYr=round(PricePerYr,2) 

 | eval totalPerYr=(PricePerYr * 1.5)

 | stats sum(PricePerYr) as "Compute  OPEX (US$/yr)", sum(totalPerYr) as "Total OPEX inc Ancillaries(storage, backup etc)", count as "Instance Count", by Service,  avg("Total OPEX inc Ancillaries(storage, backup etc)") as Avg_total_cost_per_instance by "Instance Count" 

[mjm295]

Thanks, i've been trying similar - and we are getting:

Error in 'stats' command: The output field 'Instance Count' cannot have the same name as a group-by field.

[mjm295]

his was my thoughs:

 | stats sum(PricePerYr) as "Compute  OPEX (US$/yr)", sum(totalPerYr) as "Total OPEX inc Ancillaries(storage, backup etc)", count as "Instance Count",  eval Avg_total_cost_per_instance=("Total OPEX inc Ancillaries(storage, backup etc)" / "Instance Count" ) as Avg_total_cost_per_instance by Service