How to show multiple values in one value

[aditsss]

Hi All,

I have one requirement:

I have one lookup where there is one column Case_Status. It has multiple values  for Case status:

Resolved

Closed- Resolved

Resolved - UpdateCase

Submitted

Pending 

Escalated

My requirement is I need only two values that is open and closed

I need to include Resolved submitted and pending in OPEN and Escalated, Resolved and Resolved Update Case in Closed.

How can I achieve this.

My current query:

|inputlookup Sdf.csv| table CaseStatus | dedup CaseStatus

[richgalloway]

You could add a column to the lookup, but it might be easiest to map the status values using a case statement.

|inputlookup Sdf.csv
| fields CaseStatus 
| dedup CaseStatus
| eval CaseStatus=case(CaseStatus="Resolved" OR CaseStatus="Submitted" OR CaseStatus="Pending", "OPEN", CaseStatus="Escalated" OR CaseStatus="Closed- Resolved" OR CaseStatus="Resolved - UpdateCase", "Closed", 1==1,CaseStatus)
| table CaseStatus

[aditsss]

@richgalloway 

I tried like this:

|inputlookup mnr_rally_defects.csv| sort -rundatetime| dedup state| eval state=case(state="Submitted" OR state="Fixed" OR state="Open", "OPEN", state="Closed" "Closed", 1==1,state)|table rundatetime state

I am getting error as :

Error in 'eval' command: The expression is malformed. Expected ).

[scelikok · Accepted solution]

Hi @aditsss,

You have missing comma, please try below;

| inputlookup mnr_rally_defects.csv 
| sort -rundatetime 
| dedup state 
| eval state=case(state="Submitted" OR state="Fixed" OR state="Open", "OPEN", state="Closed","Closed", 1==1,state) 
| table rundatetime state

If this reply helps you an upvote is appreciated.

[aditsss]

@scelikok 

I want to merge three columns submitted,fixed and open as one column Open and closed and resolved as closed.All these columns contains numeric values.

Will the below query correct.

[scelikok]

@aditsss,

The query is only missing resolved state, I added below. But I couldn't understand "All these columns contains numeric values". I think you meant about some other columns since state columns are string in your search. If you can post some sample data, we can have better recommendation.

| inputlookup mnr_rally_defects.csv 
| sort -rundatetime 
| dedup state 
| eval state=case(state="Submitted" OR state="Fixed" OR state="Open", "OPEN", state="Closed" OR state="Resolved","Closed", 1==1,state) 
| table rundatetime state

If this reply helps you an upvote is appreciated.

[FelixLeh]

When using case end the brackets with

 ,true(), "exampleValue")

as an option if none of your previous cases are True

(I'm relatively new to Splunk so take my thoughts with a grain of salt)

[aditsss]

@richgalloway 

Can you guide me on this.

[aditsss]

@richgalloway 

I want to display two columns OPEN and Closed in a table .

I want to include Submitted,Fixed in OPEN column and Closed,Resolved in  Closed column.

Can you guide me on this.

[aditsss]

@richgalloway 

Please guide me on this.

[bapun18]

Hi You can try,
Something like |stats first(CaseStatus) as Case_Status

or
|stats min(CaseStatus) as Case_Status 

even try |stats value(*) AS * by CaseStatus if that help

[aditsss]

@bapun18 

How can I include my values in Two fields:

I want to create one field open and want to include submitted pending in OPEN and Rest in closed.

Can you guide me on that.