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

[bollam]

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

[whrg]

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?

[bollam]

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

[bollam]

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

[whrg]

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

[bollam]

@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

[nagarjuna280]

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

[bollam]

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

[nagarjuna280]

use map command