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)
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.
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
Hi did it work for you?
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
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.
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
So when you will sort it by TodayCount and select the top 10 users, YesterdayCount column will be showing their counts from yesterday.
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