Highlighted
Outstanding Contributor.
Outstanding Contributor.
886 views

Dashboard - Report - Incidents - Requests

Jump to solution

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

Tags (3)
0 Likes
1 Solution

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: Dashboard - Report - Incidents - Requests

Jump to solution

@dfilipovic2,

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

 

View solution in original post

8 Replies
Highlighted
Trusted Contributor.
Trusted Contributor.

Re: Dashboard - Report - Incidents - Requests

Jump to solution

Hi,

try to use the time() function.

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

Highlighted
Outstanding Contributor.
Outstanding Contributor.

Re: Dashboard - Report - Incidents - Requests

Jump to solution

@Martin_E, 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

0 Likes
Highlighted
Outstanding Contributor.
Outstanding Contributor.

Re: Dashboard - Report - Incidents - Requests

Jump to solution

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'))

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: Dashboard - Report - Incidents - Requests

Jump to solution

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.

0 Likes
Highlighted
Outstanding Contributor.
Outstanding Contributor.

Re: Dashboard - Report - Incidents - Requests

Jump to solution

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.

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: Dashboard - Report - Incidents - Requests

Jump to solution

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)

Highlighted
Micro Focus Expert
Micro Focus Expert

Re: Dashboard - Report - Incidents - Requests

Jump to solution

@dfilipovic2,

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

 

View solution in original post

Highlighted
Outstanding Contributor.
Outstanding Contributor.

Re: Dashboard - Report - Incidents - Requests

Jump to solution

Hi @lingyanmeng,
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

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.