topic Re: Chart- return all cell==0 in Dashboards & Visualizations

Chart- return all cell==0

maayan — Wed, 10 Jan 2024 08:11:33 GMT

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

Re: Chart- return all cell==0

ITWhisperer — Wed, 10 Jan 2024 09:07:07 GMT

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

Re: Chart- return all cell==0

maayan — Wed, 10 Jan 2024 11:10:38 GMT

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

Re: Chart- return all cell==0

ITWhisperer — Wed, 10 Jan 2024 10:15:09 GMT

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

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

Re: Chart- return all cell==0

maayan — Wed, 10 Jan 2024 11:28:35 GMT

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

Re: Chart- return all cell==0

ITWhisperer — Wed, 10 Jan 2024 11:40:21 GMT

| 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

Re: Chart- return all cell==0

maayan — Wed, 10 Jan 2024 11:52:27 GMT

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