Splunk Search
Highlighted

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

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(starttime) as starttime 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
Highlighted

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

Motivator

Just to clarify: Let us assume your events are (please add any missing fields):
job,starttime,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
Highlighted

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

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 pctvalue
job B, 6am, 10 pct
value

0 Karma
Highlighted

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

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 starttime
[ search index=main
| search trainId="somevalue"
| stats earliest(start
time) as starttime by job
| table start
time ]
| stats first(cpu) as totalcpu sum(cpu) as MB by starttime, job
| eval pct = round((MB/totalcpu)*100, 2)
| table start
time pct job

0 Karma
Highlighted

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

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 totalcpu sum(cpu) as MB by job
| eval pct = round((MB/total
cpu)*100, 2)
| table pct job

0 Karma
Highlighted

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

Path Finder

index=main
| search trainId="somevalue"
| stats earliest(starttime) as starttime 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 totalcpu sum(cpu) as MB by starttime, job
| eval pct = round((MB/totalcpu)*100, 2)
| table start
time pct job

0 Karma
Highlighted

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

Communicator

use map command

0 Karma
Highlighted

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

Motivator

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

0 Karma
Highlighted

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

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(starttime) as starttime by job
| table starttime ]
| stats first(cpu) as total
cpu sum(cpu) as MB by starttime, job
| eval pct = round((MB/total
cpu)*100, 2)
| table start_time pct job

0 Karma