Splunk Search

How to filter multiple values in a given field?

hmohta
Path Finder

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.

 

 

Labels (3)
0 Karma
1 Solution

isoutamo
SplunkTrust
SplunkTrust

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 

View solution in original post

yuanliu
SplunkTrust
SplunkTrust

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

AccountIDvalues(Date)values(logins)
5022020-07-2020.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
Tags (1)
0 Karma

hmohta
Path Finder

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.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.

0 Karma

hmohta
Path Finder

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

AccountIDvalues(Date)values(logins)
5022020-07-2020.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

0 Karma

isoutamo
SplunkTrust
SplunkTrust

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 

hmohta
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Build Scalable Security While Moving to Cloud - Guide From Clayton Homes

 Clayton Homes faced the increased challenge of strengthening their security posture as they went through ...

Mission Control | Explore the latest release of Splunk Mission Control (2.3)

We’re happy to announce the release of Mission Control 2.3 which includes several new and exciting features ...

Cloud Platform | Migrating your Splunk Cloud deployment to Python 3.7

Python 2.7, the last release of Python 2, reached End of Life back on January 1, 2020. As part of our larger ...