topic Re: How to search the top users and compare the usage stats of those users with the previous two days? in Splunk Search

How to search the top users and compare the usage stats of those users with the previous two days?

Vicky84 — Tue, 13 Dec 2016 23:02:09 GMT

I have the search below to pull out the count of users for today & last two days.
I want to modify this to pull the top users and compare the usage stats of those users from the previous two days. I tried with the top command, but I guess I am doing something wrong :

index=apiUser  earliest=-d@d | eval timeframe=case(_time>relative_time(now(), "@d"), "Today",_timerelative_time(now(), "-1d@d"), "1 days") | chart count(userID) over userID by timeframe

userID - is the unique user Id of a person using the services
api - is the name of api that he is using (one user can call multiple api's & I am interested in his(10 top users) total count for a day)

Re: How to search the top users and compare the usage stats of those users with the previous two days?

somesoni2 — Wed, 14 Dec 2016 02:26:01 GMT

Try this

index=apiUser  earliest=-1d@d | eval timeframe=case(_time>relative_time(now(), "@d"), "Today","Yesterday") | chart count(userID) over userID by timeframe | addtotals | sort 10 -Total

Above search should give you top 10 users, bases on total count for today and yesterday. You can then compare/calculate percentage change based off column Today and Yesterday.

Update
Also try this

index=apiUser  earliest=-1d@d | eval timeframe=case(_time>relative_time(now(), "@d"), "Today","Yesterday") | chart count(userID) over userID by timeframe | sort 0 -Today

Re: How to search the top users and compare the usage stats of those users with the previous two days?

nabeel652 — Wed, 14 Dec 2016 02:37:01 GMT

you need to run the query twice, once for today's timeframes and once for yesterday's timeframes. then append the two results and use stats to combine both.

 index=apiUser   earliest=@d latest=now  | stats count(api) AS TodayCount  by userID | append [ search  index=apiUser  earliest=-1d@d latest=@d | stats count(api) AS YesterdayCount  by userID] | stats sum(TodayCount) AS TodayCount sum(YesterdayCount) AS YesterdayCount by userID| sort by -YesterdayCount | head 10

You can sort by -TodayCount and then select top 10 as well.

Re: How to search the top users and compare the usage stats of those users with the previous two days?

Vicky84 — Wed, 14 Dec 2016 23:06:12 GMT

Hi Nabeel, That is fine but I want to use the same userID which was highest today and get the stats from previous day for the SAME userID, to compare the stats from last day

Re: How to search the top users and compare the usage stats of those users with the previous two days?

nabeel652 — Wed, 14 Dec 2016 23:09:58 GMT

So when you will sort it by TodayCount and select the top 10 users, YesterdayCount column will be showing their counts from yesterday.

Re: How to search the top users and compare the usage stats of those users with the previous two days?

nabeel652 — Wed, 14 Dec 2016 23:11:18 GMT

This will do what you need, I think

 index=apiUser   earliest=@d latest=now  | stats count(api) AS TodayCount  by userID | append [ search  index=apiUser  earliest=-1d@d latest=@d | stats count(api) AS YesterdayCount  by userID] | stats sum(TodayCount) AS TodayCount sum(YesterdayCount) AS YesterdayCount by userID| sort by -TodayCount | head 10

Re: How to search the top users and compare the usage stats of those users with the previous two days?

Vicky84 — Thu, 15 Dec 2016 18:21:17 GMT

Hi Nabeel,
It gives results for today but I don't get any result for yesterday if I use this query, can you suggest if there is a way to do so.
My requirement is like :
if X & Y are the top api users for today, get their stats for today & compare from last 2 days
Report :
user (today) (yesterday) (2days earlier)
x 62334 2330 3330
y 46646 44444 414442

So, report will tell X is behaving exceptionally(his today's usage has jumped) while y is a normal usage as his trend has not changed.

Re: How to search the top users and compare the usage stats of those users with the previous two days?

nabeel652 — Thu, 15 Dec 2016 20:45:11 GMT

I am getting results for today and yesterday. For the day before (previous day) you need to append another query with different time range.

index=apiUser   earliest=@d latest=now  | stats count(api) AS TodayCount  by userID 
| append [ search  index=apiUser  earliest=-1d@d latest=@d | stats count(api) AS YesterdayCount  by userID] 
| append [ search  index=apiUser  earliest=-2d@d latest=-1d@d | stats count(api) AS PreviousDayCount  by userID]
| stats sum(TodayCount) AS TodayCount sum(YesterdayCount) AS YesterdayCount sum(PreviousDayCount) AS PreviousDayCount by userID| sort by -TodayCount | head 10

Re: How to search the top users and compare the usage stats of those users with the previous two days?

nabeel652 — Fri, 16 Dec 2016 01:52:16 GMT

Hi did it work for you?