Stats: pulling forward data into table results

[pmhelfrich]

I am trying to create a table by counting rows, then doing a stats command on the results to determine the Avg, Max, and Min of those counts. I also want to pull 3 other values forward from the 1st stats command, that I want to remain untouched through the 2nd Stats command.

Original data:

Date Time | Filename | Transaction Count | Type
  4/13/19  |  abcde   |        5          | T1
  4/13/19  |  efghi   |        10         | T2
  4/14/19  |  jklmn   |        17         | T1
  4/14/19  |  opqrs   |        2          | T2
  4/15/19  |  tuvwx   |        20         | T2

My query:

    | bucket _time span=1d
    | stats count 
      avg(TRANS_COUNT) as AverageTransCount
      max(TRANS_COUNT) as MaxTransCount
      min(TRANS_COUNT) as MinTransCount by _time TYPE
    | table SERVICE_TYPE count AverageTransCount MaxTransCount MinTransCount
    | stats avg(count) as AverageFileCount
      max(count) as MaxFileCount
      min(count) as MinFileCount by TYPE
    | table TYPE AverageFileCount MaxFileCount MinFileCount AverageTransCount MaxTransCount MinTransCount

When I run the two stats commands separately, they work, but I can't figure out how to pull " AverageTransCount MaxTransCount MinTransCount" forward to the final results.

I'd like my results to look like so:

| SERVICE_TYPE | AverageFileCount | MaxFileCount | MinFileCount | AverageTransCount | MaxTransCount | MinTransCount |

All help is much appreciated!

[vnravikumar]

Hi

Try with eventstats

[pmhelfrich]

@vnravikumar , thanks for the response. I tried eventstats, and that does pull the data forward, but it also duplicates Type, rather than having 1 result per type.