topic Re: How to only display the lowest value of field per group in Splunk Search

How to only display the lowest value of field per group

tkwaller_2 — Sun, 18 Feb 2018 17:25:18 GMT

Hello

I am tabling a bunch of data. In the table there is a field called Workflow Sort Order which orders the the data within the logs:

"Name"     "Vendor" "EngagementScope"    "Workflow Step Sort Order"      "Step Due Date"  
 AA    Vendor1  TestEngagementScope1    0    2018-02-15 20:38:10.154000      
 AA    Vendor1  TestEngagementScope1   1    2018-03-01 20:38:10.154000
 AA    Vendor1  TestEngagementScope1    2    2018-03-08 20:38:10.154000
 AA    Vendor1 TestEngagementScope1    3    2018-03-15 20:38:10.154000
 AA    Vendor1  TestEngagementScope1    4    2018-03-22 20:38:10.154000



AB    Vendor2 TestEngagementScope1      1    2018-02-15 20:38:10.154000      
AB    Vendor2  TestEngagementScope1      2    2018-03-01 20:38:10.154000
AB    Vendor2  TestEngagementScope1     3    2018-03-08 20:38:10.154000

What I would like to do is eval or something to only show the lowest value of "Workflow Step Sort Order" for each "Name"
Thanks for the help!!

Re: How to only display the lowest value of field per group

gcusello — Sun, 18 Feb 2018 17:30:43 GMT

Hi tkwaller_2,
try something like this:

Your_search
| stats values(Vendor) AS Vendor values(EngagementScope) AS EngagementScope earliest("Workflow Step Sort Order") AS "Workflow Step Sort Order" values("Step Due Date") AS "Step Due Date" BY Name

Bye.
Giuseppe

Re: How to only display the lowest value of field per group

493669 — Sun, 18 Feb 2018 17:35:20 GMT

Try this:

...|stats min(Workflow Step Sort Order) as min by Name

Re: How to only display the lowest value of field per group

tkwaller_2 — Sun, 18 Feb 2018 17:49:50 GMT

Being more specific with more data
In the table above the only events that need to be returned are :

"Name" "Vendor" "EngagementScope" "Workflow Step Sort Order" "Step Due Date"

AA Vendor1 TestEngagementScope1 0 2018-02-15 20:38:10.154000

AB Vendor2 TestEngagementScope1 1 2018-02-15 20:38:10.154000

The table CURRENTLY consists of many fields, I excluded these from the above as it wasnt necessarily important but I still need the fields in the table.:

| table Service Vendor EngagementScope "Assessment Assignee" "Assessment Assignee Email Address" Phone LOB AssessmentName "Assessment Type" "Assessment Status" "Past Due Step Name" "Past Due Step Due Date" "SLA for Past Due Step" "Days step is past due"  "Cumulative Due Date" CumulativeActualDaysLate "Assessment Start Date" "Projected Completion Date" "Total Projected Late"  "Workflow Step Sort Order"

So the table would essentially be
"Name" "Vendor" "EngagementScope" "Workflow Step Sort Order" "Step Due Date"

AA Vendor1 TestEngagementScope1 0 2018-02-15 20:38:10.154000 ...
AB Vendor2 TestEngagementScope1 1 2018-02-15 20:38:10.154000 ...

but again I only need the records that are the lowest value of the field "Workflow Step Sort Order" per "Name" "Vendor" "EngagementScope"

Re: How to only display the lowest value of field per group

somesoni2 — Sun, 18 Feb 2018 18:19:42 GMT

Try like this

your current search giving table in question
| eventstats min("Workflow Step Sort Order") as min by Name
| where min='Workflow Step Sort Order' | fields - min

 your current search giving table in question
| sort "Workflow Step Sort Order" by Name
| dedup Name