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
SplunkTrust
SplunkTrust

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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...