topic Re: Need to remove duplicate data in Splunk Search

Need to remove duplicate data

shashi584 — Mon, 12 Jul 2021 14:58:32 GMT

We have 3 different (Active,Closed,Resolved) records for same Incident and we need to retrieve only Active incident record and Incident shouldn't have any other status records such as Closed,Resolved.

Below query is still showing Active Incident record, however Incident is already in resolved status...

index="snow" sourcetype="snow:incident" source="https://dell.service-now.com/"
dv_assignment_group = "ITOPS-DCE-SELLER-SUPPORT"
dv_u_cim_true="true"
| where like(dv_incident_state,"Active") AND NOT like (dv_incident_state,"Resolved") AND NOT like (dv_incident_state,"Closed")
| dedup dv_incident_state
| stats count by dv_incident_state, dv_number,dv_active

Re: Need to remove duplicate data

gcusello — Mon, 12 Jul 2021 15:10:06 GMT

Hi @shashi584,

please try something like this:

index="snow" sourcetype="snow:incident" source="https://dell.service-now.com/" dv_assignment_group = "ITOPS-DCE-SELLER-SUPPORT" dv_u_cim_true="true" | stats dc(dv_incident_state) AS dc_incident_state values(dv_incident_state) AS dv_incident_state BY dv_number | where dc_incident_state=1 AND dv_incident_state="Active" | table dv_number dv_incident_state

Ciao.

Giuseppe

Re: Need to remove duplicate data

shashi584 — Fri, 16 Jul 2021 09:50:18 GMT

still getting incorrect data:(

Re: Need to remove duplicate data

gcusello — Fri, 16 Jul 2021 14:33:37 GMT

Hi @shashi584,

which kind of wrong results have you?

with the above search you have all the dv_numbers where there are the following conditions:

only one dv_incident_state,
dv_incident_state is "Active"

these are the conditions you requested.

Ciao.

Giuseppe

Re: Need to remove duplicate data

shashi584 — Fri, 16 Jul 2021 15:14:28 GMT

Don't know how but surprisingly it's working now using below query.. Thanks for your help..

index="snow" sourcetype="snow:incident" source="https://dell.service-now.com/"
dv_assignment_group = "ITOPS-DCE-SELLER-SUPPORT"
dv_u_cim_true="true" dv_active="true"
| where like(dv_incident_state,"Active") AND NOT like (dv_incident_state,"Resolved") AND NOT like (dv_incident_state,"Closed")
| dedup dv_incident_state
| stats count by dv_incident_state, dv_number,dv_active

Re: Need to remove duplicate data

gcusello — Fri, 16 Jul 2021 15:22:05 GMT

Hi @shashi584,

did you tried to understand why my one isn't working?

It seems to be correct and simpler than your.

Ciao.

Giuseppe

Re: Need to remove duplicate data

shashi584 — Fri, 16 Jul 2021 16:34:58 GMT

We have two different Assignment groups (ITOPS-DCE-SELLER-MONITORING and ITOPS-DCE-SELLER-SUPPORT), with your query can see only active Incidents but assignment group is "ITOPS-DCE-SELLER-MONITORING" even though we included (dv_assignment_group = "ITOPS-DCE-SELLER-SUPPORT").. No idea why it's still not working

index="snow" sourcetype="snow:incident" source="https://dell.service-now.com/"
dv_assignment_group = "ITOPS-DCE-SELLER-SUPPORT"
dv_u_cim_true="true"
| stats dc(dv_incident_state) AS dc_incident_state values(dv_incident_state) AS dv_incident_state BY dv_number
| where dc_incident_state=1 AND dv_incident_state="Active"
| table dv_number dv_incident_state