Search - count by 2 fields

[madhav_dholakia]

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

[richgalloway]

Have you tried specifying "teamName=*" in the base query?

[madhav_dholakia]

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.

[robinsonalex88 · Accepted solution]

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.

[madhav_dholakia]

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.

[robinsonalex88]

@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.

[madhav_dholakia]

Thank you @robinsonalex88 

[madhav_dholakia]

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