topic Re: Inactive users vs active users in the last 30 days in Dashboards & Visualizations

Inactive users vs active users in the last 30 days

rajkskumar — Tue, 18 Aug 2020 08:58:58 GMT

I want to build a Chart with two values. Inactive Users based on Customer ID and the Active Users who have used the service in the last 30 days.

index=prod earliest=-30d AND latest=now | eval Total = mvdedup(customerId) | timechart span=1d dc(customerId) as "Active Users"

The above code gives me the Chart for Active Users in the last 30 days. From All time data I want to get the count of Total Users and then derive the Inactive users. (Inactive = Total users - Active users). Please help me to rewrite the query to generate the required data.

Re: Inactive users vs active users in the last 30 days

FrankVl — Tue, 18 Aug 2020 10:45:34 GMT

This (ran over all-time) would get you a list of users and their status (Inactive/Active):

index=prod | stats latest(_time) as last_seen by customerId | eval status = if(last_seen > relative_time(now(), "-30d@d"),"Active","Inactive")

I'm not entirely sure what the end result is that you are after, but hope this helps you in the right direction.

Re: Inactive users vs active users in the last 30 days

rajkskumar — Tue, 18 Aug 2020 13:49:54 GMT

Hi @FrankVl,

Thank you very much for your support. This is working as expected. However, I need to add another condition to the "if" clause and I tried with the following. All the users are shown as "Inactive". Could you please point out the issue in the conditions?

index=prod | stats latest(_time) as last_seen by customerId, processName | eval status = if(processName=="Execution" AND (last_seen > relative_time(now(), "-30d@d")),"Active","Inactive") index=prod | stats latest(_time) as last_seen by customerId | eval status = if(match(processName,"Execution") AND (last_seen > relative_time(now(), "-30d@d")),"Active","Inactive")

Re: Inactive users vs active users in the last 30 days

FrankVl — Tue, 18 Aug 2020 14:25:50 GMT

In the second query, the processName field doesn't exist after the stats command, so you can't use that in the condition.

I'm not sure why the first query doesn't work. Best to investigate it by removing the eval line and look at the results of the stats command, to see if the data would actually match the condition.

To make the 'last_seen' field human readable (for easier investigation if it is within the last 30d), you can do this (at the very bottom of your query, after doing any evals/conditions that need that field):

| convert ctime(last_seen)