values are missing pot lookup

[abdul]

Not able to find the stats details for all M.  tried fill null . it is not working 

index=UA sourcetype=apps appname="xyz*"
|fields EID
|dedup EID
|lookup employee.csv EID as EID
|search MID in (M1,M2,M3 M4,M5,M6)
|stats count(EID) as total by MID

getting below result. but missing  results for M4, M5, M6

MID               total 
M1                 2
M2                 3
m3                 4

Expecting below results

MID               total 
M1                 2
M2                 3
M3                 4
M4                 0
M5                 0
M6                  0

[gcusello · Accepted solution]

Hi @abdul,

the lookup command associates the lookup values only to the key values that are in the search results, so, if you haven't results, you cannot associate any lookup value.

You can reach your target with a different approach:

index=UA sourcetype=apps appname="xyz*"
| fields EID
| dedup EID
| lookup employee.csv EID as EID
| search MID in (M1,M2,M3 M4,M5,M6)
| stats count(EID) as EID by MID
| append [ | inputlookup employee.csv | eval count=0 | dedup MID | fields MID count ]
| stats sum(EID) AS total BY MID

In this way you add to your search the condition for the MID not present in the search results.

Ciao.

Giuseppe

[abdul]

HI @gcusello ,
thank you so much for your help i really appreciate it
below query works

index=UA sourcetype=apps appname="xyz*"
| fields EID
| dedup EID
| lookup employee.csv EID as EID
| append [ | inputlookup employee.csv | eval count=0 | dedup MID | fields MID count ]
| search MID in (M1,M2,M3 M4,M5,M6)
| stats count(EID) AS total BY MID

[abdul]

Hi @gcusello ,

now below result is appearing 
MID               total 
M1                 0
M2                 0
m3                 0