How to use curl to export a list of macro occurences

[Adrian Morse]

Hello,

We use the LucidChart macro in multiple pages. I am trying to use CURL with CQL to output a list of occurrences, but it isn't working.

This is what I am trying:

curl -u MyUsername:MyPassword "https://MyCompany.com/rest/api/content/search?cql=(space=Public+macro="LucidChart")" -o "C:\Output.txt"

Output.txt is generated, but just contains one line which includes 'cannot parse cql'.

Any advice appreciated.

Thanks,

Adrian

[David Bakkers · Accepted solution]

@Adrian Morse 

If you look at the cURL examples on the Confluence Search Content page, it should be fairly obvious where you've used the wrong bracket type for delimiting the CQL within the URL

[Adrian Morse]

It sounds like you are suggesting that I replace the brackets with curly brackets. I just tried that and it still didn't work...

"Could not parse cql : space=Public macro=LucidChart","reason":"Bad Request"}

[Adrian Morse]

In contrast, if I run this command below (which does have non-curly brackets btw) the operation is successful:

curl -u user:password “https://MyCompany.com/rest/api/content/search?cql=(space=Public%20and%20type%=%20page%20and%20text%20~%20TEST)&limit=1000" -o “C:\Output.txt”

[David Bakkers]

No, what I meant was, there shouldn't be any brackets around the CQL statement; straight, curly or otherwise. CQL statments are all just declared directly in the URL, like this:

https://MyCompany.com/wiki/rest/api/content/search?cql=space=Public and macro="LucidChart" and other=other&whateverelse=whateverelse

The REST API knows that the first equals sign after the identifier 'jql' is the first delimiter and it assumes everything that follows is part of the CQL statement until an ampersand is found, or the end of the URL is reached.

Also, plus signs '+' are not a valid CQL operators. The operator is 'and' preceded and followed by a space, as per my example above and as per your second example.

The only reason your second example worked, the one that was inside some parentheses, was that you accidentally created a valid precedence.

As per the CQL documentation:

Setting precedence of operators

To enforce the precedence of operators, you can use parentheses in complex CQL statements.

For example, if you want to find all pages in the Developer space as well as all blog posts created by the the system administrator (bobsmith), you can use parentheses to enforce the precedence of the boolean operators in your query. For example:

(type=page and Space=DEV) OR (creator=bobsmith and type=blogpost)

Note: if you do not use parentheses, the statement will be evaluated left to right.

[Adrian Morse]

Oh I see. I just tried it without brackets but still no joy. Thanks anyway.

[Adrian Morse]

Even using "and" like in my other example I cannot get it to work

[David Bakkers]

Since you haven't provided the actual code example of 'it' nor provided the error response from the REST API, then I don't have much ability to help you much further.

Also, you said this works:

“https://MyCompany.com/rest/api/content/search?cql=(space=Public%20and%20type%=%20page%20and%20text%20~%20TEST)&limit=1000"

but that's not possible, since the URL to the endpoint is missing the '/wiki/' part. To work, it would have to be:

“https://MyCompany.com/wiki/rest/api/content/search?cql=(space=Public%20and%20type%=%20page%20and%20text%20~%20TEST)&limit=1000"

Sorry to be blunt, but you seem to be just guessing and hoping for the best, or just want someone else to proof-read your code. Try being methodical and using a process of elimination to find the mistakes in your cURL requests and the contents of your CQL statements.

[Adrian Morse]

Thanks David, 'll try to be more specific...

The code that works has “wiki” before the company name (I was considering this as part of the company name in my earlier post). So like this:

curl -u user:password “https://wiki.MyCompany.com/rest/api/content/search?cql=(space=Public%20and%20type%=%20page%20and%20text%20~%20TEST)&limit=1000" -o “C:\Output.txt”

The code that does not work:

curl -u user:password "https://wiki.MyCompany.com/rest/api/content/search?cql=space=Public%20and%20macro="LucidChart"” -o "C:\Output.txt"

When I say "does not work" I mean it just gives this following response even though the LucidChart macro is used in many places :

{"results":[],"start":0,"limit":25,"size":0,"_links":{"self":"https://wiki.MyCompany.com/rest/api/content/search?cql=space=Public%20and%20macro=LucidChart","base":"https://wiki.MyCompany.com","context":""}}

[David Bakkers]

Hello @Adrian Morse 

OK, I get what you mean now. Yes, for Confluence SERVER you don't need the /wiki/ in the URL path. I've been on Confluence CLOUD for so long that I forget that idiosyncrasy between the two.

I've personally never tried to use the REST API to use CQL to search for macros by their name inside content, but I'm curious, so I thought I'd give it a try for myself.

I created a new page, put some text on it, then added a Jira macro with a simple JQL lookup. I then inspected that page's HTML content to confirm the name of the macro:

So, I know the name of the macro is 'jira'.

I then constructed a request in Postman to the REST API to do a CQL search to try and find any pages in any spaces that had a macro of that name in them:

Which worked just perfectly. The query found the correct page, told me the page ID, which space that page is in, and that page's title is a match for my test page.

In summary, the ability to use the REST API to do a CQL search to find macros by their names seems to work correctly for Confluence CLOUD. As to why it's not working for you for Confluence SERVER, I can't help any further; you will have to just be methodical and double check your code and method.

1. Double check you really do have a space called 'Public'.
2. Double check the name of the macro you're looking for really is 'LucidChart'
3. Do a basic test first, like I did, and find a known macro by its known name first, to check your method.

Good luck.

[Adrian Morse]

Thanks for the info and suggestions.

So, I searched instead for the "info" macro using "macro=info" and I got lots of hits.

Then I inspected the web page code for a LucidChart diagram and it seems to be given by this:

data-macro-name="lucidchart-onprem"

So then I tried...

curl -u user:password "https://wiki.MyCompany.com/rest/api/content/search?cql=space=Public%20and%20macro="lucidchart-onprem"” -o "C:\Output.txt"

...and got this response:

{"statusCode":400,"data":{"authorized":false,"valid":true,"allowedInReadOnlyMode":true,"errors":[],"successful":false},"message":"Could not parse cql : space=Public and macro=lucidchart-onprem","reason":"Bad Request"}

So then I tried replacing the quotation marks around lucidchart-onprem with the URL encoding code for quotes (%22) ...

...macro=%22lucidchart-onprem%22”

...and it finally worked!

Thanks for your help!

[David Bakkers]

Excellent

Also, if you try:

"https://wiki.MyCompany.com/rest/api/content/search?cql=space=Public%20and%20macro=lucidchart-onprem"

you will probably find that works too.

Quotation marks are only required in CQL if the value being searched for contains a space, and Confluence usually gives things names without spaces, just to avoid that particular problem.