Splunk Search

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

mjm295
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
1 Solution

mjm295
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

mjm295
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

0 Karma

inventsekar
Ultra Champion

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"

0 Karma

mjm295
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

mjm295
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
Get Updates on the Splunk Community!

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...