Finding the Values from multiple event with a single key

[Chandras11]

Hi Everyone,

Through inputcsv and outputcsv, i finally able to consolidate the data in a single CSV. Now I need to create another CSV with the events with a single key. My inputcsv has data in the following format:-

 Ticket  |   Main_Ticket |  TYPE  | STATUS  |   END_DATE
  A2345A    | A2345A   |     MAIN    |   IN Work  | 2018-09-12
  A2345A001  | A2345A     |   SUB    |   Closed   | 2018-07-03
  Z2995A    | A2345A     |   PEER   |   Closed   |  2018-07-23
  A2345A002 |  A2345A   |    SUB    |   In Work   |2018-08-12
  B4321A    | B4321A   |     MAIN    |   IN Work  | 2018-09-12
  B4321A001  | B4321A     |   SUB    |   Closed   | 2018-07-11
  Z3495A    | B4321A     |   PEER   |   Closed   |  2018-07-19
  B4321A002 |  B4321A   |    SUB    |   In Work   |2018-08-15
  C9854A    | C9854A   |     MAIN    |   Closed  | 2018-07-12
  C9854A001  | C9854A     |   SUB    |   Closed   | 2018-06-15
  Z8749A    | C9854A     |   PEER   |  Closed   |  2018-06-23
  C9854A002 |  C9854A   |    SUB    |   Closed   |2018-06-29

Now I need to find the first "In Work"SUB or PEER ticket for each Main_Ticekt. The result may look like the following.

Main_Ticket | PEER_SUB_IN_Work | PEER_SUB_TN |  PEER_SUB_ENDDATE
A2345A  | SUB  | A2345A002 | 2018-08-12
B4321A | PEER | Z3495A | 018-08-12
C9854A | Closed | Closed |  2018-07-12

So I am trying to get something like for each loop with the key filed "Main_Ticket" , find last IN Work SUB/PEER ticket and END_Date of that SUB/PEER TIcket. If all SUB/PEER tickets are CLOSED, It should return the MAIN Ticket Type value. Once I have the values, I can easily save it to outputcsv.

As of now, I am downloading the CSVs and doing it with VB. Is it possible with Splunk?

Thanks for your help and I apologize for such a long post.

[DalJeanis · Accepted solution]

@Chandras11 - Here's run-anywhere code for your sample data with one potential solution...

|makeresults | eval mydata="A2345A,A2345A,MAIN,IN Work,2018-09-12!!!!A2345A001,A2345A,SUB,Closed,2018-07-03!!!!Z2995A,A2345A,PEER,Closed,2018-07-23!!!!A2345A002,A2345A,SUB,In Work,2018-08-12!!!!B4321A,B4321A,MAIN,IN Work,2018-09-12!!!!B4321A001,B4321A,SUB,Closed,2018-07-11!!!!Z3495A,B4321A,PEER,Closed,2018-07-19!!!!B4321A002,B4321A,SUB,In Work,2018-08-15!!!!C9854A,C9854A,MAIN,Closed,2018-07-12!!!!C9854A001,C9854A,SUB,Closed,2018-06-15!!!!Z8749A,C9854A,PEER,Closed,2018-06-23!!!!C9854A002,C9854A,SUB,Closed,2018-06-29"
| makemv delim="!!!!" mydata
| mvexpand mydata
| makemv delim="," mydata
| eval Ticket=mvindex(mydata,0)
| eval Main_Ticket=mvindex(mydata,1)
| eval TYPE=mvindex(mydata,2)
| eval STATUS=mvindex(mydata,3)
| eval END_DATE=mvindex(mydata,4)
| table Ticket Main_Ticket TYPE STATUS END_DATE
| rename COMMENT as "The above just enters your test data"

| rename COMMENT as "Get rid of all closed sub-items, calculate the last sub date for open items"
| where TYPE="MAIN" OR STATUS != "Closed"
| eventstats max(eval(case(TYPE!="MAIN",END_DATE))) as MaxSubEnd by Main_Ticket

| rename COMMENT as "Allow closed main items, or non-closed non-main items that have the latest end date for their main ticket."
| where isnull(MaxSubEnd) OR (TYPE!="MAIN" AND MaxSubEnd=END_DATE) 

[493669]

not sure if I understand correctly..
for main ticket B4321A in result it should be B4321A002 in PEER_SUB_IN_Work if you wanted last IN Work SUB/PEER ticket ..is it correct?

[Chandras11]

Yes... Thats true.... The Last "In work" ticket. Because that will be the actual ticket phase for each main ticket.

[Chandras11]

Well, that's a good idea... I can remove all closed ticket if the Main ticket is closed. Afterward, there is no need to check for example C9854A here. Just need to check the Main_TIckets in status "IN Work" and then find the last "IN Work" SUB or PEER Ticket.

[493669]

yes
so you can try

...|where STATUS!="Closed"|stats last(Main_Ticket) as Main,last(Ticket) as Ticket , last(TYPE) as TYPE ,last(END_DATE) as END_DATE  by Main_Ticket

OR

...|where STATUS="In Work"|stats last(Main_Ticket) as Main,last(Ticket) as Ticket , last(TYPE) as TYPE ,last(END_DATE) as END_DATE  by Main_Ticket

[Chandras11]

Thanks for the answer. It worked. However, I have a very small doubt here. In the stats - by can we also sort the events based on the end date. For example, it should automatically sort based on PEER/SUB Ticket End Date and then take the Last value from there.