1) I have got a query whose output are events that contains a field called CV4_TExCd.
The base query looks like this:
index=ivr sourcetype=ivr_SEF applicationName=TestApp CV4_TExCd!=000000
2) I have a lookup table that has 2 columns, Site and TeamCode
TeamCode Site
84001 SiteA
84001 SiteA
84002 SiteA
84002 SiteA
84003 SiteA
9001 SiteB
9001 SiteB
9002 SiteB
9002 SiteB
9003 SiteB
37001 SiteC
37001 SiteC
37002 SiteC
37002 SiteC
37003 SiteC
3) I want to search field CV4_TExCd against TeamCode from lookup table; when found count by Site
4) I was playing around with this query below but I noticed that my count is doubled. I have no idea why! 😞
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
Please help and thanks in advance!
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:
| inputlookup lookupTable | dedup TeamCode Site | outputlookup lookupTable
or if you just want to get a count of all distinct Site values:
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
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 | dedup TeamCode
before |stats
.
Thanks Rich! That was it. I had modified the lookup table and getting the right results now 🙂 THank you!!
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:
| inputlookup lookupTable | dedup TeamCode Site | outputlookup lookupTable
or if you just want to get a count of all distinct Site values:
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
I corrected my lookup table. That corrected my issue. I thought my query was wrong but it was the lookup table! Thanks Elliot!
Glad I could help!
Hi
Can you please try this one?
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
Thanks