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:
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 |
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?
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