6 Replies Latest reply on Dec 19, 2015 2:15 AM by ben.sunderland

extract list of users to csv via REST + shell

ben.sunderland Dec 14, 2015 5:46 PM

So I want to use the REST Api to just list username / email - and get it into CSV.

I wrote shell script that runs curl commands , and uses the pagination links to iterate through each page.

I end up with the multi-page JSON output saved to a single file (assuming user count >100).

because I am in Shell , and not Java for example, I didnt want to get in to JSON parsing.

SO I found jsawk - which looks good to be able to use shell to grab JSON values....

But my output file is not valid ... I am pretty sure I can code around this but massaging the output file into valid JSON, but I wanted to take stock and check.....

Is there a simpler way to dump ALL users to file using REST ? Parsing from shell is such a drag - and this should be quite a common request , no ?

My goal was to be able to send a shell script that does this as opposed to a Java App ......

so customers can modify etc......

Re: extract list of users to csv via REST + shell

rashedtalukder Dec 15, 2015 9:04 AM (in response to ben.sunderland)

REST actually doesn't allow explicit export to a certain file type as being stateless is part of the standard. Also, the number of users in some instances can be massive and if multiple people are trying to run the same query, you can see how that can become bad very quickly.

Anyway, this was the first hit in my Google search for shell script JSON to CSV: archan937/jsonv.sh · GitHub

I haven't used it before, but it seems like it fits the bill.
Like (0)

Actions
- Re: extract list of users to csv via REST + shell
  
  ben.sunderland Dec 15, 2015 8:02 PM (in response to rashedtalukder)
  
  I tried but couldnt get the link you sent to work on the output from my REST call , just black lines output . it works from their sample which was ultra basic.
  
  Having paginated JSON is a pain to work with, at least from shell scripting - is it standard output format from REST APIs in general ?
  
  I guess I am learning that to do anything with our JSON output you need to be in a proper programming language (or be really good a shell / parsing ).
  I think I'm going to switch to Python.
  
  Like (0)
  
  Actions
  - Re: extract list of users to csv via REST + shell
    
    ben.sunderland Dec 15, 2015 10:47 PM (in response to ben.sunderland)
    
    OK I got it working using :
    
    1. https://stedolan.github.io/jq/download/ - This is jq that does the filtering from API JSON into simple JSON
    2. https://github.com/archan937/jsonv.sh - this converts the simple JSON into CSV format.
    
    Just was quite fiddly as shell scripts tend to be....
    
    Like (0)
    
    Actions
  - Re: extract list of users to csv via REST + shell
    
    rashedtalukder Dec 16, 2015 10:49 AM (in response to ben.sunderland)
    
    Sorry that link didn't work, but I'm glad you found something that would.
    
    Any internet media types are part of the RESTful standard (JSON, XML, HTML), but JSON is the simpler structured data type to work with when compared to XML. Your goal would honestly be easiest with a more sophisticated language but it can be done with either.
    
    Like (0)
    
    Actions
Re: extract list of users to csv via REST + shell

it2000 Dec 19, 2015 1:19 AM (in response to ben.sunderland)

I would parse every page and never combine them. With >100k users the file may get bit. With csplit /id/ one can seperate the users into single files and parsing single users with sed/awk should work fine.
Anyhow I'd write a Java program for this as it is easier to debug and maintain.
Like (0)

Actions
- Re: extract list of users to csv via REST + shell
  
  ben.sunderland Dec 19, 2015 2:15 AM (in response to it2000)
  
  I ended up doing in Java and shell. Though the shell one is only for smaller sites.
  The shell script was to explore how to do it more than being the best language for the job. Idea being a script is usually more universally understood and so more easily adjustable in Sys admin community.
  
  But Java is more fit for purpose here for sure.
  
  Ben Sunderland
  Jive Software | Sales Engineer
  m 61(0) 488 493799<tel:61(0)%20488%20493799>
  <http://www.jivesoftware.com/>http://www.jivesoftware.com<http://www.jivesoftware.com/>
  
  Like (0)
  
  Actions

Go to original post