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!
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.
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.
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.
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