Splunk Search

How to aggregate the results based on the results of the transforming command?

bollam
Path Finder

I'm trying to calculate the percentage of resources that are consumed by a job based on the start time of the job.
Each job has more than one event and startTime could vary in the events. So I'm considering the earliest start time of the job.
Using eventstats is not producing the events which im looking at.

stats earliest(start_time) as start_time by job, If this is replaced in the place of eventstats im getting the results what I want.

Can someone assist me on this?

index=main 
| search trainId=acggalladks"
| eventstats earliest(start_time) as start_time by job
| stats first(cpu) as total_cpu sum(cpu) as MB by start_time, job
| eval pct = round((MB/total_cpu)*100, 2)
| table pct job
Tags (1)
0 Karma

whrg
Motivator

Just to clarify: Let us assume your events are (please add any missing fields):
job,start_time,cpu
Job A, 6am, 25
Job A, 7am, 35
Job A, 8am, 30
Job B, 8am, 10
Then what output do you want from the search?
And how do you calculate total_cpu?

0 Karma

bollam
Path Finder

The total_cpu is the common for all the events.

I'm adding another row for job B which is job B 6am
I want output to be as below.
Job A, 6am, 25 pct_value
job B, 6am, 10 pct_value

0 Karma

bollam
Path Finder

I tried this query and it's giving me the output what im looking at. But is it possible to get the output without join command?

index=main
| search trainId="somevalue"
| join start_time
[ search index=main
| search trainId="somevalue"
| stats earliest(start_time) as start_time by job
| table start_time ]
| stats first(cpu) as total_cpu sum(cpu) as MB by start_time, job
| eval pct = round((MB/total_cpu)*100, 2)
| table start_time pct job

0 Karma

whrg
Motivator

How about:
index=main
| ...
| stats earliest(cpu) as total_cpu earliest(start_time) as start_time sum(cpu) as MB by job
| eval pct = ...

0 Karma

bollam
Path Finder

@whrg, That way it won't work because i want the values of the start_time to be used in the by clause.
I got it solved without join but looking for an optimal way.

index=main
| search trainId="somevalue"
[ search index=main
| search trainId="somevalue"
| stats earliest(start_time) as start_time by job
| table start_time ]
| stats first(cpu) as total_cpu sum(cpu) as MB by start_time, job
| eval pct = round((MB/total_cpu)*100, 2)
| table start_time pct job

0 Karma

nagarjuna280
Communicator

remove join and see you get the same results (why you are using join),

try this, I think you are expecting below the search
index=main
| search trainId=acggalladks"
| stats earliest(cpu) as total_cpu sum(cpu) as MB by job
| eval pct = round((MB/total_cpu)*100, 2)
| table pct job

0 Karma

bollam
Path Finder

index=main
| search trainId="somevalue"
| stats earliest(start_time) as start_time by job

Whatever the output that returns by the field start_time from the above search need to be used in the by clause for the below query.

index=main
| search trainId="somevalue"
stats first(cpu) as total_cpu sum(cpu) as MB by start_time, job
| eval pct = round((MB/total_cpu)*100, 2)
| table start_time pct job

0 Karma

nagarjuna280
Communicator

use map command

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...