Splunk Search

Help creating multiple SUMs within a field based on the value of another field

raysonjoberts
Path Finder

I am using 2 lookup tables to correlate and combine data to create a new .csv. In this process, I have a field that has numerical values in it that i want to sum based on the values of another field.  The raw data looks something like this:

stage,resource,hours
x,rick,1
x,rick,10
x,dave,1
y,rick,5
y,dave,3
y,dave,8

 

I want the output to look like:
x,rick,11
x,dave,1
y,rick,5
y,dave,11

 

Below is the search I have, it almost works but it does not sum the totals for rick and dave individually, it sums them all, so the output looks like:

x,rick,12
x,dave,12
y,rick,16
y,dave,16

 

|inputlookup mod_master
|lookup lookuptable1 Engagement OUTPUTNEW ResourceLastName,RegularHours
| eval Resource=mvdedup(ResourceLastName)
| mvexpand Resource
| eval Hours=sum(RegularHours)
| fillnull value=0 Hours
| table Stage,Resource,Hours

 

 

How can i change the logic in this search so I get the correct individual sums for Rick and Dave and not the combined total for each?

Thank you in advance!

 

Labels (2)
0 Karma

tshah-splunk
Splunk Employee
Splunk Employee

Hey @raysonjoberts,

Can you try replacing the eval function with the below line in the query please?

| stats sum(hours) as hours by stage resource

 This will do a total of hours and group it w.r.t resource and the stage.

---
If you find the answer helpful, an upvote/karma is appreciated
0 Karma

raysonjoberts
Path Finder

Thanks @tshah-splunk , but I am not only trying to sum(Hours) by stage,resource.

I am creating a table which has multiple fields in it and within that table, I am trying to sum(hours) by stage,resource. I simplified my search in my original question but the table includes multiple fields from each of the lookups I reference in the search. In other words, I am not looking to just add the hours, I want to represent the sum of those hours within a new table.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Either you're trying to do something very confusing 😄 or you can just use eventstats instead of stats to get your summarized values along the original data so you can process it further.

0 Karma

bowesmana
SplunkTrust
SplunkTrust

Can you share your data - the stats command given by @tshah-splunk would work on what you describe as your raw data, so your data is different - can you show what the data looks like after this

|inputlookup mod_master
|lookup lookuptable1 Engagement OUTPUTNEW ResourceLastName,RegularHours
0 Karma
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...