topic Re: Can you give me some help with the coalesce command? in Splunk Search

Can you give me some help with the coalesce command?

jip31 — Wed, 13 Feb 2019 14:27:19 GMT

hi,

When I execute the query below

index="x" sourcetype="WinEventLog:Microsoft-Windows-Diagnostics-Performance/Operational"

I can display a field called "OpCode". This field has many values and I want to display one of them.

Sometimes this field is in english, sometimes in French, sometimes in Spanish and sometimes in German.

So I need to use "coalesce" like this

| eval 'Boot_Degradation'=coalesce('Boot_Degradation','Détérioration du démarrage','Información del arranque','Startbeeinträchtigung') 
| table OpCode

but it doesn't work.

could you help me please???

Re: Can you give me some help with the coalesce command?

vnravikumar — Wed, 13 Feb 2019 14:33:04 GMT

Hi @jip31

Try this, it will take first not null value

| makeresults 
   | eval OpCode=coalesce(null(),"Détérioration du démarrage","Información del arranque","Startbeeinträchtigung") | table OpCode

Re: Can you give me some help with the coalesce command?

nickhills — Wed, 13 Feb 2019 14:59:31 GMT

Coalesce is not the command you need here. Try this:

| makeresults 
| eval OpCode="Boot_Degradation,Détérioration du démarrage,Información del arranque,Startbeeinträchtigung"

| makemv delim="," OpCode
| eval OpCode=mvindex(OpCode, 0)

The first two lines make some sample data - hopefully I have interpreted this right?
The last two lines make the field multivalued, and then pick the first (0) value in the mv list and returns that value.

If you wanted french swap the 0 for a 1 etc

You full search would probably be something like this:

index="x" sourcetype="WinEventLog:Microsoft-Windows-Diagnostics-Performance/Operational"| makemv delim="," OpCode
| eval OpCode=mvindex(OpCode, 0)|table OpCode

Re: Can you give me some help with the coalesce command?

FrankVl — Wed, 13 Feb 2019 15:22:13 GMT

'Boot_Degradation','Détérioration du démarrage','Información del arranque','Startbeeinträchtigung'

Are those your field names? Or your field values? The coalesce command takes field names as parameters, and returns the first non-null field.