Dataset: Get Interval

[yferreira]

Hi,

I was trying to figure out how I can subtract "from_dtime(dtime)" of "now()" and count the interval in days.

I was wondering if somebody knows.

Tks folks.

[hzhao_FTNT · Accepted solution]

I see, then pls try  to_char((now() - from_dtime(dtime)), 'DD') as interval

[hzhao_FTNT · Accepted solution]

Please try: 

select (case when  (date(now()) - date(from_dtime(dtime))) > 90 then 'More than 90 days' else to_char( (now() - from_dtime(dtime) ), 'DD') end) as interval, devid, msg from $log where msg like 'Fortigate started' and subtype='system' and level='information' and msg is not null and $filter ORDER BY interval ASC limit 1

regards,

hz

[hzhao_FTNT · Accepted solution]

then:

select (case when  (date(now()) - date(from_dtime(dtime))) < 90 then to_char( (now() - from_dtime(dtime) ), 'DD')  else 'More than 90 days' end) as interval, devid, msg from $log where msg like 'Fortigate started' and subtype='system' and level='information' and msg is not null and $filter ORDER BY interval ASC limit 1

[hzhao_FTNT · Accepted solution]

Please try: 

select (case when  (date(now()) - date(from_dtime(dtime))) > 90 then 'More than 90 days' else to_char( (now() - from_dtime(dtime) ), 'DD') end) as interval, devid, msg from $log where msg like 'Fortigate started' and subtype='system' and level='information' and msg is not null and $filter ORDER BY interval ASC limit 1

regards,

hz

[yferreira]

It worked fine.

But if I can't find anything, I would like to print "More than 90 days".

[hzhao_FTNT · Accepted solution]

then:

select (case when  (date(now()) - date(from_dtime(dtime))) < 90 then to_char( (now() - from_dtime(dtime) ), 'DD')  else 'More than 90 days' end) as interval, devid, msg from $log where msg like 'Fortigate started' and subtype='system' and level='information' and msg is not null and $filter ORDER BY interval ASC limit 1

[yferreira]

Did not print. Returned "No Data"

[yferreira]

I mean, if did not find any register, print "More than 90 days".

[hzhao_FTNT]

base on your query, if "msg like 'Fortigate started' and subtype='system' and level='information' and msg is not null", you won't see null record.

[yferreira]

Good point.

So, I'll remove "and msg is not null".

But, I'm still wondering if could be possible to print a message if we didn't find anything.

[hzhao_FTNT · Accepted solution]

AFAIK, it is not possible to do it, chart can not automatically change column number or customize "No matching log data for this report".