Splunk Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question

Comparing values between two columns

mariamathewtel
Explorer
‎06-23-2020 02:17 AM

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)
Labels
0 Karma
Reply

to4kawa
Ultra Champion
‎06-23-2020 03:45 AM 
| 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
Reply

mariamathewtel
Explorer
‎06-23-2020 04:25 AM

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

ayush1906
Path Finder
‎06-23-2020 05:59 AM

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
Reply

to4kawa
Ultra Champion
‎06-23-2020 05:52 AM

 

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
Reply

ayush1906
Path Finder
‎06-23-2020 03:17 AM

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
Reply

mariamathewtel
Explorer
‎06-23-2020 04:23 AM

@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
Reply
Get Updates on the Splunk Community!

Webinar Recap | Revolutionizing IT Operations: The Transformative Power of AI and ML ...

The Transformative Power of AI and ML in Enhancing Observability   In the realm of IT operations, the ...

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...