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

[sharif_ahmmad]

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.

[TISKAR]

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 🙂

[to4kawa · Accepted solution]

 ... | 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.

[haraksin]

Fantastic answer; I made a simpler version without REX, since it is a compute hog, with test examples (if you get an error with the comment macro, just delete those lines):

| makeresults
`comment("Setting up fields to compare")`
| eval mv_more=mvappend("yes", "no", "not", "you"), mv_less=mvappend("maybe", "later")
`comment("Begin here")`
| eval null_fill = mvrange(mvcount(mv_less),mvcount(mv_more))
| eval null_fill=mvmap(null_fill, "N/A")
| eval mv_less_new = mvappend(mv_less,null_fill)

By taking the range from the length of the lesser field to the length of the greater field, we're able to eleminate a few eval statements crunching the difference with mvindex. You can try this by adding fields to mv_more; it will give you the same result each time.

[sharif_ahmmad]

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

[to4kawa]

and ,you are Splunk Ninja 🙂

[sharif_ahmmad]

@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.

[to4kawa]

filldown and fillnull , maybe.

[sharif_ahmmad]

before that stats command?

[to4kawa]

after stats

[sharif_ahmmad]

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

[to4kawa]

| 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.

[sharif_ahmmad]

@to4kawa Thanks a lot.

[sharif_ahmmad]

🙂 thanks again.

[to4kawa]

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.

[sharif_ahmmad]

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

[DavidHourani]

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

[sharif_ahmmad]

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.

[DavidHourani]

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

[aberkow]

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....

Either:

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

Hope this helps!

[sharif_ahmmad]

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.