Check if Wiki Markup is empty

Hi!

I have several pages using a Wiki Markup with a SQL statement inside (sql macro). Those sql queries are database checks I am performing.

Now, I want to create an overview of all the checks I am running within those subsites.
This overview should be a traffic light status layout which gives me green or red status lights based on if there is a result in the sql statement of the subsite or not.

What I have figured out is, that a possible soltution would be to have two sections on such a subsite:
1. the query itself
2. a check which returns {status:color=red|title=issues found} if the sql query returns any results and status:green if the query does not return any results.

Then I would be able to create an overview page containing a table and each row of this table contains the {status} syntax of a subsite (i think there is the possibility to show only a specific section of a page).

Does someone have an idea how this could work out?
How could I run a check against a Wiki Markup to figure out if it is empty or not?

Thanks in advance,
Tom

1 answer

1 accepted

You could construct a similar query that is based on the count of rows returned and uses an SQL case statement or similar and returns the text you want to display. For example:

select case 
    when (select count(*) from test_address1 where last = 'Smith') > 0 
    then '{status:color=red|title=issues found}' 
    else '{status:color=green|title=No issues!}' 
end

Use this on your summary page or, if you have to, put that data into metadata or something on the subsite page and bring it in using reporting macro or similar.

Hi Bob, thanks for the quick response!

I actually had that, but it didnt work for me so I thought this is not possible.

My markup contains:

{sql:DataSource=datasource|output=html|table=false|macros=true}
select case
    when (SELECT 0) = 1
    then '{status:color=red|title=issues found}'
    else '{status:color=green|title=No issues!}'
end
{sql}

The result looks like this:




Am I missing any parameters here?

Hi Bob, thanks for the quick response!

I actually had that, but it didnt work for me so I thought this is not possible.

My markup contains:

{sql:DataSource=datasource|output=html|table=false|macros=true}
select case
    when (SELECT 0) = 1
    then '{status:color=red|title=issues found}'
    else '{status:color=green|title=No issues!}'
end
{sql}

The result looks like this:

Am I missing any parameters here?

Hi Bob, thanks for the quick response!

I actually had that, but it didnt work for me so I thought this is not possible.

My markup contains:

{sql:DataSource=datasource|output=html|table=false|macros=true}
select case
    when (SELECT 0) = 1
    then '{status:color=red|title=issues found}'
    else '{status:color=green|title=No issues!}'
end
{sql}

The result looks like this:

Am I missing any parameters here?

Hi Bob, thanks for the quick response!

I actually had that, but it didnt work for me so I thought this is not possible.

My markup contains:

{sql:DataSource=datasource|output=html|table=false|macros=true}
select case
    when (SELECT 0) = 1
    then '{status:color=red|title=issues found}'
    else '{status:color=green|title=No issues!}'
end
{sql}

The result looks like this:

Am I missing any parameters here?

Try

{sql:DataSource=datasource|output=wiki|table=false}
select case
    when (SELECT 0) = 1
    then '{status:color=red|title=issues found}'
    else '{status:color=green|title=No issues!}'
end
{sql}

Try

{sql:DataSource=datasource|output=wiki|table=false}
select case
    when (SELECT 0) = 1
    then '{status:color=red|title=issues found}'
    else '{status:color=green|title=No issues!}'
end
{sql}

I think you want something more like:

{sql:DataSource=datasource|output=wiki|table=false}
select case
    when (SELECT 0) = 1
    then '{status:color=red|title=issues found}'
    else '{status:color=green|title=No issues!}'
end
{sql}

See SQL Plugin 4.1.0 released for more information on why your markup may not work as you expected.

Oh, this works great now.
Thank you very much for your help, Bob!

I have to follow this up:
What did you mean with "put that data into metadata or something on the subsite page and bring it in using reporting macro or similar."

Is there a possiblity to also implement this using the reporting plugin? Is there a reporter that can pull the information if a sql query on a child page is empty or not? Or if the Wiki markup is empty or not?
Without storing the result of the sql queries as metadata?

Because then I would not have to duplicate each query, which makes maintaining the queries much easier.

I think you need to duplicate the queries, the comment was related to where you wanted to put the 2nd query. On the summary page is the easiest. If you needed to put it on the subsite page, then you need a mechanism to bring it back to the summary page. Reporting/metadata might be able to help with that.

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,203 views 13 19
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot