How to get not closed Incidents/Tasks/Changenumber's from servicenow index in splunk

[lalitha]

I written this query in order to pull not closed tasks from service now index. but its not working.

index="servicenow" sourcetype="snow:sc_task" AND sys_class_name="sc_task"
| fillnull "UnAssigned" dv_assigned_to
| stats latest(*) as * by dv_number
| search dv_state!="Closed Complete" AND dv_state!="Closed Incomplete"
| table sys_created_on, dv_number, dv_short_description, dv_state, dv_assigned_to
| rename dv_number as "Task Ticket#",dv_assigned_to as "Assigned To",dv_short_description as "Short Description"
| sort - sys_created_on, dv_number, dv_state
| fields - sys_created_on,dv_state

Could you please help me.

[Gr0und_Z3r0]

Hi @lalitha ,

Check for field values with leading/trailing spaces and try something like this to get your result.

| makeresults count=8 
| streamstats count 
| eval dv_number = case(count=1, 1023, count=2, 39, count=3, 31, count=4, 234,count=5,112,count=6,462,count=7,627,count=8, 998 ) 
| eval dv_assigned = case(count=1 OR count=3, "James", count=2 OR count=4, "Sam",count=5 OR count=7, "Morkel",count=6 ,null(), count=8, "Stacy") 
|fillnull  value="--Unassigned--" dv_assigned
| eval dv_state = case(count=1, "Closed Complete", count=3, "Pending", count=2 OR count=4, "Closed Incomplete",count=5 OR count=7, "Closed Skipped",count=6, "Work in Progress", count=8, "Open") 
| eval status = if(like(trim(dv_state),"Closed%"),"Closed", trim(dv_state)) 
| where status!="Closed"

[lalitha]

@tscroggins I executed above query. still i am getting closed tasks in my results.  I dont want to display any tasks that has already been closed. Could you please assist me.

index="servicenow" sourcetype="snow:sc_task" sys_class_name="sc_task"AND dv_assignment_group="NETWORK-L3" AND
(dv_assigned_to="XXXXXXXXXX" OR dv_assigned_to="XXXXXXXXXX" OR dv_assigned_to="XXXXXXXXXX" OR dv_assigned_to="XXXXXXXXXX") AND
(dv_short_description!="XXXXXXXXXX" OR dv_short_description!="XXXXXXXXXX")
| fillnull value="UnAssigned" dv_assigned_to
| stats latest(*) as * by dv_number
| search dv_state!="Closed Complete" dv_state!="Closed Incomplete"

[tscroggins · Accepted solution]

@lalitha 

Did you apply @Gr0und_Z3r0's advice to your search? Your ServiceNow implementation may be adding whitespace to your display values, although that should be visible in the raw event data. You could try:

| search dv_state!="*Closed Complete*" dv_state!="*Closed Incomplete*"

[lalitha]

Thank you and much appreciated your solution.

Splunk-service-now add-on issue in our environment. Hence i couldn't able to implement recommendations.

[tscroggins]

@lalitha 

What values for dv_state to do you see after running this search?

index="servicenow" sourcetype="snow:sc_task" AND sys_class_name="sc_task"
| fillnull "UnAssigned" dv_assigned_to
| stats latest(*) as * by dv_number
| stats count by dv_state

[lalitha]

Below values are the values i am getting.

Closed Complete
Closed Incomplete
Closed Skipped
Open
Pending
Work in Progress

[tscroggins]

@lalitha 

Doe this search return results?

index="servicenow" sourcetype="snow:sc_task" sys_class_name="sc_task"
| fillnull "UnAssigned" dv_assigned_to
| stats latest(*) as * by dv_number
| search dv_state!="Closed Complete" dv_state!="Closed Incomplete"

For ServiceNow data, also recall most inputs use the sys_updated_on column for timestamp extraction. Changes to ServiceNow tables that occur in between input intervals will be missed by the input. If a task was last updated outside your search's time range, it won't be visible in your results.