Printer Log search to list all printers from lookup and give record count and page printed count

[ramuzzini]

Looking for help running a stats count and stats count sum referencing a lookup using print logs.  Looking to output all printers from a lookup to give "total job" count counting each record in the query for a single printer and giving a "total page" count for all pages that was printed for each printer listed in lookup.   

Logs from my index 

date                      printer_name           user            pages_printed
2024_10_09    prnt_01                        user1            10
2024_10_09    prnt_02                        user4            15
2024_10_09    prnt_01                        user6            50
2024_10_09    prnt_04                        user9            25
2024_10_09    prnt_01                        user2            20

Data from my lookup file name: printers.cvs

printer_name        printer_location
prnt_01                      main office
prnt_02                      front desk
prnt_03                      breakroom
prnt_04                      hallway

Looking for an output to give me results similar to what I provided below

Printer Name      Location            Print Jobs                Pages Printed
prnt_01                  main office       3                                   80
prnt_02                  front desk         1                                   15
prnt_03                  breakroom       0                                   25
prnt_04                  hallway              1                                   25

I have two separate queries for both respectively and having issues merging them together.  My individual queries are:

Working query that gives me job count with sum of total jobs and total pages  

index=printer sourcetype=printer:logs
| stats count sum(pages_printed) AS pages_printed by printer_name,
| lookup printers.csv printer_name AS printer_name OUTPUT printer_location
| table printer_name,  printer_location,  count,  pages_printed
| rename printer_name AS "Printer Name", printer_location AS "Location", count AS "Print Job",  pages_printed AS "Pages Printed",

Results

Printer Name      Location            Print Jobs                Pages Printed
prnt_01                  main office       3                                   80
prnt_02                  front desk         1                                   15
prnt_04                  hallway              1                                    25

Working query that gives me list of all printers and job count

index=printer sourcetype=printer:logs
| eval printer_name=lower(printer_name)
| stats count BY printer_name
| append [| inputlookup printers.csv | eval printer_name=lower(printer_name), count=0 | fields printer_name count]
| stats sum(count) AS print_jobs by printer_name
| table printer_name, total 
| rename printer_name AS "Printer Name", print_jobs AS "Print Job"

Results

Printer Name      Print Jobs                
prnt_01                 3                                  
prnt_02                 1                                   
prnt_04                 1                               

Again, trying to merge the two to give me Printer Name, Location, # of print jobs and total pages printed.  Any assistance will be greatly appreciated.

[ITWhisperer]

Splunk is not well-known for reporting on things that don't exist, you have to give it some clues!

index=printer sourcetype=printer:logs
| stats count sum(pages_printed) AS pages_printed by printer_name,
| lookup printers.csv printer_name AS printer_name OUTPUT printer_location
| table printer_name,  printer_location,  count,  pages_printed
| append
  [| inputlookup printers.cvs ``` Should this be csv? ```
  | eval count = 0, pages_printed = 0
  | table printer_name printer_location count pages_printed]
| stats sum(count) as count sum(pages_printed) as pages_printed by printer_name printer_location
| rename printer_name AS "Printer Name", printer_location AS "Location", count AS "Print Job",  pages_printed AS "Pages Printed"

[ramuzzini]

Thank you.   Appreciate your assistance and input on helping me learn the finer details of Splunk and how the logic works.   And yes, the lookup is .csv and not .cvs.  Was a type-o.  I have a sand box I work with for Splunk so manually type my searches on my work computer in the Splunk forum to help me learn the syntax better.  Old school way of understanding how to learn something, especially when it comes to code.    Thanks again.

[yuanliu]

---

Looking for an output to give me results similar to what I provided below

Printer Name      Location            Print Jobs                Pages Printed
prnt_01                  main office       3                                   80
prnt_02                  front desk         1                                   15
prnt_03                  breakroom       0                                   25
prnt_04                  hallway              1                                   25

I have two separate queries for both respectively and having issues merging them together.  My individual queries are:

Working query that gives me job count with sum of total jobs and total pages  

...

Results

Printer Name      Location            Print Jobs                Pages Printed
prnt_01                  main office       3                                   80
prnt_02                  front desk         1                                   15
prnt_04                  hallway              1                                    25

---

Is this not confusing for others?  You already get what you wanted.  What is missing here?