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.

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