topic Re: How to fill null value of multi value fields with other value in search output in Splunk Search

How to fill null value of multi value fields with other value in search output

sharif_ahmmad — Wed, 08 Jan 2020 16:32:58 GMT

Hello Community,

I need to fill null value of multi-field values with any value , i.e 0 or Not found.

Here's the sample data in table

Sample Table

Customer_Id           Counter_ID    Customer_Name               Desk_ID        Purchased_Item

     121                                                            1                     Pen

     121                                                            1                     Pencil

Expected Output

 Customer_Id      Counter_ID        Customer_Name     Desk_ID         Purchased_Item

   121                  0                     0              1                       Pen

   121                   0                     0              1                      Pencil

current Output

 Customer_Id        Counter_ID      Customer_Name        Desk_ID           Purchased_Item

   121                       0                0             1                                 Pen

                                                            1                                Pencil

Where what i wrote so far.

| stats list(Customer_Id) as Customer_id, stats list(Counter _Id) as Counter _id, stats list(Customer_Name) as Customer_Name,

stats list(Desk_ID) as Desk_ID, stats list(Purchased_Item) as Purchased_Item By Customer_Id | fillnull value=0 Counter_ID Customer_Name

Not sure how to proceed from here. Tried using foreach. But can't get what i want.

Any help would be really appreciated.

Re: How to fill null value of multi value fields with other value in search output

aberkow — Wed, 08 Jan 2020 18:19:59 GMT

Is the fillnull command not an option? You can fill either a specific field, i.e.:

| fillnull Counter_Id value=0

or a catch all fill null:

|fillnull value="N/A"

