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