Splunk Search

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

Explorer

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)

Builder

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.

Explorer

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

0 Karma

Builder

Hi did it work for you?

0 Karma

Builder

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

Explorer

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.

0 Karma

Builder

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

Builder

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

0 Karma

Revered Legend

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
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!