topic Re: Filtering by account id in Getting Data In

Filtering by account id

Dima101010101 — Wed, 19 Feb 2014 12:42:01 GMT

Hi guys, quick question:
I have stats for users that have unique account ids. I want to search events registered only to specific group of accounts. For example, if I have 1,000 account ids (each constructed out of 7 digits), I want to display results for account ids below certain value.

Now I have search query that displays all of them. When I add "field < value" to the query, splunk shows me message "No results found". By "field" I mean specific word, in my case it's "account_id", and by "value" I mean specific number of account that I want results below it (for example I want results for accounts below 1085382). So only by adding this search parameter I receive no results. What should I do?

Thank you for your help.

Re: Filtering by account id

MuS — Wed, 19 Feb 2014 12:52:39 GMT

Hi Dima101010101,

append (without the dots, but include the |) this to your existing search:

... | where account_id < "1085382" ...

This will return all account_id's which are less then 1085382.

hope this helps ...

cheers, MuS

Re: Filtering by account id

kristian_kolb — Wed, 19 Feb 2014 12:59:34 GMT

This should work,

index=_internal sourcetype=splunk_web_access status<300

index=_internal sourcetype=splunk_web_access | where status>300

Re: Filtering by account id

Dima101010101 — Wed, 19 Feb 2014 13:32:16 GMT

Thank you for answers. The first solution by MuS didn't work. I received the same message.

Regarding the solution by kristian.kolb, I am not quite sure i understand it. I already have index , sourcetype and status fields. I write for them specific values that are relevant fort my search.
What I am looking for is the way to filter those results by account id numbers (not by number of ids, but by specific id numbers, if you understand what I mean).

Re: Filtering by account id

somesoni2 — Wed, 19 Feb 2014 14:07:24 GMT

Try this

your search giving all account_id | where tonumber(account_id) < 1234567

Re: Filtering by account id

Dima101010101 — Wed, 19 Feb 2014 14:10:14 GMT

Still nothing.
The most interesting part is, that when I do search for specific id: where account_id = 1085382, I receive proper result only for this ID. But if I try more or less with < > signs, there is nothing.
Any other parameters that can do "more/less than" search?

Re: Filtering by account id

Dima101010101 — Wed, 19 Feb 2014 14:16:23 GMT

Thanks, but still no success.

Re: Filtering by account id

Dima101010101 — Wed, 19 Feb 2014 14:31:39 GMT

let me explain more. the service is games, played by users. I do search for number of game plays and number of users, per each game. In the results I receive list of games and each has stats for how many game plays and how many users played the game.
What I want to filter, is the results from users that have id number higher than one I want. Or vice-versa: lower than what I want.

So the original search is not for account ids, I just want my results for specific account ids. Hope I clarified this a bit.

Re: Filtering by account id

MuS — Wed, 19 Feb 2014 14:35:35 GMT

nevertheless any version (/k's or mine) of where should work fine, you just have to use the field name that want to use in your lower/higher filter.

Re: Filtering by account id

thslopes — Wed, 19 Feb 2014 14:52:19 GMT

Hi friend,

You need to check if your field was recognized by splunk before use it.

Do you see your field on the left of the results, on the fields list?

Re: Filtering by account id

Dima101010101 — Wed, 19 Feb 2014 14:56:22 GMT

Yes, I do. The field is fine. I can search for specific id by inserting parameter account_id = 'number'.
In this case I receive results for this id. If for the same exact search I change = with < or > the search fails and I see message "No results".

Re: Filtering by account id

Dima101010101 — Wed, 19 Feb 2014 14:58:40 GMT

This is so weird, it should work but unfortunately it doesn't. Bummer.

Re: Filtering by account id

kristian_kolb — Wed, 19 Feb 2014 15:06:27 GMT

This was just an example of how the search language works. The sample data I used is from the _internal index, and all Splunk installations have that, so you can test the query by cut-and-paste.

Re: Filtering by account id

MuS — Wed, 19 Feb 2014 15:06:40 GMT

Dima, thx for the points - can you join IRC #splunk tomorrow? We can have a chat there and I can help you directly?

Re: Filtering by account id

Dima101010101 — Mon, 28 Sep 2020 15:56:03 GMT

I see, this is exactly what I use. It is similar to what MuS recommended. I use "account_id < number" in the same search window with index and sourcetype. And I also tried outside with | where...
Nothing works so far. And I know that I do it right because when I use account_id = number, equal to specific id, then I get results

Re: Filtering by account id

Dima101010101 — Wed, 19 Feb 2014 16:04:57 GMT

IRC is not working for me.
Missing Application-Name manifest attribute for: http://www.splunk.com/themes/splunk_com/scripts/pjirc/irc.jar
Is this some kind of Java issue?

Re: Filtering by account id

MuS — Wed, 19 Feb 2014 16:45:18 GMT

No the IRC chat network....this not started nor run in Splunk itself 😉

Re: Filtering by account id

Dima101010101 — Mon, 28 Sep 2020 15:56:09 GMT

Strange, this is what I see in the error details.
In any case I found the problem - for some reason the account_id field is multi-valued, it holds the account id twice. So after using | where account_id0 < number | the results seem to be displayed correctly.

Re: Filtering by account id

Dima101010101 — Mon, 28 Sep 2020 15:56:12 GMT

I found the problem - for some reason the account_id field is multi-valued, it holds the account id twice. So after using | eval account_id0=mvindex(account_id,0) | where account_id0 < number | the results seem to be displayed correctly.

This is a common problem in splunk in Statistics search. Some of my data appeared twice so I had to do such changes before to other fields as well.

Anyway, thank you all for the help.

Re: Filtering by account id

kristian_kolb — Wed, 19 Feb 2014 17:52:49 GMT

You cannot use the quotes in the way you just did in the comment above.

index=blah "userid<1234"

will not work unless that exact string actually exists in an event.

However, if your events look like this;

2014-02-19 11:22:33 userid=1234 blah blah

you can search for the literal string "userid=1234", but not "userid<1500".

Could that be the issue?