topic Re: How to display results grouping events by the field which is not part of event ? in Splunk Search

How to display results grouping events by the field which is not part of event ?

sangs8788 — Wed, 16 Aug 2017 05:54:14 GMT

I have a lookup file with Release Number and Dates of Release.

Below query lists me the count of events which occured during release.

index=app  [|inputlookup ReleaseCalender.csv |sort Release ASC| reverse |streamstats current=f last(Production) as latest|rename Production as earliest| where Release="1"| table earliest, latest| convert mktime(*) timeformat="%d/%m/%Y"| format "" "" "" "" "" ""| rex field=search mode=sed "s/\"//g"] | stats count

is it possible to group the number of events by Release field, which is not part of events at all - but has the timestart and timeend for grouping ? Something like below result

Release Count
1 100
2 50
3 200

Thanks

Re: How to display results grouping events by the field which is not part of event ?

DalJeanis — Wed, 16 Aug 2017 16:14:14 GMT

index=app  
| rename COMMENT as "for counting events by release, we only need the _time and a record type marker"
| eval rectype="detail"
| fields rectype

| rename COMMENT as "now we add the release records and sort them in front of any events on the same date"
| append [| inputlookup append=t ReleaseCalender.csv | table Release Production | rename Production as _time | eval rectype="deleteme"]
| sort 0 _time rectype

| rename COMMENT as "copy the Release onto all succeeding records until the next Release record, default nulls to zero."
| streamstats last(Release) as Release
| eval Release=coalesce(Release,"0")

| rename COMMENT as "kill the Release records then stats it up"
| search rectype="detail"
| stats count as Count by Release