Forum Discussion

drahrig's avatar
drahrig
Level 4
10 years ago

DQL syntax error

I am trying to create a DQL report. I am receiving an error message, it states: Parse error: syntax error, unexpected ID, expecting END.

The rule I have mashed together looks like this:

 

FROM activity

 

GET user.name

        user.domain

        sum(count) AS access_count,

        path.device.name,

        path.absname

        formatdate(timestamp, "YYYY-MM-DD HH:mm") AS time

WHERE

        timestamp >= datetime("2015/01/01 00:00", "YYYY/MM/DD HH:mm")

        timestamp <= datetime("2015/04/21 23:59", "YYYY/MM/DD HH:mm")

 

IF     path.issensitive = 1

GROUPBY user.name,

                 user.domain,

                 device.name,

                 path.absname

 

SORTBY sum(count) DESC

LIMIT 10;

Data Insight
Information Governance
Reporting
Windows Server (2003-2008)
  • Rod_p1's avatar
    Rod_p1
    10 years ago

    Do I need to customize it further?

    the errors initially were due to missing section delimiters and separators. Essentially each sections request need the Clause Request and data separated by commas until the last item which does not have a comma an then the next request. Example:
     

    Get Table.column1, Table.column2, Table.column3, Table.column4

    Next
    END;

    In the example I posted my lines:

    GET user.name,
            user.domain,
            sum(count) AS access_count,
            path.device.name
            path.absname,

    formatdate(timestamp, "YYYY-MM-DD HH:mm")

     

    actually reads:

    GET user.name,
            user.domain,
            sum(count) AS access_count,
            path.device.name,
            path.absname,

    formatdate(timestamp, "YYYY-MM-DD HH:mm")

    in my report. I am not sure why the comma is truncated it must have been a formatting correction I allowed.

    Rod

5 Replies

Sort By
  • Rod_p1's avatar
    Rod_p1
    Level 6
    10 years ago

    Hello Dave,

     

    You have a couple of columns mismatched and a few grammatical errors. I think you are trying to find the sensitive files between a range of dates and show the user and location. I have changed it slightly (you can add back the limit if you desire for testing) based on the Programmer's guide descriptions of the avaialble clauses and columns. Noteworthy is the IF clause replacing the WHERE clause you are used to.

     

    Please test out:

    FROM activity

    GET user.name,

            user.domain,
            sum(count) AS access_count,
            path.device.name

             path.absname,


    formatdate(timestamp, "YYYY-MM-DD HH:mm")

    IF path.issensitive = "1"   AND
     timestamp >= datetime("2015/01/01 00:00", "YYYY/MM/DD HH:mm")
    AND
     timestamp <= datetime("2015/04/21 23:59", "YYYY/MM/DD HH:mm")

    GROUPBY user.name,

                     user.domain,

                     path.device.name,

                     path.absname

    SORTBY sum(count) DESC;

     

    Hopefully that is what you are looking for?

    Rod

  • drahrig's avatar
    drahrig
    Level 4
    10 years ago

    You are correct. That is what I am looking for. I ran and received the same error message. I tested with both "IF" and "WHERE".

  • Rod_p1's avatar
    Rod_p1
    Level 6
    10 years ago

    Do I need to customize it further?

    the errors initially were due to missing section delimiters and separators. Essentially each sections request need the Clause Request and data separated by commas until the last item which does not have a comma an then the next request. Example:
     

    Get Table.column1, Table.column2, Table.column3, Table.column4

    Next
    END;

    In the example I posted my lines:

    GET user.name,
            user.domain,
            sum(count) AS access_count,
            path.device.name
            path.absname,

    formatdate(timestamp, "YYYY-MM-DD HH:mm")

     

    actually reads:

    GET user.name,
            user.domain,
            sum(count) AS access_count,
            path.device.name,
            path.absname,

    formatdate(timestamp, "YYYY-MM-DD HH:mm")

    in my report. I am not sure why the comma is truncated it must have been a formatting correction I allowed.

    Rod

  • Rod_p1's avatar
    Rod_p1
    Level 6
    10 years ago

    You are welcome Dave, hopefully that is the ticket to get you the data you require. Let me know what you think of the tool as it is currently. We are always enhancing and adding features to the product and this is one area we will focus on for the 5.0 release.

     

    Rod