How search the latest timestamp of each event type in our data?

[MikeBertelsen]

We run a query that produces a count of each event type, but we also want to know when was the last time the event ran.
Here is the query:
index=lisa_vse| top limit=100 serviceName | fields serviceName count serviceTimeStamp

The results are:

serviceName	count	serviceTimeStamp
trn_RiskReportsService	32208
trn_ProductPortfolioService	16501
engr_RiskReportsService	12790
RatingSymbolService_PC	10751
trn_GeoCoderService	9856
trn_RatingService	8777
RiskReportsService_GUnit	7308
AutoRateInfoService_PC	6851
trn_AutoRateInfoService	6315
trn_PolicyTransitionService	4532

[aweitzman · Accepted solution]

I think you want to structure the search a little differently:

index=lisa_vse | stats count, first(_time) as serviceTimeStamp by serviceName | sort -count | head 100 | fieldformat serviceTimeStamp = strftime('serviceTimeStamp', "%c")

(edited to correct typo)

You can keep track of the latest time and the count simultaneously by using the stats command instead of top. Then sort by the count descending, and just take the first 100.

[MikeBertelsen]

My partner figured it out. Here is what he came up with:
index=lisa_vse source=/opt/lisavse/VSEMetrics/Logs/VSE_CallsMade.* | stats count, first(serviceTimeStamp) as lastExecution by serviceName | sort -count
It works but I like the format of aweitzman's query better. Thanks for the help.

[aweitzman]

Oh, I see the second typo in my answer, the extra space in the field name taken by strftime! If you take that out, it ought to work.

[aweitzman]

I'm confused.

Except for the typo in my answer (index-lisa_vse should be index=lisa_vse, which you seem to have figured out), your search is exactly the same as my suggestion, except for the final clause.

Where does maxTimeStamp come from? serviceTimeStamp already contains the epoch time value you want - the fieldformat clause just makes it easier to read. If you want to create a new field maxTimeStamp for that instead of overwriting the existing serviceTimeStamp, you need to write your final clause like this:

fieldformat maxTimeStamp=strftime('serviceTimeStamp',"%c")

[MikeBertelsen]

Which provided the following output:

serviceName	count	serviceTimeStamp	maxTimeStamp
RatingSymbolService_PC	7329	1410212566
RiskReportsService_GUnit	7308	1410212831
trn_RiskReportsService	6677	1410212851
AutoRateInfoService_PC	5152	1410212857
engr_RiskReportsService	3784	1409937368
RatingService_PC	2580	1410212832

closer but not there

[MikeBertelsen]

It made no difference in the output.

I should have included the following info.
There are two Field Extractions defined:

1. VSE_CallsMade : EXTRACT-serviceName Inline (?i)^(?P[^,]+)

2. VSE_CallsMade : EXTRACT-serviceTimeStamp Inline (?i)^(?:[^,]*,){2}(?P.+)



So I made a few changes to the offered solution:
index=lisa_vse | stats count, first(_time) as serviceTimeStamp by serviceName | sort -count | head 100 | fieldformat maxTimeStamp =strftime('maxTimeStamp ', "%c")