https://community.splunk.com/t5/Splunk-Search/My-count-is-double-Can-I-use-field-from-search-to-search-lookup/m-p/311021#M93242 <P>1) I have got a query whose output are events that contains a field called CV4_TExCd.<BR /> The base query looks like this:<BR /> index=ivr sourcetype=ivr_SEF applicationName=TestApp CV4_TExCd!=000000<BR /> 2) I have a lookup table that has 2 columns, Site and TeamCode</P> <P>TeamCode Site<BR /> 84001 SiteA<BR /> 84001 SiteA<BR /> 84002 SiteA<BR /> 84002 SiteA<BR /> 84003 SiteA<BR /> 9001 SiteB<BR /> 9001 SiteB<BR /> 9002 SiteB<BR /> 9002 SiteB<BR /> 9003 SiteB<BR /> 37001 SiteC<BR /> 37001 SiteC<BR /> 37002 SiteC<BR /> 37002 SiteC<BR /> 37003 SiteC</P> <P>3) I want to search field CV4_TExCd against TeamCode from lookup table; when found count by Site </P> <P>4) I was playing around with this query below but I noticed that my count is doubled. I have no idea why! <span class="lia-unicode-emoji" title=":disappointed_face:">😞</span></P> <P>index=ivr sourcetype=ivr_SEF applicationName=TestApp CV4_TExCd!=000000 _time=* | lookup lookupTable TeamCode AS CV4_TExCd OUTPUT Site AS Site| stats count by Site</P> <P>Please help and thanks in advance!</P> Tue, 29 Sep 2020 16:17:02 GMT mmdacutanan 2020-09-29T16:17:02Z https://community.splunk.com/t5/Splunk-Search/My-count-is-double-Can-I-use-field-from-search-to-search-lookup/m-p/311021#M93242 <P>1) I have got a query whose output are events that contains a field called CV4_TExCd.<BR /> The base query looks like this:<BR /> index=ivr sourcetype=ivr_SEF applicationName=TestApp CV4_TExCd!=000000<BR /> 2) I have a lookup table that has 2 columns, Site and TeamCode</P> <P>TeamCode Site<BR /> 84001 SiteA<BR /> 84001 SiteA<BR /> 84002 SiteA<BR /> 84002 SiteA<BR /> 84003 SiteA<BR /> 9001 SiteB<BR /> 9001 SiteB<BR /> 9002 SiteB<BR /> 9002 SiteB<BR /> 9003 SiteB<BR /> 37001 SiteC<BR /> 37001 SiteC<BR /> 37002 SiteC<BR /> 37002 SiteC<BR /> 37003 SiteC</P> <P>3) I want to search field CV4_TExCd against TeamCode from lookup table; when found count by Site </P> <P>4) I was playing around with this query below but I noticed that my count is doubled. I have no idea why! <span class="lia-unicode-emoji" title=":disappointed_face:">😞</span></P> <P>index=ivr sourcetype=ivr_SEF applicationName=TestApp CV4_TExCd!=000000 _time=* | lookup lookupTable TeamCode AS CV4_TExCd OUTPUT Site AS Site| stats count by Site</P> <P>Please help and thanks in advance!</P> Tue, 29 Sep 2020 16:17:02 GMT https://community.splunk.com/t5/Splunk-Search/My-count-is-double-Can-I-use-field-from-search-to-search-lookup/m-p/311021#M93242 mmdacutanan 2020-09-29T16:17:02Z https://community.splunk.com/t5/Splunk-Search/My-count-is-double-Can-I-use-field-from-search-to-search-lookup/m-p/311022#M93243 <P>Hi <BR /> Can you please try this one?</P> <PRE><CODE>index=ivr sourcetype=ivr_SEF applicationName=TestApp CV4_TExCd!=000000 _time=* | dedup CV4_TExCd | lookup lookupTable TeamCode AS CV4_TExCd OUTPUT Site AS Site| stats count by Site </CODE></PRE> <P>Thanks</P> Thu, 19 Oct 2017 12:55:52 GMT https://community.splunk.com/t5/Splunk-Search/My-count-is-double-Can-I-use-field-from-search-to-search-lookup/m-p/311022#M93243 kamlesh_vaghela 2017-10-19T12:55:52Z https://community.splunk.com/t5/Splunk-Search/My-count-is-double-Can-I-use-field-from-search-to-search-lookup/m-p/311023#M93244 <P>I'd imagine that's because you have duplicate entries in your lookup table. You could either delete the duplicate entries in the lookup table:<BR /> <CODE>| inputlookup lookupTable | dedup TeamCode Site | outputlookup lookupTable</CODE><BR /> or if you just want to get a count of all distinct Site values:<BR /> <CODE>index=ivr sourcetype=ivr_SEF applicationName=TestApp CV4_TExCd!=000000 _time=* | lookup lookupTable TeamCode AS CV4_TExCd OUTPUT Site AS Site| stats dc(Site) AS site_count</CODE></P> Thu, 19 Oct 2017 13:01:08 GMT https://community.splunk.com/t5/Splunk-Search/My-count-is-double-Can-I-use-field-from-search-to-search-lookup/m-p/311023#M93244 elliotproebstel 2017-10-19T13:01:08Z https://community.splunk.com/t5/Splunk-Search/My-count-is-double-Can-I-use-field-from-search-to-search-lookup/m-p/311024#M93245 <P>Most of the entries in your sample lookup table are duplicates. If that is true in the real file, try removing the duplicate entries from the lookup or add <CODE>| dedup TeamCode</CODE> before <CODE>|stats</CODE>.</P> Thu, 19 Oct 2017 13:07:34 GMT https://community.splunk.com/t5/Splunk-Search/My-count-is-double-Can-I-use-field-from-search-to-search-lookup/m-p/311024#M93245 richgalloway 2017-10-19T13:07:34Z https://community.splunk.com/t5/Splunk-Search/My-count-is-double-Can-I-use-field-from-search-to-search-lookup/m-p/311025#M93246 <P>Thanks Rich! That was it. I had modified the lookup table and getting the right results now <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span> THank you!!</P> Thu, 19 Oct 2017 23:58:01 GMT https://community.splunk.com/t5/Splunk-Search/My-count-is-double-Can-I-use-field-from-search-to-search-lookup/m-p/311025#M93246 mmdacutanan 2017-10-19T23:58:01Z https://community.splunk.com/t5/Splunk-Search/My-count-is-double-Can-I-use-field-from-search-to-search-lookup/m-p/311026#M93247 <P>I corrected my lookup table. That corrected my issue. I thought my query was wrong but it was the lookup table! Thanks Elliot! </P> Thu, 19 Oct 2017 23:59:01 GMT https://community.splunk.com/t5/Splunk-Search/My-count-is-double-Can-I-use-field-from-search-to-search-lookup/m-p/311026#M93247 mmdacutanan 2017-10-19T23:59:01Z https://community.splunk.com/t5/Splunk-Search/My-count-is-double-Can-I-use-field-from-search-to-search-lookup/m-p/311027#M93248 <P>Glad I could help!</P> Fri, 20 Oct 2017 02:33:45 GMT https://community.splunk.com/t5/Splunk-Search/My-count-is-double-Can-I-use-field-from-search-to-search-lookup/m-p/311027#M93248 elliotproebstel 2017-10-20T02:33:45Z