Extract field from group with minimum timestamp

[agh]

I have a query like this where i group by REQUEST_ID

eventtype=sfdc-event-log EVENT_TYPE="ApexTrigger" REQUEST_ID!="" | stats sum(EXEC_TIME) as e1, min(TIMESTAMP_DERIVED) as e2 by REQUEST_ID | eval e1=e1/1000 | sort -e1

I would like to add a new field in this output called TRIGGER_TYPE and display only that trigger_type from each group which has the minimum TIMESTAMP_DERIVED field (e2). (Note that TIMESTAMP_DERIVED is my custom timestamp field)

I see I can get a list of all the trigger types in each group with list(TRIGGER_TYPE) but i only want the TRIGGER_TYPE which has a specific value for the TIMESTAMP_DERIVED field.

Any ideas on how this can be achieved?

[manjunathmeti]

hi @agh,
Try this:

eventtype=sfdc-event-log EVENT_TYPE="ApexTrigger" REQUEST_ID!="" | eventstats min(TIMESTAMP_DERIVED) as TIMESTAMP_DERIVED_min by REQUEST_ID | eval trigger_type_min=if(TIMESTAMP_DERIVED=TIMESTAMP_DERIVED_min, TRIGGER_TYPE, "") | stats sum(EXEC_TIME) as e1, min(TIMESTAMP_DERIVED) as e2, max(trigger_type_min) as TRIGGER_TYPE by REQUEST_ID | eval e1=e1/1000 | sort -e1

If this reply helps you, an upvote/like would be appreciated.

[agh]

this doesnt seem to give correct output, and also takes a lot of time to process (5+ min)

[aasabatini]

Hi

after stats comand please create your triggered_time field with eval:

| eval TRIGGER_TYPE=if[please here create your condition with e2 fields]

https://splunkonbigdata.com/2018/08/26/usage-of-splunk-eval-function-if/

after this order your output with  a table comand or other stats comand

[agh]

I tried this 

eventtype=sfdc-event-log EVENT_TYPE="ApexTrigger" REQUEST_ID!="" | stats sum(EXEC_TIME) as e1, min(TIMESTAMP_DERIVED) as e2 by REQUEST_ID | eval a1=if(TIMESTAMP_DERIVED==e2, TRIGGER_TYPE, "not_first") | eval e1=e1/1000 | sort -e1

trying to output TRIGGER_TYPE field, but a1 is always "not_first". I tried changing the true false outputs, but looks like the field TRIGGER_TYPE is not outputted