Splunk Search

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

Vicky84
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)

nabeel652
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.

Vicky84
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

nabeel652
Builder

Hi did it work for you?

0 Karma

nabeel652
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

Vicky84
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

nabeel652
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

nabeel652
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

somesoni2
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
Get Updates on the Splunk Community!

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...