Need to remove duplicate data

[shashi584]

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.

shashi584_0-1626101590120.png

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

[gcusello]

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

[shashi584]

still getting incorrect data:(

[gcusello]

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

[shashi584]

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

[gcusello]

Hi @shashi584,

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

It seems to be correct and simpler than your.

Ciao.

Giuseppe

[shashi584]

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