topic Re: How to filter multiple values in a given field in Splunk Search

How to filter multiple values in a given field?

hmohta — Wed, 17 Aug 2022 14:38:24 GMT

Hi all, I am new at Splunk and trying to evaluate this query.

I have some accounts, dates(week starting) and number of browsers used by the account for that date.

I have grouped the dates and number_of_browsers. there is 1 account but multiple dates and multiple or single values for browser_types. My query:

index="a" source type="ab"| rename Week Starting AS Date
| stats sum(browser_ types) AS New_BrowserTypes by AccountID TotalUsers Date
| eval New_BrowserTypes= round(New_BrowserTypes/TotalUsers,2)
| stats MAX(New_BrowserTypes) as logins by AccountID Date
| stats values(Date) values(logins) by AccountID

which gives an output something like this:

AccountID	values(date)	values(logins)
502	2020-07-20	20.00
102	2020-07-20	15.00
	2020-08-25	18.00
304	2020-07-20	24.00
	2020-08-25	18.00
	2021-07-20	25.00
	2021-08-25	15.00

For the final result, I want to use AccountID's where values(logins) are >1. So I want to use only those accounts where, logins are 2 or more. how do I achieve this? thank you in advance. Please not this is only an example, my actual AccountID's are more than 500.

Re: How to filter multiple values in a given field

yuanliu — Wed, 17 Aug 2022 06:38:14 GMT

Based on your SPL, the resultant values(Date) and values(logins) are both multivalued; thus, I speculate that the output looks more like

AccountID	values(Date)	values(logins)
502	2020-07-20	20.00
102	2020-07-20 2020-08-25	15.00 18.00
304	2020-07-20 2020-08-25 2021-07-20 2021-08-25	15.00 18.00 24.00 25.00

So, you will need to clarify your criteria "only those accounts where, logins are 2 or more." Are you selecting those whose "logins" are 2 or more on each selected "Date"? or in aggregate after all Dates?

Assuming it is the former (select Dates), you can do something like

index="a" source type="ab"| rename "Week Starting" AS Date | stats sum(browser_types) AS New_BrowserTypes by AccountID TotalUsers Date | eval New_BrowserTypes= round(New_BrowserTypes/TotalUsers,2) | stats MAX(New_BrowserTypes) as logins by AccountID Date | where logins > 2 | stats values(Date) values(logins) by AccountID

If you need the latter (aggregate), try something like

index="a" source type="ab"| rename "Week Starting" AS Date | stats sum(browser_types) AS New_BrowserTypes by AccountID TotalUsers Date | eval New_BrowserTypes= round(New_BrowserTypes/TotalUsers,2) | stats MAX(New_BrowserTypes) as logins by AccountID Date | stats values(Date) values(logins) sum(logins) as Aggregate_Logins by AccountID | where Aggregate_Logins > 2

Re: How to filter multiple values in a given field

hmohta — Wed, 17 Aug 2022 06:48:56 GMT

thankyou for your prompt reply. I am after results where ALL Dates are suppose to include. Yes your output table is better than mine:). your reply for aggregate give me the total of values for all accounts where aggregate is >2. thankyou. But I am not after aggregate, rather I want to use/see "only" those AccountID's where "values(logins)" is >2. so I want AccountID's like 102,304 only to appear for my output.

Hope this is better.

Re: How to filter multiple values in a given field

yuanliu — Wed, 17 Aug 2022 07:31:56 GMT

Still unclear what is unique about 102 and 304, because in your sample output, 502 has 20 logins. But anyway, if you simply want to select any values(login) is greater than 2, put a single quote across this output in where command, like

index="a" source type="ab"| rename "Week Starting" AS Date | stats sum(browser_types) AS New_BrowserTypes by AccountID TotalUsers Date | eval New_BrowserTypes= round(New_BrowserTypes/TotalUsers,2) | stats MAX(New_BrowserTypes) as logins by AccountID Date | stats values(Date) values(logins) by AccountID | where 'values(logins)' > 2

Most people choose to add "AS" clause to those values() function to get rid of the single quote nuisance.

Re: How to filter multiple values in a given field

hmohta — Wed, 17 Aug 2022 07:58:58 GMT

@yuanliu .thankyou for replying. Let me try this again. ( I have attached the table again)

AccountID-502 : has only 1 value of "20".

AccountID 102 and 304 have 2 and 4 values respectively. Now I have tried your suggestions of

| where 'values(logins)' > 2

But this singles out all Dates. I want the Values(date) to remain grouped. So in this instance I don't want AccountID502 as it has only 1 value. I want my final output which shows only those AccountID's where "values(logins)" are >=2 values. So in this instance 102 and 304.

AccountID	values(Date)	values(logins)
502	2020-07-20	20.00
102	2020-07-20 2020-08-25	15.00 18.00
304	2020-07-20 2020-08-25 2021-07-20 2021-08-25	15.00 18.00 24.00 25.00

Re: How to filter multiple values in a given field

isoutamo — Wed, 17 Aug 2022 20:32:25 GMT

Can you try

| where mvcount('values(logins)') > 2

or if this is not working then 1st rename that values(logins) to new name and then use it inside mvcount.

r. Ismo

Re: How to filter multiple values in a given field

hmohta — Wed, 17 Aug 2022 22:48:11 GMT

@isoutamo Worked like a charm, thankyou so much!!