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!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...