4 Replies Latest reply on Feb 11, 2016 9:47 AM by rashedtalukder

Is There a Custom Query Tool

roguen Feb 10, 2016 1:32 PM

We're moving from a locally hosted environment to Jive's cloud environment this year. In our local environment we obviously have a bit more flexibility with the plugin customizations and have a query tool we use pretty often. One example might be the following:

select count(*) from jivequestion question
where
question.containerid in (2013, 2024, 2025, 2027, 2028, 2029, 2030, 2032, 2031, 2033)
and question.resolutionstate = 0
and TO_TIMESTAMP(question.creationdate/ 1000) > TO_TIMESTAMP(20130822000000::text, 'YYYYMMDDHH24MISS')

Are there any capabilities to query like this in Jive cloud?

I'm guessing not, but would love to hear some creative workarounds.

Re: Is There a Custom Query Tool

jrlarson Feb 10, 2016 2:07 PM (in response to roguen)

Hey Roguen Keller, what's up?

To answer your question, there is no direct way to query the DB like you are showing. However, you could probably accomplish the goal through a combination of Jive API calls. You may have to post-process some of the API responses to narrow the results.

The API queries do have some filterability built in that may help with narrowing the search to specific places and date ranges.

I hope you are doing well.
Like (0)

Actions
- Re: Is There a Custom Query Tool
  
  jrlarson Feb 10, 2016 2:10 PM (in response to jrlarson)
  
  We are actually doing something very similar for a client - looking up unanswered questions in a list of places for a certain date range. Is that close to what you need to do?
  
  Like (0)
  
  Actions
  - Re: Is There a Custom Query Tool
    
    roguen Feb 11, 2016 5:49 AM (in response to jrlarson)
    
    Thanks for the quick reply John!
    
    I suspected this was the case. I can also now see that the cloud admin tool does not allow DB downloads for cloud environments. So going that route is also not an option. API access in the manner you share seems to be the only route.
    
    As for the query itself, while your example does match what I have above, this is just an example of one of the queries we would need. I have about 10 different queries aimed at various metrics and checks. These remain mostly static, but change from time to time.
    It appears I would need to create these one off for the time being.
    
    Again, If anyone else out there has a more creative plan, I would love to hear it. Marking this answer correct though.
    
    Like (0)
    
    Actions
    - Re: Is There a Custom Query Tool
      
      rashedtalukder Feb 11, 2016 9:47 AM (in response to roguen)
      
      You can query the DB like that using DES: Using the V2 Jive Data Export Service (Analytics)
      
      Like (1)
      
      Actions

Go to original post