Splunk Search
Highlighted

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

Path Finder

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 shotalt text

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

0 Karma
Highlighted

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

Champion

not sure, but please check this -

index=staraws sourcetype=aws:ec2 State=running | dedup InstanceID | rename InstanceSize as InstanceType 
| eval OperatingSystem = case(match(OS,"RHE.+"), "RHEL", match(OS,"win.+"), "Windows", match(OS,"Win.+"), "Windows", 1=1, "Linux")

| fields InstanceID Instance
Type OS OperatingSystem Service StarName

| join type=left max=1 Instance
Type OperatingSystem [ | inputlookup awsprice.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 Avgtotalcostperinstance by "Instance Count"

0 Karma
Highlighted

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

Path Finder

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.
0 Karma
Highlighted

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

Path Finder

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 
0 Karma
Highlighted

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

Path Finder

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

View solution in original post

0 Karma