Inactive users vs active users in the last 30 days

[rajkskumar]

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.

[FrankVl · Accepted solution]

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.

[rajkskumar]

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

[FrankVl]

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)