Creating a custom column defined based on criteria?

[LealP]

Hello,

I have a scenario where I need to create a custom column (status) that should be defined based on a criteria.

CorrelationID	tracePoint
123	START
123	BEFORE REQUEST
123	AFTER REQUEST
123	END
456	START
456	BEFORE REQUEST
456	EXCEPTION
789	START
789	AFTER REQUEST

Expected Output:

CorrelationID	tracePoint	Status
123	START	SUCCESS
123	BEFORE REQUEST	SUCCESS
123	AFTER REQUEST	SUCCESS
123	END	SUCCESS
456	START	ERROR
456	BEFORE REQUEST	ERROR
456	EXCEPTION	ERROR
789	START	UNKNOWN
789	AFTER REQUEST	UNKNOWN

Rule: for a given correlationID the status should be set to ERROR if for that correlationId there is a tracePoint=EXCEPRION, should be set to SUCCESS if for that correlationId there is a tracePoint=END and should be set to UNKNOWN if for that correlationID there is no tracePoint=EXCEPTION or tracePoint=END.

Can you give me some guidance on how to achieve this scenario? Thanks!

[Brausepaule]

my Case Statement was an example, but anyhow you intend to flag events with same identifier based on one event with that identifier...in this case my solution would never work, as i judges each line byitself.

do you need the actuall list or would this suffice:

CorrelationID	Status
123	SUCCESS
456	ERROR
789	UNKNOWN

[LealP]

Yes, that's what I need because later on I'll run a dedup based on correlationId 

[Brausepaule]

please try this:

| stats values(tracePoint) as all_states by CorrelationID
| eval status = Case(all_states LIKE "%EXCEPTION%", "ERROR", all_states LIKE "%END%", "SUCCESS", 1==1, "UNKOWN")
| table CorrelationID status

[LealP]

the command that you sent produces that simplified  table containing only 3 lines, however before doing that I need to filter based on some other columns (that I didn't put in the example) and I need to have all rows  with that computed status column:

CorrelationID	tracePoint	Status
123	START	SUCCESS
123	BEFORE REQUEST	SUCCESS
123	AFTER REQUEST	SUCCESS
123	END	SUCCESS
456	START	ERROR
456	BEFORE REQUEST	ERROR
456	EXCEPTION	ERROR
789	START	UNKNOWN
789	AFTER REQUEST	UNKNOWN

Sorry for not making myself clear. 

[Brausepaule]

okay so not as simple as i hoped.

just to get an idea...about how many IDs and lines are we talking about?

[LealP]

it can be a big number...thousands for sure

[Brausepaule]

Okay if it is a big number we are talking reporting and not monitoring i guess.

I am basically a novice myself but i use dirty tricks. They are not nice to look at and are probably more complicated tahn neccessary, but as long as no one else provides a better solution this will work.

Use my last query to create the short table.

save the results in a summary index:

| collect index=<your_summary_index>

Here it gets complicated...how often do you run the query?

If it is very frequent then we are talking about monitoring...in this case my solution will still work, but might pose consistency issues.

In your query use a join a join command:

| join type=left CorrelationID [ | search index=<your_summary_index>]

this will add the field status to your main search based on the CorrelationID.

IMPORTANT: run the query to populate the sumary index shortly before the query which uses it. Then in the join qualify earliest as -10min or so.

give it a try 😉

[LealP]

The dashboard that I'm developing is purely for monitoring purposes. I'm afraid that the solution proposed it's not the one that fits my requirement.

The issues is that I'm a newbie and I don't know in detail that vast collection of commands provided by Splunk. Is there any expert that could advise on the best approach for my requirement?

Thanks in advance.

[Brausepaule]

Hi,

your example is abit confusing as you have three different states for the same tarcePoint value "START"

try something like this:

<your normal query>

| eval status = Case(tracePoint LIKE "%EXCEPTION%", "ERROR", tracePoint LIKE "%<your criteria here>%", "SUCCESS", 1==1, "UNKOWN")

then just table all three columns

[LealP]

That eval statement doesn't produce the expected output:

CorrelationID	tracePoint	Status	Expected Status
123	START	UNKNOWN	SUCCESS
123	BEFORE REQUEST	UNKNOWN	SUCCESS
123	AFTER REQUEST	UNKNOWN	SUCCESS
123	END	SUCCESS	SUCCESS
456	START	UNKNOWN	ERROR
456	BEFORE REQUEST	UNKNOWN	ERROR
456	EXCEPTION	ERROR	ERROR
789	START	UNKNOWN	UNKNOWN
789	AFTER REQUEST	UNKNOWN	UNKNOWN

Example: for correlationID 456 if there is one row with tracePoint=EXCEPTION, all the rows related with correlationID 456 should be set with status=ERROR