topic Re: Search - count by 2 fields in Splunk Search

Search - count by 2 fields

madhav_dholakia — Sun, 21 Jun 2020 04:26:10 GMT

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

Re: Search - count by 2 fields

richgalloway — Sun, 21 Jun 2020 12:10:33 GMT

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

Re: Search - count by 2 fields

madhav_dholakia — Mon, 22 Jun 2020 08:17:13 GMT

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

Re: Search - count by 2 fields

madhav_dholakia — Mon, 22 Jun 2020 13:32:40 GMT

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.

Re: Search - count by 2 fields

robinsonalex88 — Mon, 22 Jun 2020 17:41:06 GMT

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.

Re: Search - count by 2 fields

madhav_dholakia — Tue, 23 Jun 2020 05:46:31 GMT

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.

Re: Search - count by 2 fields

robinsonalex88 — Tue, 23 Jun 2020 12:43:50 GMT

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

Re: Search - count by 2 fields

madhav_dholakia — Tue, 23 Jun 2020 12:48:38 GMT

Thank you @robinsonalex88