How to see if a user has logged in every month since first event?

[sharonmok]

Hi everyone,

I have this very simple query right now to figure out which months a user has logged in and I'm trying to figure out the next step.

| stats dc(date_month) as DCmonth values(date_month) by user_company

It returns a table with the user, and which months they've logged in (e.g. Feb, Mar, Apr, May). What I want to check is whether a user has logged in every month until now since their first appearance: i.e, we are in June right now, so if a user had their first appearance in March, I want to only count them if they've logged in March, April, May, and June . If a user's first appearance is March, but they didn't log in during April and they only have Mar, May, June, they won't count.

Thanks!

[DalJeanis]

First, don't use date_month for this. That field is occasionally flaky, and doesn't contain the year, so starting there will give you structural issues in the long run.

Second, you can 't do the dc() before you've analyzed which months they logged in, so it won't help you.

Try something like this ...

your search which returns _time and user_company
| timechart span=1mon count by user_company useother=f 

Now you have one record per month, with a field named after each user_company containing that month's count for that user_company. Unfortunately, there will be count=zero records for every month before their first appearance, so we need to kill those.

Let's start by untabling the records so that we have simple events with only three fields each, _time, user_company, and count.

The untable command almost looks like magic here, because we happen to be putting things back exactly where they were originally.

The first parameter is "the one field on each event to leave alone", i.e. _time. We want each record to have the same _time as timechart put out - the start of the month.

The second parameter is "the field to put each other field's name into", i.e. user_company. The timechart will have output a bunch of fields named after each individual user_company value, like "userfoo1" and "userbar5". untable will put that field into the field we decided to call user_company, which happens to be where it originally came from. We could just as well call it MyPlaceForTheTimechartSeriesName

The last parameter is "the field to put each other field's value into", i.e. count. We could just as well call it MyPlaceForTheTimechartSeriesValue.

 | untable _time user_company count

 | rename COMMENT as "kill all records with count=0 that occur before the first records with count>0"
 | sort 0 _time user_company
 | streamstats sum(count) as sumcount by user_company
 | eval count= case(count>0 OR sumcount>0, count)
 | where isnotnull(count)

 | rename COMMENT as "now, if any records exist with count=0, kill the entire user_company"
 | eventstats min(count) as mincount by user_company
 | where mincount>0

 | rename COMMENT as "finally, count up how many consecutive months the user has logged in"
 | stats min(_time) as _time count as monthCount by user_company
 | eval firstMonth=strftime(_time,"%Y-%m")