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 |
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.
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.
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.
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.
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")
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 |
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")