topic Re: Distinct counts on only those fields that begin with a particular string in Splunk Search

Distinct counts on only those fields that begin with a particular string

deton0 — Mon, 18 Oct 2021 03:59:20 GMT

I'm searching on an internet usage index for events that contain a particular word somewhere in the domain. For example the word could be "edublogs".

From the result, I need to sum the total of bytes downloaded for all events returned. The problem I'm having is needing to calculate a distinct count of centers and a distinct count of userids where the domain name only begins with "edublogs", e.g. "edublogs.com" and not when it doesn't, e.g. "accountsedublogs.com".

I know this example is wrong but can someone please help me with how I would change it to achieve the outcome below?

index=searchdata domain="*edublogs*"
| stats count dc(centre) AS distinctCenters
| stats count dc(userid) AS distinctUserids
| stats sum(bytes) AS totalBytes by domain
| table domain totalBytes, distinctCenters, distinctUserids

My desired results would look something like...
domain totalBytes distinctCenters distinctUserids
Senior 50000 15 321

Many thanks

Re: Distinct counts on only those fields that begin with a particular string

kamlesh_vaghela — Mon, 18 Oct 2021 05:21:32 GMT

@deton0

Can you please try this?

index=searchdata domain="*edublogs*" | stats count dc(centre) AS distinctCenters, dc(userid) AS distinctUserids, sum(bytes) AS totalBytes by domain | table domain totalBytes, distinctCenters, distinctUserids

Re: Distinct counts on only those fields that begin with a particular string

deton0 — Mon, 18 Oct 2021 22:01:01 GMT

Hi KV

Thanks for this. It's wasn't quite what I needed but got me on the right path to working it out. This is how I changed it and it appears to be working how I need with my original data.

index=searchdata domain="*edublogs*"
| eval distinctCenters=Case(Like(domain,"edublogs%"), centre)
| eval distinctUserids=Case(Like(domain,"edublogs%"), userid)
| eventstats dc(distinctCenters) as distinctCenters, dc(distinctUserids) as distinctUserids by domain
| stats count, sum(bytes) AS totalBytes by domain, distinctCenters, distinctUserids
| table domain totalBytes, distinctCenters, distinctUserids

deton0