6 Replies Latest reply on Jul 16, 2018 3:41 AM by Ana Lorite

    tw_query and dates

    Ana Lorite

      Hi everybody,

       

      I have a problem when I execute a query using tw_query command.

       

      What i want is all the Discovery Access which starttime is greater than 2018-07-12 11am.

       

      In Generic Search Query, the following works fine:

       

      search DiscoveryRun where endtime defined traverse List:List:Member:DiscoveryAccess where starttime > parseLocalTime('2018-7-12 11:00:00') order by starttime desc processwith where nodecount(traverse Member:List:List:DiscoveryRun where label = 'POC_Hiper_San_Sebastian_Reyes') show #Member:List:List:DiscoveryRun.label as 'Label', endpoint as 'Endpoint', result as 'Result', device_summary as 'Device Summary', starttime as 'Start Time', endtime as 'End Time'

       

      But in my script, it seems something is wrong with the date. I build the variable $DATE and, today, its value is 2018-07-12 11.00.00

       

      The query is:

       

      /usr/tideway/bin/tw_query --password=XXXXXX --csv --file /usr/tideway/POC_tmp/$REPORT_DISCOVERY_ACCESS "search DiscoveryRun where endtime defined traverse List:List:Member:DiscoveryAccess where starttime > parseLocalTime($DATE_PARAM) order by starttime desc processwith where nodecount(traverse Member:List:List:DiscoveryRun where label = 'POC_Hiper_San_Sebastian_Reyes') show #Member:List:List:DiscoveryRun.label as 'Label', endpoint as 'Endpoint', result as 'Result', device_summary as 'Device Summary', starttime as 'Start Time', endtime as 'End Time'"

       

      And the query returns all the Discovery Access executed today but I need only the ones after 11am. So I presume that the problem is parseLocalTime($DATE_PARAM) but I don't know how to specify the correct syntax. I tried with \ caracter but i doesn't work.

       

      Thanks in advance.

       

      Ana.

        • 1. Re: tw_query and dates
          Ana Lorite

          I make a correction: that is how I execute with no success

           

          /usr/tideway/bin/tw_query --password=XXXXXX --csv --file /usr/tideway/POC_tmp/$REPORT_DISCOVERY_ACCESS "search DiscoveryRun where endtime defined traverse List:List:Member:DiscoveryAccess where starttime > parseLocalTime('$DATE_PARAM') order by starttime desc processwith where nodecount(traverse Member:List:List:DiscoveryRun where label = 'POC_Hiper_San_Sebastian_Reyes') show #Member:List:List:DiscoveryRun.label as 'Label', endpoint as 'Endpoint', result as 'Result', device_summary as 'Device Summary', starttime as 'Start Time', endtime as 'End Time'"

          • 2. Re: tw_query and dates
            Ana Lorite

            Using tw_query in command line, it doesn't  work either

             

            [tideway@discoverypoc POC_tmp]$ /usr/tideway/bin/tw_query --password=XXXXXXXXX --csv --file /usr/tideway/POC_tmp/help.cvs "search DiscoveryRun where endtime defined traverse List:List:Member:DiscoveryAccess where starttime > parseLocalTime('2018-07-12 11.00.00') order by starttime desc processwith where nodecount(traverse Member:List:List:DiscoveryRun where label has substring 'San_Sebastian_Reyes') show #Member:List:List:DiscoveryRun.label as 'Label', endpoint as 'Endpoint', result as 'Result', device_summary as 'Device Summary', starttime as 'Start Time', endtime as 'End Time'"

            • 3. Re: tw_query and dates
              Andrew Waters

              It would be better to use a query like:

              /usr/tideway/bin/tw_query --password=XXXXXX --csv --file /usr/tideway/POC_tmp/$REPORT_DISCOVERY_ACCESS "SEARCH DiscoveryRun WHERE label = 'POC_Hiper_San_Sebastian_Reyes' AND endtime > parseLocalTime('$DATE_PARAM') AND NOT __inprogress TRAVERSE List:List:Member:DiscoveryAccess WHERE starttime > parseLocalTime('$DATE_PARAM') ORDER BY starttime DESC SHOW #Member:List:List:DiscoveryRun.label, endpoint, result, device_summary, starttme, endtime"

              Have you made sure the date is being filled in? tw_svc_model.log will show you the query it is running.

              3 of 3 people found this helpful
              • 4. Re: tw_query and dates
                Duncan Grisby

                You must use colons in the time, not period characters. It must be "11:00:00", not "11.00.00".

                4 of 4 people found this helpful
                • 5. Re: tw_query and dates
                  Ana Lorite

                  That was right. Thank you very much!!

                  • 6. Re: tw_query and dates
                    Ana Lorite

                    Thanks four your recommendation, Andrew Waters. It seems it worked fine but I'll take an eye on it

                     

                    Thanks!!!

                     

                    Regards.

                     

                    Ana.