Custom Report URLs

Reporting
4

This will probably run quite slowly, I was searching for some very limited data when I originally wrote this and didn’t need to much worry about the efficiency, but I hope it’s helpful or that you can at least reuse pieces of this SQL query:

SELECT ReportName,Creator,CreatorName, isPublic, ReportXML, TextResult
FROM 
(
	SELECT ReportName,Creator,LoginName AS CreatorName, isPublic,ReportXML
	FROM [BESReporting].[dbo].[WEBREPORTS] 
	LEFT JOIN
	[BESReporting].[dbo].[USERS]
	ON Creator=UserID
) As Reports
INNER JOIN
(
	select * from
	(
		SELECT
			[ShortText],
			FullText,
			CONVERT(varchar(max),CAST(FullText AS varbinary(max))) AS TextResult
		FROM [BESReporting].[dbo].[SHORTENED_TEXT]
	) as TempTable
) AS MyShortText
 ON Reports.ReportXML LIKE '%' + MyShortText.ShortText + '%'

My sample report has two SHORTENED_TEXT substitutions in it - one looks like the Sort and another for the Columns Displayed. The MyShortText column can have a value like

"sort=R-Computer%20Name&dir=asc&startIndex=0&results=50&c=R-Computer%20Name&c=O-8361-34-2&c=R-IP%20Address&c=R-OS&c=R-CPU&c=R-Last%20Report%20Time"

This shows several Reserved Properties by name - like “R-Computer%20Name” is the (Reserved) Property “Computer Name”; it also uses the non-reserved property “O-8361-34-2” which would be the second property from Analysis 34 in site 8361. In Session Relevance I can look that up (in the Console Debugger or Web Reports QNA page) as

Q: (name of it, name of source analysis of it | "none", name of site of source analysis of it | "none") of bes properties whose (id of it = ( 8361, 34, 2 ))
A: Model, Hardware Information, OS Deployment and Bare Metal Imaging

I also have a second match from this Analysis showing the Columns that are displayed. The result for SHORTENED_TEXT on that one is in JSON format -

{"filterName":"","filterID":"","filterDescription":"{\"matchType\":\"all\",\"conditionList\":[{\"selectedContentTypeName\":\"Computer\",\"selectorList\":[{\"selectedOperatorName\":\"contains\",\"selectedOperatorValue\":\"Win\"}],\"selectedProperty\":{\"analysis\":null,\"type\":\"reserved\",\"id\":\"R-OS\",\"name\":\"OS\",\"displayName\":\"OS\"}},{\"selectedContentTypeName\":\"Computer\",\"selectorList\":[{\"selectedOperatorName\":\"contains\",\"selectedOperatorValue\":\"VMWare\"}],\"selectedProperty\":{\"analysis\":{\"id\":34,\"siteid\":8361,\"sitename\":\"OS Deployment and Bare Metal Imaging\",\"dbid\":2299732975,\"name\":\"Hardware Information\"},\"type\":\"analysis\",\"id\":\"O-8361-34-2\",\"name\":\"Model\",\"displayName\":\"Model (Hardware Information)\"}}]}"}

The JSON result is probably a bit easier to read because it shows both the Object ID for the property, as well as its displayName and the source analysis’ Display Name

2 Likes