Hello,

I would have liked to create the following query to get an http response time distribution  

SELECT 'bigger than 3sec', count(*) as nbrequestbelowtime, (SELECT count(*) as nb_of_request_total FROM CurrentView where cs-uri LIKE '%/suite/sites/cpr%') as total
FROM CurrentView
where cs-uri LIKE '%/suite/sites/cpr%' AND time-taken > 3000;

but after a while i get the following error

 ---- Query Failed ----

Unable to interpret SELECT statement. Field, function, or expression expected.

What could be wrong with this query ?

Hi,

Thanks for reaching out.  I think the problem here is that the LogViewPlus query engine does not support an embedded SQL statement as a field for a SELECT clause.

However, I think you can achieve a similar result with something like:

SELECT 
    'bigger than 3sec', 
    SUM(CASE WHEN time-taken > 3000 THEN 1 ELSE 0 END) AS nbrequestbelowtime,
    COUNT(*) AS total
FROM CurrentView
WHERE cs-uri LIKE '%/suite/sites/cpr%' 
GROUP BY cs-uri;

I have not tested that, but I think it should be close to what you are looking for.

Hope that helps,

Toby

Thanks for the tips .... i've finally achieved my goal with the following query 

SELECT nbabovetime,total  FROM(
SELECT sum(isrequestabovetime) as nbabovetime , count(*) as total FROM (SELECT CASE WHEN time-taken > 1000 THEN 1 ELSE 0 END AS isrequestabovetime,cs-uri FROM CurrentView WHERE cs-uri LIKE '%/suite/sites/cpr%') );


Any clue to get the percentage at the end ?  (nbabovetime/total)*100 is not accepted 

 

That's a good solution - thanks for sharing.  

To calculate the percentage, you can try:

SELECT nbabovetime,total, CAST(ROUND(100.0 * (nbabovetime / total), 2) AS DECIMAL) as Percent
FROM(
    SELECT sum(isrequestabovetime) as nbabovetime , count(*) as total 
    FROM (
        SELECT CASE WHEN time-taken > 1000 THEN 1 ELSE 0 END AS isrequestabovetime,cs-uri FROM CurrentView WHERE cs-uri LIKE '%/suite/sites/cpr%'
    )
);

Hope that helps,

Toby

Sorry, i've the following error now : Error in ROUND: 100.0 is not a valid value for Double.

Thanks for reporting this issue. 

I had a look at the Round function and couldn't find a matching error message, so I am not sure what could be going wrong.  I have tested the 'Round' SQL statement above and it seems to be working fine.  Do you know what data type you are passing into the Round function?  Is '100.0' a string?  Are you able to paste the full SQL statement you are trying to execute?

I also wanted to let you know that we have now implemented sub-queries in SELECT fields, so your original SQL statement will work in the next version of LogViewPlus (v3.0.23 or greater).  Thanks for highlighting this issue.

If i execute the subselect alone, i get the following result 

+---------------------+
| nbabovetime | total |
+---------------------+
| 21912   | 27061 |
+---------------------+

and if i try to extract the percentage with a outer select

SELECT nbabovetime,total, CAST(ROUND(100.0 * (nbabovetime / total), 2) AS DECIMAL) as Percent
FROM(
  SELECT sum(isrequestabovetime) as nbabovetime , count(*) as total
  FROM (
   SELECT CASE WHEN time-taken > 1000 THEN 1 ELSE 0 END AS isrequestabovetime,cs-uri FROM CurrentView WHERE cs-uri LIKE '%/suite/webapi/%'
  )
);

i get the Error in ROUND: 100.0 is not a valid value for Double.


Trying different other notations for 100.0 (100,0 or 100 since in Belgium we use different characters to separate decimals)     gives 

Function ROUND expects parameter 0 to be of type System.Decimal.

What version of LogViewPlus are you using?  I am not able to recreate the problem locally:



I think this might be an issue with culture localization (this post might be a similar issue).  The first error message you reported does not come from LogViewPlus, but the second one does. 

Just a quick note to let you know that we have now released LogViewPlus v3.0.24 as a BETA release. 

This release addresses SQL issue discussed above which was related to localization.  The fix was relatively straight-forward, but unfortunately the change was bundled with a lot of other changes which needed additional testing before we could release.  We would have liked to fix this one faster - apologies for the delay.

This release also adds the ability to embed a subquery statement into select field.  The original SQL statement you posted should now be valid.

Thanks again for bringing this issue to our attention. Please do let me know if you have any further questions or issues.

Toby