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