Splunk Search

How to create cumulative field for duplicated results?

JChris_
Path Finder

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?

Labels (2)
0 Karma
1 Solution

JChris_
Path Finder

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

 

 

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

 

 

---
If this reply helps you, an upvote would be appreciated.

JChris_
Path Finder

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

 

 

0 Karma

PickleRick
Ultra Champion

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
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...