Splunk Search

Only want most recent ticket - dedup?

Username1
Path Finder

Hey Everyone,

Everyday Splunk is ingesting a csv of information, and we are doing charts to show when/how they changed. 
The table consist if "Month","Project_Name", "Status", "Resolution", "Points", and of course "_time". So we are trying to see that if the status and resolution changes then the project_name gets points, and visually its just a stacked bar chart where the "Total Points" column stays the same but the "Gained Points" column grows over the course of the month. Basically if we have duplicate Project_Name that vary in Status and Resolution, how do we only show the row with the most recent _time 

Example: 

MonthProject_NameStatusResolutionPoints_time
1Project_DogOpenOpen22020-08-11
1Project_DogOpenOpen22020-08-12 
1Project_DogDoneDone22020-08-13 
1Project_BirdOpenOpen12020-08-12
1Project_CatOpenOpen32020-08-12
1Project_CatDoneDone32020-08-13 
1Project_BirdOpenOpen1 2020-08-13 

According to this example, Project_Dog gained 2 points for Month 1, and Project_Cat gained 3 points for Month 1. How do I get this example to show that Total Points = 6 and Gained Points = 5? 

Labels (5)
Tags (2)
0 Karma

soutamo
SplunkTrust
SplunkTrust

Hi

I'm not sure if I understand your request correctly, but at least this give your wanted result.

index=_internal 
| head 1
| eval _raw="Month,	Project_Name,	Status,	Resolution,	Points,	time
1,	Project_Dog,	Open,	Open,	2,	2020-08-11
1,	Project_Dog,	Open,	Open,	2,	2020-08-12 
1,	Project_Dog,	Done,	Done,	2,	2020-08-13 
1,	Project_Bird,	Open,	Open,	1,	2020-08-12
1,	Project_Cat,	Open,	Open,	3,	2020-08-12
1,	Project_Cat,	Done,	Done,	3,	2020-08-13 
1,	Project_Bird,	Open,	Open,	1,	2020-08-13"
| multikv forceheader=1
| eval Points = tonumber(trim(Points)), Resolution=trim(Resolution)
| rename COMMENTS as "Previous generate sample data"
| eventstats max(Points) as maxPoints by Project_Name
| stats max(maxPoints) as Gained max(Points) as totalPoints last(time) as Time values(Month) as Month last(Resolution) as Resolution last(Status) by Project_Name
| eventstats sum(totalPoints) as totalPoints
| where Resolution=="Done"
| stats values(totalPoints) as totalPoints sum(Gained) as Gained
0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!