Splitting a multi-value bes property (session relevance)

Reporting
1

(imported topic written by Bill.Ehardt)

I’m trying to move a SQL report from our old application to a WebReport, but am having trouble with the final piece. I created the needed properties including “SSLTask - Raw Values” which may have multiple values. Because of this, I’m basing my session rel on that property.

That property is set like the following -> “;;”, so I’d like to split it.

I’d like to do something like this, but I’m getting singular expression refers to nonexistant object (Makes sense)

( name of computer of it, (value of results (computer of it,bes property 
"SSLTask - Last Run")|
""), (preceding text of first 
";" of it) of values of it, (preceding text of first 
";" of (following text of first 
";" of values of it)), (following text of first 
";" of (following text of first 
";" of values of it)) ) of results of bes properties whose (name of it = 
"SSLTask - Raw Values")

I’m not sure on how to fix it, but I’ve gotten this far:

( name of computer of it, (value of results (computer of it,bes property 
"SSLTask - Last Run")|
""), (

if (exists values whose (it contains 
";") of it) then ((preceding text of first 
";" of it) of values of it) 

else (
"") ) ) of results of bes properties whose (name of it = 
"SSLTask - Raw Values")

As soon as I set the other substrings with an if/then I start getting extra results.

Any ideas?

2

(imported comment written by NoahSalzman)

Can you just use “substrings separated by “;” of values of it”?

3

(imported comment written by Bill.Ehardt)

That gives me extra lines as it splits up each of the parts into a new line, so I get something like

Server, LastRun, startdate

Server, LastRun, enddate

Server, LastRun, certname

etc

4

(imported comment written by Bill.Ehardt)

I am trying from bes computers as well, but I keep getting a “singular expression refers to a non-unique object” when I do something as simple as:

(value of results from (bes properties 
"SSLTask - Raw Values") of it | 
"") of bes computers

When I change it to “values” it gives me a singular expression is required.

5

(imported comment written by Bill.Ehardt)

I guess it boils down to… how do I split an individual line, not the whole thing.

So if I have:

“A”,“B”,“C;D;E”

I want

“A”,“B”,“C”,“D”,“E”

not

“A”,“B”,“C”

“A”,“B”,“D”

“A”,“B”,“E”

Every time I try to take substrings, concat, etc… it looks at the entire output, not just that individual line.

6

(imported comment written by NoahSalzman)

I need a little help visualizing what the real data looks like. Can you post a few lines of the output from this query:

(

name of computer of it,

(value of results (computer of it,bes property “SSLTask - Last Run”)|""),

values of it,

)

of results of bes properties whose (name of it = “SSLTask - Raw Values”)

7

(imported comment written by Bill.Ehardt)

Thanks Noah, Lee Wei surprised me to help me out with this and I got it working!

To make a long story short, I just about had it but had some problems with concatenating multiple values.

For completion sake, the final HTML is below:

(html 
"<table border=1>" & it & html 
"</table>") of concatenations of trs of (item 0 of it & item 1 of it & item 2 of it) of ( td of name of computer of it, td of (value of results (computer of it,bes property 
"SSLTask - Last Run")|
""), (

if (length of concatenations of values of it != 0) then ( (td of preceding text of first 
";" of it & td of preceding text of first 
";" of following text of first 
";" of it & td of following text of last 
";" of it) of        values of it ) 

else (td of 
" " & td of 
" " & td of 
" ")) ) of results of bes properties whose (name of it = 
"SSLTask - Raw Values")

Had to do some trickery with tuples to get it to format properly on a 1:1:1 value for properties.