How to achieve conditional count for multivalue field?

luis_carlos — Thu, 15 Jun 2023 12:37:58 GMT

Hi, I'm kind of new to Splunk and I was wondering if someone could help on this:

What I'm trying to do is a timechart that counts by month, the number of hosts that had 3 or more lastlogons.

So far this is what I have:

index="assets" sourcetype="ldap:devices"
| stats values(lastLogonTimestamp) as "LastLogon" by host
| eval LastLogon_Count = mvcount(LastLogon)

host LastLogon LastLogon_Count

Host1	2023-06-10T14:05:35.849017Z	1
Host2	2023-06-10T16:24:01.290211Z	1
Host3	2023-03-12T01:30:39.853238Z 2023-03-22T12:01:18.877600Z 2023-04-01T14:05:33.812544Z 2023-04-11T15:34:16.462356Z 2023-04-24T11:50:29.265116Z 2023-05-04T12:34:50.229455Z 2023-05-14T16:16:22.161436Z 2023-05-29T00:57:30.342080Z	8
Host4	2023-06-10T16:23:14.783142Z	1
Host5	2023-06-10T14:05:51.345719Z	1
Host6	2023-05-11T14:52:26.019471Z 2023-05-21T21:22:27.404659Z 2023-05-31T22:02:28.210643Z 2023-06-12T00:59:03.121092Z	4
Host7	2023-05-11T14:46:42.864582Z 2023-05-21T18:02:34.820364Z 2023-05-31T22:13:17.107118Z 2023-06-11T00:32:24.358015Z	4
Host8	2023-06-10T14:05:04.812651Z	1
Host9	2023-06-10T14:05:20.315748Z	1
Host10	2023-06-10T14:06:37.952136Z	1

From this results I want to count on a timechart the hosts that had 3 or more lastlogon on the LastLogon_Count field.
So let's say here the count should only be 3 (Host3,Host6,Host7)

I tried doing this, but got no results:

index="assets" sourcetype="ldap:devices"
| stats values(lastLogonTimestamp) as "LastLogon" by host
| eval LastLogon_Count = mvcount(LastLogon)
| timechart span=1mon count(eval(if(LastLogon_Count >= 3, 1,0))) by host

Re: Conditional Count for Multivalue Field

richgalloway — Tue, 13 Jun 2023 21:03:18 GMT

Try a simpler expression in the timechart command.

Either

index="assets" sourcetype="ldap:devices" | stats values(lastLogonTimestamp) as "LastLogon" by host | eval LastLogon_Count = mvcount(LastLogon) | where LastLogon_Count >= 3 | timechart span=1mon count by host

index="assets" sourcetype="ldap:devices" | stats values(lastLogonTimestamp) as "LastLogon" by host | eval LastLogon_Count = mvcount(LastLogon) | eval too_many = if(LastLogon_Count >= 3, 1, 0) | timechart span=1mon sum(too_many) by host

topic How to achieve conditional count for multivalue field? in Splunk Search

How to achieve conditional count for multivalue field?

Re: Conditional Count for Multivalue Field