Splunk Search

My count is double. Can I use field from search to search lookup table?

Explorer

1) I have got a query whose output are events that contains a field called CV4TExCd.
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=ivrSEF applicationName=TestApp CV4TExCd!=000000 time=* | lookup lookupTable TeamCode AS CV4TExCd OUTPUT Site AS Site| stats count by Site

Please help and thanks in advance!

0 Karma
1 Solution

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

View solution in original post

SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

Explorer

Thanks Rich! That was it. I had modified the lookup table and getting the right results now 🙂 THank you!!

0 Karma

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

View solution in original post

Explorer

I corrected my lookup table. That corrected my issue. I thought my query was wrong but it was the lookup table! Thanks Elliot!

0 Karma

Glad I could help!

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma