Chart- return all cell==0

[maayan]

Hi,

I need to find all time_interval for each machine where there is no data (no row for Name) .
(to goal is to create an alert if there was no data in a time interval for a machine)

for example, if we look at one day and machine X.
if there was data in time interval 8:00-10:00, 10:00-12:00.
I need to return X and the rest of the interval (12:00-1:00,1:00-2:00,..)

i wrote the following command: 
| chart count(Name) over machine by time_interval

i get a table with all interval and machines. cell=0 if there is no data.
i want to return all cell =0 (i need the interval and machine where cell=0)
but i didn't succeed.

i also tried to save the query and do left join but it doenst work.
it's a very simple mission, some can help me with that?

thanks,
Maayan

[ITWhisperer]

| timechart span=2h count(Name) by machine

[maayan]

thanks! i use TimeStamp and not _time.
how do i use it in my query?

| addinfo
| fieldformat info_min_time=strftime(info_min_time,"%c")
| fieldformat info_max_time=strftime(info_max_time,"%c")

| where strptime(TimeStamp,"%F %T.%3N")>info_min_time AND strptime(TimeStamp,"%F %T.%3N")<info_max_time

```Divide the time to intervals ```
| eval TimeStamp_epoch = strptime(TimeStamp, "%F %T")
| bin TimeStamp_epoch span=2d 
| eval interval_start = strftime(TimeStamp_epoch, "%F %T")
| eval interval_end = strftime(relative_time(TimeStamp_epoch, "+2d"), "%F %T") 
| eval interval_end = if(strptime(interval_end, "%F %T") > now(), strftime(now(), "%F %T"), interval_end)
| eval time_interval = interval_start . " to " . interval_end

| chart count(Name) over machine by time_interval

[ITWhisperer]

Use _time, then timechart will fill in the blanks for you

| eval _time=strptime(TimeStamp, "%F %T")
| timechart span=2h count(Name) by machine

[maayan]

thanks! 🙂

i don't get all cells=0, no results when using the where clause (if i remove `where` i see that cells==0 exist) . i found a ticket: https://community.splunk.com/t5/Splunk-Search/How-to-show-only-fields-over-0/m-p/164589
maybe i can't do it with timechat?

| eval _time=strptime(TimeStamp, "%F %T")

| timechart span=12h count(Name) AS CountEvents by machine cont=t usenull=f useother=f

| where CountEvents=0

[ITWhisperer · Accepted solution]

| eval _time=strptime(TimeStamp, "%F %T")

| timechart span=12h count(Name) AS CountEvents by machine cont=t usenull=f useother=f

| untable _time machine count
| where count == 0

[maayan]

i will do validations but i think that it works , thanks! 🙂