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?
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
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
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.
I added a lookup to find the name of the school but this worked great. Thanks.
Sorry. cname is a lookup based on the cid field.