Splunk Search

Best way to query for multiple values in one row

lancealotx
Explorer

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?

Tags (1)
0 Karma

Ayn
Legend

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

johandk
Path Finder

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.

0 Karma

johandk
Path Finder

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)
0 Karma

johandk
Path Finder

Option 2 is a lot easier. See my edit.

0 Karma

lancealotx
Explorer

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?

0 Karma

lancealotx
Explorer

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.

0 Karma

Ayn
Legend

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?

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...