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

[Vicky84]

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)

[nabeel652]

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.

[Vicky84]

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

[nabeel652]

Hi did it work for you?

[nabeel652]

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

[Vicky84]

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.

[nabeel652]

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

[nabeel652]

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

[somesoni2]

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