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 q...
See more...
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.