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"

thanks and best regards,
Sekar

PS - If this or any post helped you in any way, pls consider upvoting, thanks for reading !
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!

Enterprise Security Content Update (ESCU) | New Releases

In January, the Splunk Threat Research Team had one release of new security content via the Splunk ES Content ...

Expert Tips from Splunk Professional Services, Ensuring Compliance, and More New ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Observability Release Update: AI Assistant, AppD + Observability Cloud Integrations & ...

This month’s releases across the Splunk Observability portfolio deliver earlier detection and faster ...