topic Re: Creating multiple SUMs within a field based on the value of another field in Splunk Search

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

raysonjoberts — Tue, 08 Mar 2022 05:07:48 GMT

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

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!

Re: Creating multiple SUMs within a field based on the value of another field

tshah-splunk — Tue, 08 Mar 2022 04:15:42 GMT

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.

Re: Creating multiple SUMs within a field based on the value of another field

raysonjoberts — Tue, 08 Mar 2022 04:30:14 GMT

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.

Re: Creating multiple SUMs within a field based on the value of another field

bowesmana — Tue, 08 Mar 2022 06:43:29 GMT

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

Re: Creating multiple SUMs within a field based on the value of another field

PickleRick — Tue, 08 Mar 2022 09:03:40 GMT

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.