topic Re: Best way to query for multiple values in one row in Splunk Search

Best way to query for multiple values in one row

lancealotx — Fri, 24 Aug 2012 16:55:52 GMT

Just got the splunk pdf guide, moved it to the iPad for some weekend reading, still trying to understand how | separates what, hopefully that will help but I will say it's so confusing, really need to grasp this as the requests from people are coming in all over. So, I have users hitting our site via an API. The API has 4 different tokens so I know which application was used (website, iphone, etc.). I would like to know in the past x time each person and the # of times they hit each one, for example;

username - website - iphone - android - website2

usera 0 2 0 5

Now, my query currently looks like this;

memberLevel="1" AND ApiKey = "123" OR ApiKey = "456" OR ApiKey = "789" OR ApiKey = "000" | top username limit="100" application

and the results look like this;

username - application - count - percentage

usera web2 2132 51.232132

application is a lookup to the apikey field so they see web2 instead of 000, but i am trying to basically count BY application and username. Any help on this is appreciated.

************************************************ Updated Comments due to limited reply size ********************************************************

the results is what's not working. The OP showed both what I wanted vs the output. The application has a unique API key by type. I would like to know each user and count each way he accesses the site. So I could get username Joe, then 3 logins by a website, 10 by an iphone. The query provided shows one field application with what I would guess was the last one as opposed to counting each one. I have a separate query that gives me what I want (broken out) but total by day, not by user but maybe the better example.

memberLevel="1" AND (ApiKey = "123" OR ApiKey = "456" OR ApiKey = "789" OR ApiKey = "000") | timechart span=1d count BY application. That output looks like;

_time -- web1 -- iphone -- android -- web2

date -- 1200 -- 800 -- 982 -- 430

I can look at that and say ok, most people came from web1. Now I want to remove the date, and just say for the last 7 day's, tell me the top users of the site, broken out;

username -- web1 -- iphone -- android -- web2

joe -- 200 -- 5 -- 0 -- 0

sam -- 110 -- 0 -- 5 -- 0

Does it make more sense when I explain it like that?

Re: Best way to query for multiple values in one row

Ayn — Fri, 24 Aug 2012 17:36:43 GMT

I don't really get it - if you do a top username,application you WILL get a count BY each combination of application and username Splunk finds. What's not working?

Re: Best way to query for multiple values in one row

lancealotx — Fri, 24 Aug 2012 18:17:12 GMT

Sorry you don't get it. Reply doesn't allow enough txt, so I went into more details with more examples in the original post under a ******* update ******** part. Feel free to look and maybe it will explain it better.

Re: Best way to query for multiple values in one row

johandk — Fri, 24 Aug 2012 18:38:45 GMT

Try something like this:

memberLevel="1" AND ApiKey = "123" OR ApiKey = "456" OR ApiKey = "789" OR ApiKey = "000" | stats count(eval(application="web1")) AS web1, count(eval(application="iphone")) AS iPhone by username

In the docs:
http://docs.splunk.com/Documentation/Splunk/4.3.3/SearchReference/Stats

EDIT:

memberLevel="1" AND ApiKey = "123" OR ApiKey = "456" OR ApiKey = "789" OR ApiKey = "000" | stats count(eval(application="web1")) AS web1, count(eval(application="iphone")) AS iPhone by username | search NOT (web1=0 AND iPhone=0)

Re: Best way to query for multiple values in one row

lancealotx — Fri, 24 Aug 2012 18:57:15 GMT

Cool, the only negative I see are it's in order by username so there are a lot of zeros. To fix I could either (in english);
- count the total behind the scenes and sort by that value
- only show where all 4 are not 0

Not sure how hard that is, but at least with the above, people can look, click the arrows and move along! Are either the above ones easier or harder?

Re: Best way to query for multiple values in one row

Ayn — Fri, 24 Aug 2012 19:09:30 GMT

top acts like stats in that it gives you one line per pair instead of a matrix which seems to be what you want. chart will give a matrix, though. So, this:

memberLevel="1" AND ApiKey = "123" OR ApiKey = "456" OR ApiKey = "789" OR ApiKey = "000" | chart count by username,application

should give you what you want, I hope. 🙂 Or what johandk suggested works, too.

EDIT: And if you want to sort by tha total value, add | addtotals at the end. This will give you a field called "Total" at the end of each line, that you could sort by with | sort - Total

Re: Best way to query for multiple values in one row

johandk — Fri, 24 Aug 2012 19:42:59 GMT

Option 2 is a lot easier. See my edit.

Re: Best way to query for multiple values in one row

johandk — Fri, 24 Aug 2012 19:49:38 GMT

This solution is better than mine. Simpler. Guess I use stats so much that chart often slips my mind. And with this it's still possible to append the |search to only display rows that aren't all zero as per my edit.