How to create cumulative field for duplicated results?

[JChris_]

I have the following events in splunk:

company,name,email,status
Acme,John Doe,john.doe@example.com,inactive
Company Inc.,John Doe,john.doe@example.com,active
HelloWorld Inc.,John Doe,john.doe@example.com,inactive
Contoso,John Doe,john.doe@example.com,inactive
Contoso,Mary Doe,mary.doe@example.com,inactive
HelloWorld Inc.,Mary Doe,mary.doe@example.com,inactive

I want to create a new field called "cumulativeStatus" that will be "active" if that email is active in at least one row, and will be "inactive" if the person is inactive in all rows. Like this:

company,name,email,status,cumulativeStatus
Acme,John Doe,john.doe@example.com,inactive,active
Company Inc.,John Doe,john.doe@example.com,active,active
HelloWorld Inc.,John Doe,john.doe@example.com,inactive,active
Contoso,John Doe,john.doe@example.com,inactive,active
Contoso,Mary Doe,mary.doe@example.com,inactive,inactive
HelloWorld Inc.,Mary Doe,mary.doe@example.com,inactive,inactive

Is it possible, how?

[richgalloway]

You should be able to do that using eventstats, but first the status field will need to be converted into a number.  Then the cumulativeStatus is just the max of that number.

| eval statusNum = case(status=active,1, status=inactive, 0, 1==1,-1)
| eventstats max(statusNum) as cumulativeStatus by email
```Convert cumulativeStatus back to text```
| eval cumulativeStatus = case(cumulativeStatus=0,"inactive", cumulativeStatus=1,"active", 1==1,"unknown")
| fields - statusNum

[JChris_ · Accepted solution]

Thanks, I ended up using a slightly modified version of yours and it worked great:

| eval StatusCode = if(Status == "ACTIVE", 1, 0) 
| eventstats max(StatusCode) as CumulativeStatus by Email 
| eval CumulativeStatus = if(CumulativeStatus == 1, "ACTIVE", "INACTIVE") 

[PickleRick]

I think you can skip "encoding" the active/inactive values. Just use

streamstats count(eval(status="active")) by email

To see whether there is a positive number of active statuses.

It boils down to the same operation, but does not involve adding additional artificial field.

[richgalloway]

That is cleaner, but I'd use max rather than count.  The count function will return the number of zeroes and ones were returned by eval, which always will be the same as the number of events.  max, however, will tell you if there was a one ("active") or not. 

You'll still have to map the numeric stats result to the cumulativeStatus string, however.