Using Eval Where Clause in Secondary Search from Stats Count

[ramuzzini]

Have working query to give me list of all printers, total job count, total page count and show location of printers using a lookup.  Sample Data, Lookup and query is:   

Sample Data print logs from index=printer

prnt_name   jobs   pages_printed   size_paper
CS001             1          5                               letter
CS001             1         10                            11x17
CS002             1         20                            11x17
CS003             1         10                             letter
CS003             1         15                            11x17

Lookup Data from printers.csv
prnt_name   location
CS001             office               
CS002            dock               
CS003            front  

               
Splunk Query
index=printer
   | stats count sum(pages_printed) AS tot_prnt_pgs by prnt_name,
   | lookup printers.csv prnt_name AS prnt_name OUTPUT location
   | stats sum(count) AS print_jobs by prnt_name
   | table prnt_name, location, count,  tot_prnt_pgs

Splunk Query Results
prnt_name     location    count      tot_prnt_pgs 
CS001               office         2               15                          
CS002               dock           1               20                          
CS003               front           2               25       

I have been trying to use a (count (eval(if...))) clause but not sure how ot implement it or if that is the correct way to get the results I am after.  I have been using various arguments from other Splunk posts but can't seem to make it work.  Below is the output I am trying to get 

Output looking for:  "ltr" represents letter and lgl represents 11x7.  
prnt_name     location    count      tot_prnt_pgs    ltr_count    ltr_tot_pgs    lgl_count     lgl_tot pgs
CS001               office         2               15                            1                    5                         1                      10
CS002               dock           1               20                            0                    0                         1                      20
CS003               front           2                25                           1                    10                       1                      15

Appreciate any time give on this.

[ramuzzini]

After looking over my initial post, thought I would clarify a little more as to what I am after here.  I am looking to get total print jobs that are "letter", total pages printed that are "letter" and total print jobs that are "11x17" (legal), total pages printed that are "11x17" in addition to my initial working query of sum of total print jobs and total pages printed logged by a specific printer 

Thanks

[PickleRick · Accepted solution]

You overcomplicate your case.

<your initial search>

will give you a list of printer activites. As a side note you didn't take into account the fact that there is a field called count. I assume it can contain a value higher than 1. If it doesn't you can probably use count instead of sum later on.

For the naming sake, we'll overwrite the format name

| eval size_paper=if(size_paper="11x7","legal",size_paper)

Now you can use the paper format to create additional fields based on the paper size value.

| eval {size_paper}_jobs=jobs
| eval {size_paper}_pages=pages

Now you can just aggregate

| stats sum(*_jobs) as *_jobs sum(*_pages) as *_pages sum(jobs) as overall_count sum(pages) as overall_pages by prnt_name

And all that's left is enriching your results with your lookup contents

| lookup printers_csv prnt_name OUTPUT location