Check if Wiki Markup is empty

[TomTom]

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

[Bob Swift OSS (Bob Swift Atlassian Apps) · Accepted solution]

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.

[TomTom]

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?

[TomTom]

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?

[TomTom]

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?

[TomTom]

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?

[Bob Swift OSS (Bob Swift Atlassian Apps)]

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}

[Bob Swift OSS (Bob Swift Atlassian Apps)]

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}

[Bob Swift OSS (Bob Swift Atlassian Apps)]

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}

[Bob Swift OSS (Bob Swift Atlassian Apps)]

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

[TomTom]

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

[TomTom]

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.

[Bob Swift OSS (Bob Swift Atlassian Apps)]

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.