Converting query from Crystal Reports over to Bigfix 6 custom reports

Reporting
1

(imported topic written by larry.lotspeich91)

We have been using Crystal Reports for some time to automatically generate and mail out some monthly views of patch compliancy. I would like to move this over to Bigfix 6 now. Can someone help with this?

Here is a sample of the code:

SELECT t1.ComputerID, t5.ComputerName, t5.UserName, t5.LastReportTime, t1.ID AS ‘FixletID’, t2.Name, t3.SourceID, t3.Source, t3.Severity, t3.Category, t5.IPAddress, t5.MACAddress, t5.First3OctetsOfIP

FROM dbo.BES_RELEVANT_FIXLETS t1

INNER JOIN dbo.BES_FIXLETS t2 ON t1.ID = t2.ID

INNER JOIN

(

SELECT ID,

MAX(CASE WHEN SUBSTRING(PropertyName, 1, 15) = ‘Source ID’ THEN SUBSTRING(PropertyValue, 1, 20) ELSE ‘’ END) AS SourceID,

MAX(CASE WHEN SUBSTRING(PropertyName, 1, 15) = ‘Source’ THEN SUBSTRING(PropertyValue, 1, 20) ELSE ‘’ END) AS Source,

MAX(CASE WHEN SUBSTRING(PropertyName, 1, 15) = ‘Source Severity’ THEN SUBSTRING(PropertyValue, 1, 10) ELSE ‘’ END) AS Severity,

MAX(CASE WHEN SUBSTRING(PropertyName, 1, 8) = ‘Category’ THEN SUBSTRING(PropertyValue, 1, 20) ELSE ‘’ END) AS Category

FROM dbo.BES_FIXLET_PROPERTIES

WHERE SUBSTRING(PropertyName, 1, 15) IN (‘Source ID’, ‘Source’, ‘Source Severity’, ‘Category’)

GROUP BY ID

) AS t3 ON t2.ID = t3.ID

INNER JOIN

(

SELECT ComputerID,

MAX(CASE WHEN SUBSTRING(Name, 1, 10) = ‘Ip Address’ THEN SUBSTRING(Value, 1, 100) ELSE ‘’ END) AS IPAddress,

MAX(CASE WHEN SUBSTRING(Name, 1, 9) = ‘User Name’ THEN SUBSTRING(Value, 1, 20) ELSE ‘’ END) AS UserName,

MAX(CASE WHEN SUBSTRING(Name, 1, 16) = ‘Last Report Time’ THEN SUBSTRING(Value, 1, 35) ELSE ‘’ END) AS LastReportTime,

MAX(CASE WHEN SUBSTRING(Name, 1, 13) = ‘Computer Name’ THEN SUBSTRING(Value, 1, 20) ELSE ‘’ END) AS ComputerName,

MAX(CASE WHEN SUBSTRING(Name, 1, 11) = ‘MAC Address’ THEN SUBSTRING(Value, 1, 110) ELSE ‘’ END) AS MACAddress,

MAX(CASE WHEN SUBSTRING(Name, 1, 20) = ‘First 3 Octets of IP’ THEN SUBSTRING(Value, 1, 100) ELSE ‘’ END) AS First3OctetsOfIP

FROM dbo.BES_COLUMN_HEADINGS

WHERE SUBSTRING(Name, 1, 20) IN (‘IP Address’, ‘User Name’, ‘Last Report Time’, ‘Computer Name’, ‘MAC Address’, ‘First 3 Octets of IP’)

GROUP BY ComputerID

) as t5 ON t1.ComputerID = t5.ComputerID

WHERE

(

t5.First3OctetsOfIP LIKE '%10..

^0123456789

%’ OR

t5.First3OctetsOfIP LIKE '%10..

^0123456789

%’

)

AND

t3.Severity IN (‘Critical’, ‘Important’)

2

(imported comment written by jessewk)

Larry,

Can you post a sample of the output? As I read it, this prints out a line for each critical or important Fixlet that is relevant on each computer with a 10.x.x.x ip address. Is that correct?

-Jesse

3

(imported comment written by BenKus)

Hey Larry,

That is quite a beast of an expression. I imagine it takes a very long time to run and it will likely lock the database for a while when running. A custom report will have zero impact on the database and it will also likely run much faster. Hopefully the Web Reports interface is easier for you to use than the Crystal Reports interface.

This may take a few revisions, but this is what I have so far:

<table border=1> <tr> <td>Computer ID</td><td>Computer Name</td><td>IPs</td><td>User Name</td><td>Last Report Time</td> <td>Fixlet ID</td> <td>Fixlet Name</td> <td>Source</td> <td>Source ID</td> <td>Source Severity </td> <td>Category</td> </tr> <?relevance trs of (td of (id of item 1 of it as string) & td of name of item 1 of it & td of concatenation 
", " of values of results (bes property 
"IP Address", item 1 of it) & td of value of result (bes property 
"User Name", item 1 of it) & td of value of result (bes property 
"Last Report Time", item 1 of it) & td of (ID of item 0 of it as string) & td of name of item 0 of it & td of source of item 0 of it & td of source id of item 0 of it & td of source severity of item 0 of it & td of category of item 0 of it  )   of (relevant fixlets whose (source severity of it as lowercase = 
"critical" OR source severity of it as lowercase = 
"important") of it, it)   of bes computers whose (exists result (bes property 
"subnet address", it) whose (value of it starts with 
"192.168.100" OR value of it starts with 
"192.168.0") AND exists result (bes property 
"user name", it) AND exists result (bes property 
"last report time", it) AND exists result (bes property 
"ip address", it)   ) ?>   </table>

This only notes Fixlets (instead of Tasks) and I didn’t add the MAC Address property (which is like the IP address property and the multiple results will need to be concatenated). I know this works, but I am not sure if I caught all the error cases.

Let me know how that goes,

Ben

4

(imported comment written by BenKus)

By the way Larry, I really enjoy your avatar.

5

(imported comment written by larry.lotspeich91)

Thanks Ben! I will let you know the results.