topic Re: How do I edit my eval statement to insert a specified number in ColumnB based on specific values found in ColumnA? in Splunk Search

How do I edit my eval statement to insert a specified number in ColumnB based on specific values found in ColumnA?

IRHM73 — Wed, 11 Nov 2015 07:57:08 GMT

Hi, I wonder whether someone may be able to help me please.

I'm trying to run a search which looks at a value in column A (detail.overall), and if they contain specific values, then insert a specific value into the "Rating" column or zero.

So if the detail.overall column is:

Very Satisfied - Insert the value of 100 in the "Ratings" Column
Satisfied - Insert the value of 75 in the "Ratings" Column
Neither - Insert the value of 50 in the "Ratings" Column
Dissatisfied - Insert the value of 25 in the "Ratings" Column
Very Dissatisfied - Insert the value of 0 in the "Ratings" Column

If none of the values are found, I'd like to insert a zero.

This is the search I've put together so far:

index=main auditSource=frontend auditType=Survey detail.overall!="None" | replace frontend with "Overall Satisfaction" | replace 1 with "Very Satisfied", 2 with "Satisfied", 3 with "Neither", 4 with "Dissatisfied", 5 with "Very Dissatisfied" | chart count by detail.overall | addcoltotals|fillnull value="Total" count | eval Rating = if(match(detail.overall, "5"), "100", "0")

There are two problems I have:

[] Using the value "Very Satisfied", I'm unable to insert the value of 100 in the "Ratings" column
[] I'm unsure how to create a nested if statement to take into all of the text values.

I just wondered whether someone may be able to look at this please and offer some guidance on how I may be able to achieve this.

Many thanks and kind regards

Chris

Re: How do I edit my eval statement to insert a specified number in ColumnB based on specific values found in ColumnA?

HeinzWaescher — Wed, 11 Nov 2015 09:19:02 GMT

I think you need the case command here:

...
| eval rating=case(detail.overall="Very Satisfied", 100, detail.overall="Satisfied", 75 , detail.overall="Neither", 50, detail.overall="Dissatisfied", 25, detail.overall="Very Dissatisfied", 0 , 1=1, 0)

Re: How do I edit my eval statement to insert a specified number in ColumnB based on specific values found in ColumnA?

IRHM73 — Wed, 11 Nov 2015 09:30:22 GMT

Hi @HeinzWaescher, thank you very much for taking the time to reply to my post.

I've tried your solution but unfortunately this doesn't work. I'm not sure whether this is because the solution is the opposite way around to my original post.

I'd like if possible to insert the value into the Ratings column if:

The detail.overall column value:

Many thanks and kind regards

Chris

Re: How do I edit my eval statement to insert a specified number in ColumnB based on specific values found in ColumnA?

HeinzWaescher — Wed, 11 Nov 2015 09:36:47 GMT

I mixed it up. Try my updated answer.

Re: How do I edit my eval statement to insert a specified number in ColumnB based on specific values found in ColumnA?

IRHM73 — Wed, 11 Nov 2015 09:41:19 GMT

Hi, thank you very much for coming back to me with this.

Unfortunately though, although the query does run, it is not inserting the values into the column and all rows remain zero.

Many thanks and kind regards

Chris

Re: How do I edit my eval statement to insert a specified number in ColumnB based on specific values found in ColumnA?

HeinzWaescher — Tue, 29 Sep 2020 07:51:23 GMT

Hm, perhaps it does not like the dot in the fieldname?
You could try out to pipe this before the eval command

| rename detail.overall -> detail_overall
| eval rating=case(detail_overall="Very Satisfied", 100, detail_overall="Satisfied", 75 , detail_overall="Neither", 50, detail_overall="Dissatisfied", 25, detail_overall="Very Dissatisfied", 0 , 1=1, 0)

Re: How do I edit my eval statement to insert a specified number in ColumnB based on specific values found in ColumnA?

IRHM73 — Wed, 11 Nov 2015 09:52:25 GMT

Hi, that's really very interesting about the '.' It now works perfectly!

Many thanks and kind regards

Chris