Dashboard - Report - Incidents - Requests

Hello experts,

I have a problem with building an query for Incidents, I need to create report that will show all Incidents that are open after 04:00 PM and before 08:00 AM (after 16:00 and before 08:00).

I have tried to build query like this:
"(flag=true) and ( open.time>='16:00:00' and open.time<='08:00:00')" but it didn't work.

Do you know If I can somehow use SQL statments on Dashboards?

Best regards,
Dino

  • Hi,

    try to use the time() function.

    "(flag=true) and ( time(open.time)>='16:00:00' and time(open.time)<='08:00:00')"

  • , thank you for reply I have tried but I am getting this error, too bad. :(

    time_error.jpg

    I am only querying "probsummary" but I think this is because HPMS is creating aliases for tables like "m1"...

    Best regards,
    Dino

  • I have tried this but I can't get it to work:

    open.time>(date(tod()) - '30 00:00:00') and ((open.time>='00:00:01' and open.time<='07:59:59') or (open.time>='16:00:00' and open.time<='23:59:59'))

    When I change and/or operators here then it works... but of course it finds everything because everything is in that condition...
    open.time>(date(tod()) - '30 00:00:00') and ((open.time>='00:00:01' or open.time<='07:59:59') and (open.time>='16:00:00' or open.time<='23:59:59'))

  • You need to break your logic down and test each bit before joining them up. you can use expert search or rad debugger and test open.time>(date(tod()) -'30 00:00:00'). see what value you actually get.  

    That condition is looking for open time greater than 30 days ago based on current time. Value return is a date which may not fit with the date specified in the other conditions. open.time is actually a date including time value. You may need to convert to string and just compare the hour portion of the date/time.

  • This "open.time>(date(tod()) -'30 00:00:00')" is working and that will show past records for past 30 days.
    Problem is with this:
    This one is working because I put date insided of it:( open.time>='09/04/17 16:00:00' and open.time<='09/05/17 08:00:00')
    I need to make this dynamic so I can take values for past week and month. I don't want to make query that will check every day for past month.

  • Correct. However, even when you just comparing  (open.time>='00:00:01' and open.time<='07:59:59') , it is comparing against the date stored within open.time which I suspect is conflicting against date returned from the subtract 30 days date condition. You need to change the (open.time>='00:00:01' and open.time<='07:59:59') condition to compare only the time and ignored the date inside open.time.

    Try these functions. Maybe it will do the job. Try time(val(open.time,2))>='"0:00:01". As time() only works with string, you may need val(var,2) to convert it to a string.  

    RAD function: time
    A RAD function that returns the time portion of a date and time value.
    Function
    time
    Format
    time($date.time.variable)
    Parameters
    The following parameter is valid for the time function:
    Parameter Data type Description
    $string String The string variable containing upper-case characters.
    Factors
    The time of a given date and time may be different in different time zones.
    Example
    time($date.time)
    After execution, the value of $date.time is 12:10:15.

     

    RAD function: val
    A RAD function that converts a datum to a different data type.
    Function
    val
    Format
    $new.data=val($data, $new.type)

  • Verified Answer

    ,

    Following query works for me:

    flag=true and (time(open.time)>='16:00:00' or time(open.time)<='02:00:00')

    And there are 3 tips:

    1. You should use "or" instead of "and", as >=16 and <=8 will return nothing.

    2.  Please make sure you are not grouping by other table's fields, like CIs or service or assignment, to avoid the cross table query error.

    3. the time function will slow down the system significantly if you have millions of open incidents (flag=true) in your probsummary table.

    Let me know if it helps,

    Ling-Yan

     

  • Hi ,
    This is working for view not dashboard at least for me. But it is great and I will use view to show this report.
    Thank you very much. :)

    Best regards,
    Dino