How to fill in 0 for dates when we have missing values in the chart?

[anu1729]

 I am using below query to fill in 0 for dates when we have missing value and get those dates on the chart. But this is not working . Could anyone please help me here.

base search  | eval timestamp_epoc = strptime(timestamp,"%Y-%m-%dT%H:%M:%S.%3N%Z") | eval date_picker = strftime(timestamp_epoc,"%Y-%m-%d") | search requestURI="/api/v1/home/reseller/*" | eval hqid = substr(requestURI,23,10) | search $hqid$ | eval status_success=if(httpStatus="200",1,0) | eval status_fail= if(httpStatus != "200",1,0) | stats sum(status_success) as status_success, sum(status_fail) as status_fail by hqid,date_picker | eval status = case( (status_fail>0 AND status_success>0), "Multiple successful logins", (status_fail>0), "Multiple failed logins", (status_success>0), "Successful logins",1=1, "Other") |  fillnull value=0 date_picker hqid  status | chart count(hqid) by date_picker,status

[kamlesh_vaghela]

@anu1729 

 fillnull will work for the field having null value. Please check below search to reproduce such scenario.

| makeresults count=10 
| eval a=1 
| accum a 
| eval date_picker = if(a%2==0,_time,null())
| eval status = "Other"
| eval requestURI="/api/v1/home/reseller/kamlesh" 
| eval hqid = substr(requestURI,23,10)
| fillnull value=0 date_picker hqid status
| chart count(hqid) by date_picker,status

You also make sure, by executing below search.

base_search
| eval timestamp_epoc = strptime(timestamp,"%Y-%m-%dT%H:%M:%S.%3N%Z") 
| eval date_picker = strftime(timestamp_epoc,"%Y-%m-%d") 
| eval is_null = if(isnull(date_picker),"Null Value","Not Null")

Thanks
KV


If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

[VatsalJagani]

Can you please specify what do you mean when you see it is not working?
As it seems fillnull command seems okay.

[anu1729]

when I am running my query it is just plotting the graph only for those days when we have value for hqid, but we want to see dates for days as well if there is no hqid hit.

[VatsalJagani]

Just use timechart instead of chart command.

base search | eval timestamp_epoc = strptime(timestamp,"%Y-%m-%dT%H:%M:%S.%3N%Z") 
| bin date_picker span=1d 
| search requestURI="/api/v1/home/reseller/*" 
| eval hqid = substr(requestURI,23,10) 
| search $hqid$ 
| eval status_success=if(httpStatus="200",1,0) | eval status_fail= if(httpStatus != "200",1,0) 
| stats sum(status_success) as status_success, sum(status_fail) as status_fail by hqid,date_picker 
| eval status = case( (status_fail>0 AND status_success>0), "Multiple successful logins", (status_fail>0), "Multiple failed logins", (status_success>0), "Successful logins",1=1, "Other") 
|  fillnull value=0 hqid status
| eval _time=date_picker
| timechart count(hqid) by status

(See the change in second line and last two lines.)

[anu1729]

Its not working as I was expecting