Splunk Search

Comparing values between two columns

mariamathewtel
Explorer

Hi, 
I have a table like below where multiple entries of same ticket numbers are displaying as these are taken from the logs receiving from the ticketing system. 

IncidentsStatusResolved DateClosed Date
INC001Assigned  
INC001Assigned  
INC001Resolved1/5/20202/5/2020
INC001Closed1/5/20202/5/2020
INC002Assigned  
INC002Resolved8/5/2020 
INC002Resolved8/5/2020 
INC002Closed8/5/202010/5/2020
INC003Assigned  
INC003Pending  
INC004Assigned  
INC004Assigned  
INC004Assigned  
INC004Resolved15/05/2020 
INC004Closed15/05/202022/05/2020
INC004Closed15/05/202022/05/2020

 

If the ticket is actually closed, I want to fill the closed date column of each incident with the actual closed date.  

IncidentsStatusResolved DateClosed Date
INC001Assigned 2/5/2020
INC001Assigned 2/5/2020
INC001Resolved1/5/20202/5/2020
INC001Closed1/5/20202/5/2020
INC002Assigned 10/5/2020
INC002Resolved8/5/202010/5/2020
INC002Resolved8/5/202010/5/2020
INC002Closed8/5/202010/5/2020
INC003Assigned  
INC003Pending  
INC004Assigned 22/05/2020
INC004Assigned 22/05/2020
INC004Assigned 22/05/2020
INC004Resolved15/05/202022/05/2020
INC004Closed15/05/202022/05/2020
INC004Closed15/05/202022/05/2020

 

Can someone please help me.

Labels (4)
0 Karma

to4kawa
Ultra Champion
| makeresults
| eval _raw="Incidents	Status	Resolved_Date	Closed_Date
INC001	Assigned	 	 
INC001	Assigned	 	 
INC001	Resolved	1/5/2020	2/5/2020
INC001	Closed	1/5/2020	2/5/2020
INC002	Assigned	 	 
INC002	Resolved	8/5/2020	 
INC002	Resolved	8/5/2020	 
INC002	Closed	8/5/2020	10/5/2020
INC003	Assigned	 	 
INC003	Pending	 	 
INC004	Assigned	 	 
INC004	Assigned	 	 
INC004	Assigned	 	 
INC004	Resolved	15/05/2020	 
INC004	Closed	15/05/2020	22/05/2020
INC004	Closed	15/05/2020	22/05/2020"
| multikv forceheader=1
| table Incidents	Status	Resolved_Date	Closed_Date
| rename COMMENT as "the logic"
| eventstats values(eval(if(Status="Closed",Closed_Date,NULL))) as Closed_Date by Incidents
0 Karma

mariamathewtel
Explorer

@to4kawa 

Thanks for the reply. 
I did not get the required results from your query. 

I will modify my requirement little bit as this will be more easier , I guess....

From the table -

IncidentsStatusResolved DateClosed Date
INC001Assigned  
INC001Assigned  
INC001Resolved1/5/20202/5/2020
INC001Closed1/5/20202/5/2020
INC002Assigned  
INC002Resolved8/5/2020 
INC002Resolved8/5/2020 
INC002Closed8/5/202010/5/2020
INC003Assigned  
INC003Pending  
INC004Assigned  
INC004Assigned  
INC004Assigned  
INC004Resolved15/05/2020 
INC004Closed15/05/202022/05/2020
INC004Closed15/05/202022/05/2020
INC005Assigned  
INC005Assigned  
INC005In Progress  
INC005Pending  
INC005Pending  

If the incident is  Resolved or Closed in any of the entries, remove all entries for that incident. 

OR, i want to have incidents which are not resolved or closed .

Required Table

IncidentsStatusResolved DateClosed Date
INC003Assigned  
INC003Pending  
INC005Assigned  
INC005Assigned  
INC005In Progress  
INC005Pending  
INC005Pending  
0 Karma

ayush1906
Path Finder

Hi @mariamathewtel 

 

 

| makeresults
| eval _raw="Incidents	Status	Resolved_Date	Closed_Date
INC001	Assigned	 	 
INC001	Assigned	 	 
INC001	Resolved	1/5/2020	2/5/2020
INC001	Closed	1/5/2020	2/5/2020
INC002	Assigned	 	 
INC002	Resolved	8/5/2020	 
INC002	Resolved	8/5/2020	 
INC002	Closed	8/5/2020	10/5/2020
INC003	Assigned	 	 
INC003	Pending	 	 
INC004	Assigned	 	 
INC004	Assigned	 	 
INC004	Assigned	 	 
INC004	Resolved	15/05/2020	 
INC004	Closed	15/05/2020	22/05/2020
INC004	Closed	15/05/2020	22/05/2020"
| multikv forceheader=1
| table Incidents Status Resolved_Date Closed_Date
| search Status != "Resolved" AND Status != "Closed"

 

 

