Splunk Search

Why stats count does not return results after filtering indexed data with lookup?

jalau9
Explorer

I have a lookup (person, manager) that looks like this (lookup flatorg):
1,2
fk,ry
op,ry

and a sourcetype that looks like this (index=*):
fk,3,a
fk,6,b
op,1,c
op,5,d

I want to give the lookup one person, and find the manager, and from the manager, find all the persons under him, then sum by persons. These are the searches I tried:
1. search index=* | append [ inputlookup flatorg | search "1"="op" | dedup "2" | fields "2" | lookup flatorg "2" OUTPUT "1" AS person ] | mvexpand person | stats count(field) by person
which returns me
op 1 (expected 6)
fk 1 (expected 9)

Many variations of these have been done, they can return me all persons under "ry" but when I try to stats count by person, I don't get the result that I want.

I want the lookup to produce results similar to:
index=* person="op" OR person="fk" | stats count(field) by person

Many thanks for your help!

Tags (3)
0 Karma
1 Solution

chris
Motivator

I'm not sure I understand what you are trying to do but this might help:

index=* | lookup flatorg "1" as person OUTPUT "2" as manager | search [inputlookup flatorg | search "1"=op | fields "2" | rename "2" as manager ] | stats count sum(field) values(field) by person

I'm assuming that the fields in your sourcetype are person (values are fk and op) and field (values are 3,6,1,5) the third field is not used.

Let me know if this helps.

View solution in original post

chris
Motivator

I'm not sure I understand what you are trying to do but this might help:

index=* | lookup flatorg "1" as person OUTPUT "2" as manager | search [inputlookup flatorg | search "1"=op | fields "2" | rename "2" as manager ] | stats count sum(field) values(field) by person

I'm assuming that the fields in your sourcetype are person (values are fk and op) and field (values are 3,6,1,5) the third field is not used.

Let me know if this helps.

jalau9
Explorer

Holy cow, yes you got it working! Couldn't imagine stats count sum values existed as one command. Splunk and I just don't get along. Thank you again!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...