How to remove the null values from below fields?

[ramkyreddy]

I want to remove the null values, which are in empty cells, there is no data present I tried multiple ways not able to remove them,

index="gitxapixation" platform !=null provision_type IN (*) site_location IN (*)
| eval end_time=strftime(end_time, "%m/%d/%y %H:%M:%S")
| eval WW=strftime(start_time, "%V.%w")
| eval _time = start_time
| eval WW=strftime(_time, "%V.%w")
| eval scenario_name=cas(provision_type="BIOS",mvindex(split(context,"."),1),provision_type="IFWI",mvindex(split(context,"."),1),provision_type="OS",mvindex(split(context,"."),1))
| eval scenario_name=coalesce(scenario_name,name)
| rename scenario_name as Testcase_id
| stats count(eval(status="FAIL")) as fail_count, count(eval(status="PASS")) as pass_count, count(eval(like(status,"BLOCKED%"))) as not_run_count by WW,kit,provision_type,platform,subproject
| eval scenario_status=case(fail_count>0, "FAIL", not_run_count>0, "NOT RUN", fail_count=0 AND not_run_count=0, "PASS")
| eval IFWI=case(provision_type="IFWI",scenario_status)
| eval OS=case(provision_type="OS",scenario_status)
| eval BIOS=case(provision_type="BIOS",scenario_status)
| rename platform as Platform subproject as AutomationType
| table WW,Platform,kit,AutomationType,IFWI,BIOS,OS

I will appreciate on this, please help me out

[ITWhisperer · Accepted solution]

Each row represents an event from your results. Each column represents the fields for those events and their values.

If you want something in those fields to represent the fact that no value is available for the field for that event, you can use the fillnull command, for example:

| fillnull value="N/A"

[ramkyreddy]

Is there any logic can we hide or delete, all 3cloums having N/A values, because there is no data present.

[ITWhisperer]

Unless you have create the fields with null for all events, there is likely to be at least one event with a non-null value, although it may not be on that page of the table, therefore the column remains.

If this isn't the case, you would need to share your search so we might be able to find a reason for the "empty" columns

[ramkyreddy]

You're asking  about this search query right

index="gitxapixation" platform !=null provision_type IN (*) site_location IN (*)
| eval end_time=strftime(end_time, "%m/%d/%y %H:%M:%S")
| eval WW=strftime(start_time, "%V.%w")
| eval _time = start_time
| eval WW=strftime(_time, "%V.%w")
| eval scenario_name=cas(provision_type="BIOS",mvindex(split(context,"."),1),provision_type="IFWI",mvindex(split(context,"."),1),provision_type="OS",mvindex(split(context,"."),1))
| eval scenario_name=coalesce(scenario_name,name)
| rename scenario_name as Testcase_id
| stats count(eval(status="FAIL")) as fail_count, count(eval(status="PASS")) as pass_count, count(eval(like(status,"BLOCKED%"))) as not_run_count by WW,kit,provision_type,platform,subproject
| eval scenario_status=case(fail_count>0, "FAIL", not_run_count>0, "NOT RUN", fail_count=0 AND not_run_count=0, "PASS")
| eval IFWI=case(provision_type="IFWI",scenario_status)
| eval OS=case(provision_type="OS",scenario_status)
| eval BIOS=case(provision_type="BIOS",scenario_status)
| rename platform as Platform subproject as AutomationType
| table WW,Platform,kit,AutomationType,IFWI,BIOS,OS

[ITWhisperer]

Because you specifically listed the fields in the table command, they will appear in the table.

You could try it this way

| stats count(eval(status="FAIL")) as fail_count, count(eval(status="PASS")) as pass_count, count(eval(like(status,"BLOCKED%"))) as not_run_count by WW,kit,provision_type,platform,subproject
| eval scenario_status=case(fail_count>0, "FAIL", not_run_count>0, "NOT RUN", fail_count=0 AND not_run_count=0, "PASS")
| eval IFWI=case(provision_type="IFWI",scenario_status)
| eval OS=case(provision_type="OS",scenario_status)
| eval BIOS=case(provision_type="BIOS",scenario_status)
| rename platform as Platform subproject as AutomationType
| fields - failed_count pass_count not_run_count scenario_status provision_type
| table WW,Platform,kit,AutomationType,*

[ramkyreddy]

Great Thanks, @ITWhisperer Its working fine but 


Is there any chance actually i want like this 

[ITWhisperer]

Seems unlikely given that your stats are by WW Platform Kit and AutomationType. You could either fill in the blanks (use fillnull as previously mentioned) or exclude all lines where any of them are null.

What do you think a table like you showed would represent?

[ramkyreddy]

the output actually I need in the 2 Fig.

Thanks a lot, @ITWhisperer for helping me out with this query its working fine