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!

Dashboard Studio Challenge - Learn New Tricks, Showcase Your Skills, and Win Prizes!

Reimagine what you can do with your dashboards. Dashboard Studio is Splunk’s newest dashboard builder to ...

Introducing Edge Processor: Next Gen Data Transformation

We get it - not only can it take a lot of time, money and resources to get data into Splunk, but it also takes ...

Take the 2021 Splunk Career Survey for $50 in Amazon Cash

Help us learn about how Splunk has impacted your career by taking the 2021 Splunk Career Survey. Last year’s ...