You can also do checks with the |where or |eval command for if things are null, and then filling them accordingly (a little more abstract for this use case but in general it's helpful to use sometimes) https://docs.splunk.com/Documentation/Splunk/8.0.1/SearchReference/InformationalFunctions#isnotnull.28X.29

Either:

| where isnotnull(fieldName)
or
|eval fieldName=if(isnull(fieldName), someAction, fieldName)

Hope this helps!

Re: How to fill null value of multi value fields with other value in search output

sharif_ahmmad — Wed, 08 Jan 2020 18:43:47 GMT

Hi @aberkow , thanks. but if you see my shared query i already tried with fillnull value. But what it does is fill of the null value of first row multi valued fields. What i need also is the same thing for the second row, which i am having problem with.

Re: How to fill null value of multi value fields with other value in search output

DavidHourani — Wed, 08 Jan 2020 20:00:27 GMT

Hi @sharif_ahmmad,

If I understand your query correctly then replacing your entire stats statement with this would give you the result you're looking for :

... | table  Customer_Id, Counter_ID, Customer_Name, Desk_ID, Purchased_Item
    | fillnull value=0

This would work because all you're trying to do is list the info, not trying to do any real aggregation.

The above search won't run fast though, so you can improve it by using something along those lines :

... | stats  values(Counter _Id) as Counter _id  values(Customer_Name) as Customer_Name values(Desk_ID) as Desk_ID  By Customer_Id,Purchased_Item
   | fillnull value=0

This will include some multi-value fields that you can break using | mvexpand fieldname.
Your final search should look something like this :

... | stats  values(Counter _Id) as Counter _id  values(Customer_Name) as Customer_Name values(Desk_ID) as Desk_ID  By Customer_Id,Purchased_Item
   | fillnull value=0 
   | mvexpand whicheverFieldHasMultiValue

Let me know if this helps.

Cheers,
David

Re: How to fill null value of multi value fields with other value in search output

to4kawa — Wed, 08 Jan 2020 21:19:36 GMT

your previous question

| eval Counter_ID = if(source = "A" AND isnull(Counter_ID), "N/A", Counter_ID)
| eval Customer_Name = if(source = "A" AND isnull(Customer_Name), "N/A", Customer_Name)
| stats . . .

before stats, you should fill null values.

Re: How to fill null value of multi value fields with other value in search output

to4kawa — Wed, 08 Jan 2020 21:49:56 GMT

 ... | stats ...
| eval null_fill = mvrange(0,mvcount(Customer_Id))
| rex field=null_fill mode=sed "s/\d+/NA/g"
| eval Counter_ID=mvappend(Counter_ID,null_fill)
| eval Customer_Name = mvappend(Customer_Name,null_fill)
| eval Counter_ID=mvindex(Counter_ID,0,mvcount(Customer_Id))
| eval Customer_Name =mvindex(Customer_Name,0,mvcount(Customer_Id))
| table  Customer_Id, Counter_ID, Customer_Name, Desk_ID, Purchased_Item

maybe works.

Re: How to fill null value of multi value fields with other value in search output

sharif_ahmmad — Thu, 09 Jan 2020 00:38:58 GMT

@to4kawa thanks. I will let you know which one works once i get to test them.

Re: How to fill null value of multi value fields with other value in search output

sharif_ahmmad — Wed, 30 Sep 2020 03:32:55 GMT

Hi @DavidHourani thanks. i like the idea of mvexpanding. Will let you know if it works.

But in the mean time, Can we use any other option than stats values() or table command ? Because stats values() will break the natural event order of log. no? And in this scenario table command will not put all the relevant values in the same row. I forgot to mention, for this case Desk_ID & Purchased_Item belongs to separate sourcetype than Counter _Id & Customer_Name.

Re: How to fill null value of multi value fields with other value in search output

DavidHourani — Thu, 09 Jan 2020 08:40:26 GMT

Hi @sharif_ahmmad,

In case values() doesn't work for you, you can stick with list(). I used values() to avoid duplicated, but in your case I guess you need to keep the duplicates.

This point is crucial as this means that you must use aggregation to join the events :

I forgot to mention, for this case Desk_ID & Purchased_Item belongs to separate sourcetype than Counter _Id & Customer_Name.

Since the only common field in that case is Customer_Id then the search should actually be like this :

 ... | stats  list(Counter _Id) as Counter _id  list(Customer_Name) as Customer_Name list(Desk_ID) as Desk_ID  list(Purchased_Item) as Purchased_Item By Customer_Id
    |mvexpand Purchased_Item
    |mvexpand Desk_ID
    | fillnull value=0

Let me know if this works any better for you.
Cheers,
David

Re: How to fill null value of multi value fields with other value in search output

TISKAR — Thu, 09 Jan 2020 09:22:04 GMT

Hi,

Can try to use fillnull befor stats command, like this:

| makeresults count=1
     | eval data = "121 1 Pen;121 1 Pencil"
     | makemv delim=";" data
     | mvexpand data
     | rex field=data "(?<Customer_Id>[^\s]+)\s(?<Desk_ID>\d+)\s(?<Purchased_Item>\w+)"
     | table Customer_Id Counter_ID Customer_Name Desk_ID Purchased_Item
     | fillnull Counter_ID Customer_Name value=0
     | stats list(Counter_ID) as Counter_ID, list(Customer_Name) as Customer_Name, list(Desk_ID) as Desk_ID, list(Purchased_Item) as Purchased_Item by Customer_Id

Smile Splunk 🙂

Re: How to fill null value of multi value fields with other value in search output

sharif_ahmmad — Wed, 30 Sep 2020 03:33:56 GMT

works like a charm. All i needed to added if(isnull) check along with mvappend

eval Counter_ID=if(isnull(Counter_ID), mvappend(Counter_ID,null_fill),Counter_ID)

Thanks a lot @to4kawa

Re: How to fill null value of multi value fields with other value in search output

to4kawa — Fri, 10 Jan 2020 12:02:36 GMT

and ,you are Splunk Ninja 🙂

Re: How to fill null value of multi value fields with other value in search output

sharif_ahmmad — Fri, 10 Jan 2020 13:13:13 GMT

🙂 thanks again.

Re: How to fill null value of multi value fields with other value in search output

sharif_ahmmad — Wed, 30 Sep 2020 03:40:11 GMT

@to4kawa hi, just wanted to follow up on this? While this works for the mentioned scenrio. but it doesn't work if there is already a value. Like this,

Customer_Id Counter_ID Customer_Name Desk_ID Purchased_Item

  121                              1                             1                     Pen

  121                                                            1                     Pencil

Any idea what to do, if i want to fill up the vacant counter_id with some value? Would really appreciate the help.

Re: How to fill null value of multi value fields with other value in search output

to4kawa — Thu, 16 Jan 2020 04:41:50 GMT

filldown and fillnull , maybe.

Re: How to fill null value of multi value fields with other value in search output

sharif_ahmmad — Thu, 16 Jan 2020 04:44:52 GMT

before that stats command?

Re: How to fill null value of multi value fields with other value in search output

to4kawa — Thu, 16 Jan 2020 05:07:07 GMT

after stats

Re: How to fill null value of multi value fields with other value in search output

sharif_ahmmad — Thu, 16 Jan 2020 05:34:42 GMT

thanks for the tips. Will let you know if it works.

Re: How to fill null value of multi value fields with other value in search output

to4kawa — Fri, 17 Jan 2020 03:39:16 GMT

| makeresults
| eval _raw="Customer_Id,Counter_ID,Customer_Name,Desk_ID,Purchased_Item
121,1,,1,Pen
121,,,1,Pencil"
| multikv forceheader=1
| table Customer_Id,Counter_ID,Customer_Name,Desk_ID,Purchased_Item
| filldown
| fillnull Counter_ID,Customer_Name,Desk_ID value="N/A"

e.g.

Re: How to fill null value of multi value fields with other value in search output

sharif_ahmmad — Fri, 17 Jan 2020 11:01:29 GMT

@to4kawa Thanks a lot.