Mission Control SPL for incidents

[666Meow]

Hi all,

I'm hoping someone could help assist with refining an SPL query to extract escalation data from Mission Control. The query is largely functional (feel free to steal borrow it), but I am encountering a few issues:

Status Name Field: This field, intended to provide the status of the incident (with a default value if not specified), is currently returning blank results.
Summary and Notes Fields: These fields are returning incorrect data, displaying random strings instead of the expected information.
Escalation Priority: The inclusion of the "status" field was an attempt to retrieve escalation priority, but it is populating with a random field that does not accurately reflect the case priority (1-5).

I also tried to use the mc_investigations_lookup table but this too doesn't display current case statue or priority.
Any guidance or support in resolving these issues would be greatly appreciated.

SPL:
| mcincidents
| `get_realname(creator)`
| fieldformat create_time=strftime(create_time, "%c")
| eval _time=create_time, id=title
| `investigation_get_current_status`
| `investigation_get_collaborator_count`
| spath output=collaborators input=collaborators path={}.name
| sort -create_time
| eval age=toString(now()-create_time, "duration")
| eval new_time=strftime(create_time,"%Y-%m-%d %H:%M:%S.%N")
| eval time=rtrim(new_time,"0")
| table time, age, status, status_name, display_id, name, description, assignee, summary

[666Meow]

Thanks @livehybrid 

Converted your finding into a case to rename the numbers.
Oddly enough, when I use 'mc_incidents', I don't get any results. But I do have a working model that's almost there - it's just a bit noisy because it shows all alerts linked to a case. That's an easy fix, though; I can just export the data and do a quick pivot to tidy it up.

| mcincidents
| eval CaseNumber=display_id
| join display_id [search index=app_servicenow
| rex field=description "(?<Priority>(?<=Priority:)\s*[0-9]{1,4}|(?<=P:)\s*[0-9]{1,4})"
| rex field=description "(?<CaseNumber>ES-\d{5})"
| eval Priority=trim(Priority)
| fields display_id CaseNumber Priority
| where isnum(Priority)]
| eval Priority=coalesce(Priority, Priority)
| fieldformat create_time=strftime(create_time, "%c")
| eval _time=create_time, id=title
| spath output=collaborators input=collaborators path={}.name
| sort -create_time
| eval age=toString(now()-create_time, "duration")
| eval new_time=strftime(create_time,"%Y-%m-%d %H:%M:%S.%N")
| eval time=rtrim(new_time,"0")
| eval status_name=case(
status == "0", "Unassigned",
status == "1", "New",
status == "2", "In Progress",
status == "3", "Pending",
status == "4", "Resolved",
status == "5", "Closed",
true(), "Unknown"
)
| table time, age, status_name, CaseNumber, Priority, name, assignee


now to battle the constant SVC Limit searches being aborted (customer is aware of these)

[livehybrid · Accepted solution]

Hi @666Meow 

Looking at the `investigation_get_current_status` macro - This is expecting a JSON string for the status field - however in my tests the mcincidents command returns an number (in my case "1") for the field "status".

I found the following mappings for the numeric values which should help!

0 = Unassigned
1 = New
2 = In Progress
3 = Pending
4 = Resolved
5 = Closed

You may also find that |`mc_incidents` gives you some of the info you need - this has a "status_name" field for the status already.

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing