topic Comparing values between two columns in Splunk Search

Comparing values between two columns

mariamathewtel — Tue, 23 Jun 2020 09:17:02 GMT

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.

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

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

Incidents	Status	Resolved Date	Closed Date
INC001	Assigned		2/5/2020
INC001	Assigned		2/5/2020
INC001	Resolved	1/5/2020	2/5/2020
INC001	Closed	1/5/2020	2/5/2020
INC002	Assigned		10/5/2020
INC002	Resolved	8/5/2020	10/5/2020
INC002	Resolved	8/5/2020	10/5/2020
INC002	Closed	8/5/2020	10/5/2020
INC003	Assigned
INC003	Pending
INC004	Assigned		22/05/2020
INC004	Assigned		22/05/2020
INC004	Assigned		22/05/2020
INC004	Resolved	15/05/2020	22/05/2020
INC004	Closed	15/05/2020	22/05/2020
INC004	Closed	15/05/2020	22/05/2020

Can someone please help me.

Re: Comparing values between two columns

ayush1906 — Tue, 23 Jun 2020 10:17:17 GMT

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

Re: Comparing values between two columns

to4kawa — Tue, 23 Jun 2020 10:45:33 GMT

| 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

Re: Comparing values between two columns

mariamathewtel — Tue, 23 Jun 2020 11:23:27 GMT

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

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

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

Incidents	Status	Resolved Date	Closed Date
INC003	Assigned
INC003	Pending
INC005	Assigned
INC005	Assigned
INC005	In Progress
INC005	Pending
INC005	Pending

Re: Comparing values between two columns

mariamathewtel — Tue, 23 Jun 2020 11:25:18 GMT

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

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

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

Incidents	Status	Resolved Date	Closed Date
INC003	Assigned
INC003	Pending
INC005	Assigned
INC005	Assigned
INC005	In Progress
INC005	Pending
INC005	Pending

Re: Comparing values between two columns

to4kawa — Tue, 23 Jun 2020 13:05:28 GMT

Incidents	Status	Resolved_Date	Closed_Date
INC001	Assigned		2/5/2020
INC001	Assigned		2/5/2020
INC001	Resolved	1/5/2020	2/5/2020
INC001	Closed	1/5/2020	2/5/2020
INC002	Assigned		10/5/2020
INC002	Resolved	8/5/2020	10/5/2020
INC002	Resolved	8/5/2020	10/5/2020
INC002	Closed	8/5/2020	10/5/2020
INC003	Assigned
INC003	Pending
INC004	Assigned		22/05/2020
INC004	Assigned		22/05/2020
INC004	Assigned		22/05/2020
INC004	Resolved	15/05/2020	22/05/2020
INC004	Closed	15/05/2020	22/05/2020
INC004	Closed	15/05/2020	22/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.

Re: Comparing values between two columns

ayush1906 — Tue, 23 Jun 2020 13:01:50 GMT

Hi @mariamathewtel

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