Splunk Search

Search - count by 2 fields

madhav_dholakia
Communicator

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
Communicator

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
Communicator

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
Communicator

Thank you @robinsonalex88 

0 Karma

madhav_dholakia
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...