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
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
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
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
Hi @gcusello ,
now below result is appearing
MID total
M1 0
M2 0
m3 0