topic Re: Using Eval Where Clause in Secondary Search from Stats Count in Splunk Search

Using Eval Where Clause in Secondary Search from Stats Count

ramuzzini — Fri, 18 Oct 2024 18:10:34 GMT

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.

Re: Using Eval Where Clause in Secondary Search from Stats Count

ramuzzini — Fri, 18 Oct 2024 18:39:05 GMT

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

Re: Using Eval Where Clause in Secondary Search from Stats Count

PickleRick — Fri, 18 Oct 2024 20:13:17 GMT

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