Splunk Search

Finding the Values from multiple event with a single key

Chandras11
Communicator

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.

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

@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) 

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

@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
Super Champion

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?

0 Karma

Chandras11
Communicator

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

0 Karma

Chandras11
Communicator

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.

0 Karma

493669
Super Champion

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
Communicator

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.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...