Splunk Search

Search - count by 2 fields

madhav_dholakia
Contributor

Hello,

I have a live database feed through DB Connect. This feed is having incidents data for different teams and _time is set to last_updated.

I am trying to find count of different incident statuses by Teams , I am trying below search (with time-picker set to last 6 months) but it is not showing correct numbers:

index=idx_1 source="idx_src_1" sourcetype="idx_srctype_1"
| rename COMMENT as "sorting data in descending order and removing duplicates by keeping the latest record for each incident id"
| sort -lastUpdate
| dedup incID 
| rename COMMENT as "to get the count of incidents for each team by incident status"
| chart count by incStatus,teamName

but if I specify a team name in the search, it gives correct numbers:

index=idx_1 source="idx_src_1" sourcetype="idx_srctype_1" teamName="Team1"
| rename COMMENT as "sorting data in descending order and removing duplicates by keeping the latest record for each incident id"
| sort -lastUpdate
| dedup incID 
| chart count by incStatus,teamName

 Can someone please suggest me on how to resolve this.

Thank you.

Madhav

Labels (3)
0 Karma
1 Solution

robinsonalex88
Explorer

How many events are being looked at when teamName is not specified?  The sort command has a default limit of 10000 results so you may have been hitting that limit looking at all teams.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust
Have you tried specifying "teamName=*" in the base query?
---
If this reply helps you, Karma would be appreciated.
0 Karma

madhav_dholakia
Contributor

Hi,

I have just removed sorting based on lastUpdate from the query I posted in my question and then it is giving correct result.

index=idx_1 source="idx_src_1" sourcetype="idx_srctype_1"
| rename COMMENT as "removed sorting data in descending order and only kept dedup for incident id"
| dedup incID 
| rename COMMENT as "to get the count of incidents for each team by incident status"
| chart count by incStatus,teamName

I do not understand this behavior but it somehow worked.

Thank you.

0 Karma

robinsonalex88
Explorer

How many events are being looked at when teamName is not specified?  The sort command has a default limit of 10000 results so you may have been hitting that limit looking at all teams.

0 Karma

madhav_dholakia
Contributor

Thank you, @robinsonalex88 - yes, there were 20k+ events and I was using | sort without specifying "0". After I added "| sort 0 -lastUpdate", it works fine and gives correct numbers.

So just for my understanding, if we use just "| sort" and have more than 10k+ events, it will consider only those events to get the results, correct?

Thank you.

0 Karma

robinsonalex88
Explorer

@madhav_dholakiayes if no limit is specified with the |sort command then it will only return 10k results.  So your subsequent |dedup and |chart commands were only looking at 10k results instead of the full data set returned by the initial search.

Tags (1)

madhav_dholakia
Contributor

Thank you @robinsonalex88 

0 Karma

madhav_dholakia
Contributor

Thank you. I have tried this but still not getting correct numbers.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...