Splunk Search

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

mmdacutanan
Explorer

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!

0 Karma
1 Solution

elliotproebstel
Champion

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

richgalloway
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, Karma would be appreciated.
0 Karma

mmdacutanan
Explorer

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

0 Karma

elliotproebstel
Champion

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

mmdacutanan
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

elliotproebstel
Champion

Glad I could help!

0 Karma

kamlesh_vaghela
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...