- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- My count is double. Can I use field from search to...
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Rich! That was it. I had modified the lookup table and getting the right results now 🙂 THank you!!
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I corrected my lookup table. That corrected my issue. I thought my query was wrong but it was the lookup table! Thanks Elliot!
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Glad I could help!
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Introducing the 2024 SplunkTrust!
Introducing the 2024 Splunk MVPs!
Splunk Custom Visualizations App End of Life
