How to find all results in an inputlookup and display zero if no events are matched?

[justinfranks]

I have a semi-static CSV that I am using for an input lookup to generate usage data on trial application we have in our environment. The lookup is working well except if no events are matched to the lookup. In these cases, I would like to display a zero count.

Here is my lookup code:

sourcetype=etv e=logon s=139 ur=TEACHER [| inputlookup etvd_trial_ids_week4.csv | rename "Customer ID" as cid, CurrentTrialStartDate as earliest  | format] | stats dc(u) as dis_count by cname, cid

This gives me a table with two entries:

Caringbah High School                CS04370     1
Hurlstone Agricultural High School   CS04252     17

However, I know that there are 4 entries in my CSV:

| inputlookup etvd_trial_ids_week4.csv | rename "Customer ID" as cid, CurrentTrialStartDate as earliest

gives the following output:

CS04252  07/23/2014:00:00:00
CS04370  07/18/2014:00:00:00
CS05096  07/17/2014:00:00:00
CS09634  07/21/2014:00:00:00

Is it possible to display a zero in the dis_count field if no matching events are found?

[HiroshiSatoh · Accepted solution]

cid and cname is a one-to-one?

sourcetype=etv e=logon s=139 ur=TEACHER [| inputlookup etvd_trial_ids_week4.csv | rename "Customer ID" as cid, CurrentTrialStartDate as earliest | format] | stats dc(u) as dis_count by cname, cid|append [| inputlookup etvd_trial_ids_week4.csv | rename "Customer ID" as cid|eval cname=""|eval dis_count=0|table cname,cid,dis_count]|stats first(cname),sum(dis_count) by cid

[HiroshiSatoh]

Has been changed.

sourcetype=etv e=logon s=139 ur=TEACHER [| inputlookup etvd_trial_ids_week4.csv | rename "Customer ID" as cid, CurrentTrialStartDate as earliest | format] | stats dc(u) as dis_count by cname, cid|append [| inputlookup etvd_trial_ids_week4.csv | rename "Customer ID" as cid|eval dis_count=0|table cname,cid,dis_count]|stats sum(dis_count) by cname,cid

However, I think [earliest] is not functioning correctly.

[justinfranks]

I added a lookup to find the name of the school but this worked great. Thanks.

[justinfranks]

Sorry. cname is a lookup based on the cid field.