https://community.splunk.com/t5/Splunk-Search/How-to-return-0-count-of-events-from-a-lookup-file/m-p/588960#M205104 <P>Hi&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/243871">@Hithere</a>,</P><P>I was confused with another question, my answer is to have the domains with no logs.</P><P>If you want all the domains with status, you could use something like this:</P><LI-CODE lang="markup">index=application WAFRuleMessage="Anomaly Exceeded" | eval Domain=lower(domain) | stats count as ct_Domain by Domain | append [ | inputlookup mylookupfile.csv | eval Domain=lower(domain), ct_Domain=0 | fields Domain AGS ENV ct_Domain ] | stats sum(ct_Domain) as total by AGS ENV Domain | sort AGS -ENV -count | eval status=if(total=0,"Not present","Present") | table AGS ENV Domain status</LI-CODE><P>Ciao.</P><P>Giuseppe</P><P>&nbsp;</P> Mon, 14 Mar 2022 16:57:45 GMT gcusello 2022-03-14T16:57:45Z https://community.splunk.com/t5/Splunk-Search/How-to-return-0-count-of-events-from-a-lookup-file/m-p/588948#M205100 <P>I'm trying to match all domains from a lookup file with a base search and get a count of the events for each one even if there are no events matching (0 count):<BR /><BR />mysite.com&nbsp; count 12<BR />anothersite.com count 5<BR />myothersite.com count 0</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><LI-CODE lang="markup">index=application sourcetype=mysource | lookup myfile.csv Domain as &lt;corelated event field&gt; | append [ | inputlookup myfile.csv | fields Domain] | stats count as total by Domain | fillnull value=0 total | table total | sort -total</LI-CODE><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>&nbsp;</P><P>If you can, please explain your answer - whether there is a syntax error, a keyword misuse, or conceptual error on my part.&nbsp;</P> Mon, 14 Mar 2022 21:58:28 GMT https://community.splunk.com/t5/Splunk-Search/How-to-return-0-count-of-events-from-a-lookup-file/m-p/588948#M205100 Hithere 2022-03-14T21:58:28Z https://community.splunk.com/t5/Splunk-Search/How-to-return-0-count-of-events-from-a-lookup-file/m-p/588954#M205102 <P>Hi&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/243871">@Hithere</a>,</P><P>please try something like this:</P><LI-CODE lang="markup">index=application WAFRuleMessage="Anomaly Exceeded" | eval Domain=lower(domain) | stats count as ct_Domain by Domain | append [ | inputlookup mylookupfile.csv | eval Domain=lower(domain), ct_Domain=0 | fields Domain AGS ENV ct_Domain ] | stats sum(ct_Domain) as total by AGS ENV Domain | sort AGS -ENV -count | where total=0</LI-CODE><P>Ciao.</P><P>Giuseppe</P> Mon, 14 Mar 2022 16:21:22 GMT https://community.splunk.com/t5/Splunk-Search/How-to-return-0-count-of-events-from-a-lookup-file/m-p/588954#M205102 gcusello 2022-03-14T16:21:22Z https://community.splunk.com/t5/Splunk-Search/How-to-return-0-count-of-events-from-a-lookup-file/m-p/588958#M205103 <P>This query retrieves all the lookup domains as desired, but all the total count of events for each domain is 0 which is not expected.</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Hithere_0-1647276243185.png" style="width: 400px;"><img src="https://community.splunk.com/t5/image/serverpage/image-id/18485iCD93EC3BBA5CD6DD/image-size/medium?v=v2&amp;px=400" role="button" title="Hithere_0-1647276243185.png" alt="Hithere_0-1647276243185.png" /></span></P><P>&nbsp;</P> Mon, 14 Mar 2022 16:44:25 GMT https://community.splunk.com/t5/Splunk-Search/How-to-return-0-count-of-events-from-a-lookup-file/m-p/588958#M205103 Hithere 2022-03-14T16:44:25Z https://community.splunk.com/t5/Splunk-Search/How-to-return-0-count-of-events-from-a-lookup-file/m-p/588960#M205104 <P>Hi&nbsp;<a href="https://community.splunk.com/t5/user/viewprofilepage/user-id/243871">@Hithere</a>,</P><P>I was confused with another question, my answer is to have the domains with no logs.</P><P>If you want all the domains with status, you could use something like this:</P><LI-CODE lang="markup">index=application WAFRuleMessage="Anomaly Exceeded" | eval Domain=lower(domain) | stats count as ct_Domain by Domain | append [ | inputlookup mylookupfile.csv | eval Domain=lower(domain), ct_Domain=0 | fields Domain AGS ENV ct_Domain ] | stats sum(ct_Domain) as total by AGS ENV Domain | sort AGS -ENV -count | eval status=if(total=0,"Not present","Present") | table AGS ENV Domain status</LI-CODE><P>Ciao.</P><P>Giuseppe</P><P>&nbsp;</P> Mon, 14 Mar 2022 16:57:45 GMT https://community.splunk.com/t5/Splunk-Search/How-to-return-0-count-of-events-from-a-lookup-file/m-p/588960#M205104 gcusello 2022-03-14T16:57:45Z