Aggregate Search Terms in Timelion to Create Percent Of User Who Performed Actions Chart

[Clint_Gossett]

I'm trying to use Timelion to produce a chart that looks something similar to what I've illustrated below.

A basic query for Event (A) and (B) is as follows.

.es(q=activityType.keyword:LOGIN, index='audit_logs_', timefield='createdOn', metric=cardinality:userName.keyword).label('Users Who Logged into Control Room').color("darkgrey").bars(width=15, stack=false), .es(q=activityType.keyword:CLIENT_LOGIN, index='audit_logs_', timefield='createdOn', metric=cardinality:userName.keyword).bars(stack=false, width=4).label('Users Who Logged into Client').color("blue")

I assume I can use "stack=true" to stack each % once I calculate them. How do I create an .es() query that says something to the effect of.

(cardinary of users who are in A and NOT B) / (cardinary of users who are in A OR B)
(cardinary of users who are in B and NOT A) / (cardinary of users who are in A OR B)
(cardinary of users who are in A and B) / (cardinary of users who are in A OR B)

Thanks for any guidance someone can provide.

[Brandon_Kobel]

You can use something like the following to get users who are only in A:

.es(q="activityType.keyword:CLIENT_LOGIN AND !activityType.keyword:LOGIN", index='audit_logs_', timefield='createdOn', metric=cardinality:userName.keyword).bars(stack=true, width=4).label('Users Who Logged into only Client').color("green")

and something like the following to get user who are in A and B:

.es(q="activityType.keyword:CLIENT_LOGIN AND activityType.keyword:LOGIN", index='audit_logs_', timefield='createdOn', metric=cardinality:userName.keyword).bars(stack=true, width=4).label('Users Who Logged into only Client').color("green")

[Clint_Gossett]

Brandon, this is extremely helpful. I've created a query using the syntax you suggested, but the results I'm getting just don't seem to add up. If you look at the chart below, each day should not aggregate over 100. In addition, it's hard to believe we have zero clients who logged into both systems in the same day.

.es(q="activityType.keyword:CLIENT_LOGIN AND !activityType.keyword:LOGIN", index='audit_logs_*', timefield='createdOn', metric=cardinality:userName.keyword).label('%Users Who Logged Into CLIENT and not CR').color("blue").bars(stack=true, width=15).divide( .es(q="activityType.keyword:LOGIN OR activityType.keyword:CLIENT_LOGIN", index='audit_logs_*', timefield='createdOn', metric=cardinality:userName.keyword) ).multiply(100), .es(q="!activityType.keyword:CLIENT_LOGIN AND activityType.keyword:LOGIN", index='audit_logs_*', timefield='createdOn', metric=cardinality:userName.keyword).label('%Users Who Logged Into CR and not CLIENT').color("green").bars(stack=true, width=15).divide( .es(q="activityType.keyword:LOGIN OR activityType.keyword:CLIENT_LOGIN", index='audit_logs_*', timefield='createdOn', metric=cardinality:userName.keyword) ).multiply(100), .es(q="activityType.keyword:LOGIN AND activityType.keyword:CLIENT_LOGIN", index='audit_logs_*', timefield='createdOn', metric=cardinality:userName.keyword).label('%Users Who Logged Into CR and CLIENT').color("red").bars(stack=true, width=15).divide( .es(q="activityType.keyword:LOGIN OR activityType.keyword:CLIENT_LOGIN", index='audit_logs_*', timefield='createdOn', metric=cardinality:userName.keyword)).multiply(100)