Hint: If the query is not returning any result in the table command(2nd last line) remove the spaces..they are getting recognized as <tab>, manually give space. 

I believe this resolves your part to display only the cases which are not resolved and closed.

 

Please accept this as answer if it works for you 

0 Karma

to4kawa
Ultra Champion

 

Incidents StatusResolved_Date  Closed_Date
INC001Assigned 2/5/2020
INC001Assigned 2/5/2020
INC001Resolved1/5/20202/5/2020
INC001Closed1/5/20202/5/2020
INC002Assigned 10/5/2020
INC002Resolved8/5/202010/5/2020
INC002Resolved8/5/202010/5/2020
INC002Closed8/5/202010/5/2020
INC003Assigned  
INC003Pending  
INC004Assigned 22/05/2020
INC004Assigned 22/05/2020
INC004Assigned 22/05/2020
INC004Resolved15/05/202022/05/2020
INC004Closed15/05/202022/05/2020
INC004Closed15/05/202022/05/2020

>I did not get the required results from your query. 

Here is the result of my query. what's the problem?
your first require is this.

| makeresults
| eval _raw="Incidents	Status	Resolved_Date	Closed_Date
INC001	Assigned	 	 
INC001	Assigned	 	 
INC001	Resolved	1/5/2020	2/5/2020
INC001	Closed	1/5/2020	2/5/2020
INC002	Assigned	 	 
INC002	Resolved	8/5/2020	 
INC002	Resolved	8/5/2020	 
INC002	Closed	8/5/2020	10/5/2020
INC003	Assigned	 	 
INC003	Pending	 	 
INC004	Assigned	 	 
INC004	Assigned	 	 
INC004	Assigned	 	 
INC004	Resolved	15/05/2020	 
INC004	Closed	15/05/2020	22/05/2020
INC004	Closed	15/05/2020	22/05/2020
INC005	Assigned	 	 
INC005	Assigned	 	 
INC005	In Progress	 	 
INC005	Pending	 	 
INC005	Pending"
| multikv forceheader=1
| table Incidents	Status	Resolved_Date	Closed_Date
| rename COMMENT as "the logic"
| eventstats count(eval(if(Status="Closed",Closed_Date,NULL))) as Closed count(eval(if(Status="Resolved",Closed_Date,NULL))) as Resolved by Incidents
| where Closed = 0 AND Resolved = 0
| fields - Closed Resolved

this query is totally different.

0 Karma

ayush1906
Path Finder

hi mariamathewtel,

 

You can try implementing JavaScript on the Closed date column, and the output be saved to a lookup since you cannot change the already indexed data.

Check you this link for JS implementation of the text box: How to add a textbox as a cell in a Splunk table

0 Karma

mariamathewtel
Explorer

@ayush1906 Thanks for the reply. 
I am a newbie to splunk and not much hands on in JS.

I will modify my requirement little bit as this will be more easier , I guess....

From the table -

IncidentsStatusResolved DateClosed Date
INC001Assigned  
INC001Assigned  
INC001Resolved1/5/20202/5/2020
INC001Closed1/5/20202/5/2020
INC002Assigned  
INC002Resolved8/5/2020 
INC002Resolved8/5/2020 
INC002Closed8/5/202010/5/2020
INC003Assigned  
INC003Pending  
INC004Assigned  
INC004Assigned  
INC004Assigned  
INC004Resolved15/05/2020 
INC004Closed15/05/202022/05/2020
INC004Closed15/05/202022/05/2020
INC005Assigned  
INC005Assigned  
INC005In Progress  
INC005Pending  
INC005Pending  

If the incident is  Resolved or Closed in any of the entries, remove all entries for that incident. 

OR, i want to have incidents which are not resolved or closed .

Required Table

IncidentsStatusResolved DateClosed Date
INC003Assigned  
INC003Pending  
INC005Assigned  
INC005Assigned  
INC005In Progress  
INC005Pending  
INC005Pending  

 

0 Karma
Get Updates on the Splunk Community!

Splunk is Nurturing Tomorrow’s Cybersecurity Leaders Today

Meet Carol Wright. She leads the Splunk Academic Alliance program at Splunk. The Splunk Academic Alliance ...

Part 2: A Guide to Maximizing Splunk IT Service Intelligence

Welcome to the second segment of our guide. In Part 1, we covered the essentials of getting started with ITSI ...

Part 1: A Guide to Maximizing Splunk IT Service Intelligence

As modern IT environments continue to grow in complexity and speed, the ability to efficiently manage and ...