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!

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...