How to set column with default value if column key not exist?

[hakusama1024]

Hi guys

I'm trying to create a statistic table for the data from jira. Each column has different severity for jira issue.
For example severity from S0 to S3, but there is no S0 level issue. So when i use the chart count b _time, severity it doesn't show the column for S0. I'm wondering is there any way to setup default value to 0 so i can see the missing column.
Thanks for help.

the command i use:
index = "demo1" severity != null sdlc_phase !="closed"|dedup key| eval _time=strptime(created,"%Y-%m-%dT%H:%M:%S.000+0000") | bin _time span="1mon" | eval n_status=lower('severity') | eval sort_field=case(n_status=="s0", 1,n_status=="s1", 2,n_status=="s2", 3,n_status=="s3", 4, n_status=="TOTAL", 5 )| chart count by _time, severity |sort _time desc | fields - n_status sort_field | addtotals

Best
Xin

[woodcock]

All that you need to do is add this to your existing (almost working) search:

| fillnull s0 s1 s2 s3 s4 s5 value=0

Whichever columns do not exist will be created and given a value of 0.

[hakusama1024]

Hi guys

I added true(), 5 to the end. but it still not showing S0 column with default value 0.
In my data. each issue has its severity, so this time means there's no issue in severity S0.
but i want have a column S0 with value 0.
Please advise.

Best
Xin

[sravankaripe]

Try this
| eval sort_field=case(
n_status=="s0", "1",
n_status=="s1", "2",
n_status=="s2", "3",
n_status=="s3", "4",
n_status=="TOTAL"," 5",
true(),"0")

[niketn]

@hakusama1024, first off is severity value actually "null" in your events or do you actually want to filter out only event which have severity?

Since you have used case for n_staus you can define the default value as 0 using either 1==1 or true() , following is an example

eval sort_field=case(
                                    n_status=="s0", 1,
                                    n_status=="s1", 2,
                                    n_status=="s2", 3,
                                    n_status=="s3", 4,
                                    n_status=="TOTAL", 5,
                                    true(),0)

Also, if you are already using chart command (or timechart command), you can directly use span="1mon".