[Service Now Logs] How to hide from the search Events with the same number whose final state is "Closed"

[JLC]

Hi Splunkers!

First time posting here, but I could really need some help. I've been meddling with Splunk for a while, and I got the gist of it. However, I've been having a bad time with this particular search condition. If you're familiar with Service Now, it creates event logs for every state change or update a ticket receives, so you have more than one log per INC (field name -> "number"). The following search allows me to see the latest "ticket" regardless of its dv_state. However, I want to "hide" the ones that are "Closed" or "Resolved". (Note: I've redacted some of the values as I consider them to be sensitive information). It's worth mentioning that the field "active" could be useful (values= "true" or "false"), but even if I put active="true", it will also show the dv_state in which this field was true (even though the latest state is "Closed").

splunk_server_group=oi source="[redacted]" sourcetype="snow:incident" number="*" short_description="[redacted]*" dv_state="*" AND dv_opened_by= "Oscar Pavon" OR "Helena Taribo" OR "Ronald Guevara" OR "Andres Penagos" OR "Matias Alcorta" OR "Agustin Gonzalez" OR "Abigail Soto" OR "Matias Alcorta" OR "Luis Huenuman"AND sys_created_by NOT "rsa.archer" NOT "Support"
| table number severity opened_at sys_updated_on dv_state dv_opened_by short_description dv_assignment_group
| sort -opened_at
| dedup number
| rename number as "INC Number", severity as "Severity", opened_at as "First Opened", sys_updated_on as "Latest Update", dv_opened_by as "Opened by", dv_assignment_group as "Assingment Group", dv_state as "Status", short_description as "Short Description"

INC Number	Severity	First Opened	Latest Update	Status	Opened By	Short Description	Assignment Group
INC1075596	3	2021-02-11 19:34:48	2021-02-11 19:56:17	New	Agustin Gonzalez	[redacted]	[redacted]
NC1071433	3	2021-02-08 14:52:55	2021-02-08 16:36:53	Resolved	Abigail Soto	[redacted]	[redacted]
...	...	...	...	...	...	...	...

Thanks!!

[scelikok]

Hi @JLC,

I couldn't get what you mean by hide not exclude, we can only hide results by filtering them. Maybe you mean in order no to filter at the beginning. Please try below, I filtered Resolved and Closed after stats.

You can save this search as a dashboard panel.

splunk_server_group=oi source="[redacted]" sourcetype="snow:incident" number="*" short_description="[redacted]*" dv_state="*" AND dv_opened_by= "Oscar Pavon" OR "Helena Taribo" OR "Ronald Guevara" OR "Andres Penagos" OR "Matias Alcorta" OR "Agustin Gonzalez" OR "Abigail Soto" OR "Matias Alcorta" OR "Luis Huenuman"AND sys_created_by NOT "rsa.archer" NOT "Support" 
| stats  latest(severity) latest(opened_at) latest(sys_updated_on) latest(dv_state) latest(dv_opened_by) latest(short_description) latest(dv_assignment_group) by number
| rename latest(*) as *
| search NOT dv_state IN ("Closed","Resolved")
| rename number as "INC Number", severity as "Severity", opened_at as "First Opened", sys_updated_on as "Latest Update", dv_opened_by as "Opened by", dv_assignment_group as "Assingment Group", dv_state as "Status", short_description as "Short Description"

[scelikok]

Hi @JLC,

Please try below, stats is more efficient;

splunk_server_group=oi source="[redacted]" sourcetype="snow:incident" number="*" short_description="[redacted]*" dv_state="*" AND dv_opened_by= "Oscar Pavon" OR "Helena Taribo" OR "Ronald Guevara" OR "Andres Penagos" OR "Matias Alcorta" OR "Agustin Gonzalez" OR "Abigail Soto" OR "Matias Alcorta" OR "Luis Huenuman"AND sys_created_by NOT "rsa.archer" NOT "Support" 
| stats  latest(severity) latest(opened_at) latest(sys_updated_on) latest(dv_state) latest(dv_opened_by) latest(short_description) latest(dv_assignment_group) by number
| rename latest(*) as *
| rename number as "INC Number", severity as "Severity", opened_at as "First Opened", sys_updated_on as "Latest Update", dv_opened_by as "Opened by", dv_assignment_group as "Assingment Group", dv_state as "Status", short_description as "Short Description"

[JLC]

Hi @scelikok ! I run this and yeah, it shows the latest (what I needed); however, I want to "hide" from the results any dv_state that is not "New" or "In Progress". Note that when I say "hide", is not excluding it from the search but rather make them "invisible".

View on my dashboard:

INC Number	Severity	First Opened	Latest Update	Status	Opened By	Short Description	Assignment Group
INC1075596	3	2021-02-11 19:34:48	2021-02-11 19:56:17	New	Agustin Gonzalez	[redacted]	[redacted]
INC1071433	3	2021-02-08 14:52:55	2021-02-08 16:36:53	Resolved	Abigail Soto	[redacted]	[redacted]
INC1075596	3	2021-02-11 19:34:48	2021-02-11 19:56:17	New	Agustin Gonzalez	[redacted]	[redacted]
...	...	...	...	...	...	...	...

What I need:

INC Number	Severity	First Opened	Latest Update	Status	Opened By	Short Description	Assignment Group
INC1075596	3	2021-02-11 19:34:48	2021-02-11 19:56:17	New	Agustin Gonzalez	[redacted]	[redacted]
INC1075596	3	2021-02-11 19:34:48	2021-02-11 19:56:17	New	Agustin Gonzalez	[redacted]	[redacted]
...	...	...	...	...	...	...	...

I forgot to mention that the final results are to be displayed on a dashboard.

[ITWhisperer]

Given that dedup take the first occurrence of each value of the field, perhaps it would be better to reverse sort by last updated time as well, before the dedup

splunk_server_group=oi source="[redacted]" sourcetype="snow:incident" number="*" short_description="[redacted]*" dv_state="*" AND dv_opened_by= "Oscar Pavon" OR "Helena Taribo" OR "Ronald Guevara" OR "Andres Penagos" OR "Matias Alcorta" OR "Agustin Gonzalez" OR "Abigail Soto" OR "Matias Alcorta" OR "Luis Huenuman"AND sys_created_by NOT "rsa.archer" NOT "Support"
| table number severity opened_at sys_updated_on dv_state dv_opened_by short_description dv_assignment_group
| sort -opened_at, -sys_updated_on
| dedup number
| where dv_status != "Closed" AND dv_status != "Resolved"
| rename number as "INC Number", severity as "Severity", opened_at as "First Opened", sys_updated_on as "Latest Update", dv_opened_by as "Opened by", dv_assignment_group as "Assingment Group", dv_state as "Status", short_description as "Short Description"

[JLC]

Hi @ITWhisperer , I tried running the search with these parameters but it didn't show any results.

[ITWhisperer]

Sorry, typo on my part

| where dv_state != "Closed" AND dv_state != "Resolved"