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

[jalau9]

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!

[chris · Accepted solution]

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]

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!