04-06-22 04:39 AM
Answered! Go to Answer.
06-06-22 09:35 AM
SELECT processname,CONVERT(DECIMAL(7,2),Sum(
IsNull(DataLength(sessionnumber), 1)
+ IsNull(DataLength(stageid), 1)
+ IsNull(DataLength(stagename), 1)
+ IsNull(DataLength(stagetype), 1)
+ IsNull(DataLength(processname), 1)
+ IsNull(DataLength(pagename), 1)
+ IsNull(DataLength(objectname), 1)
+ IsNull(DataLength(actionname), 1)
+ IsNull(DataLength(result), 1)
+ IsNull(DataLength(resulttype), 1)
+ IsNull(DataLength(startdatetime), 1)
+ IsNull(DataLength(enddatetime), 1)
+ IsNull(DataLength(attributexml), 1)
+ IsNull(DataLength(automateworkingset), 1)
+ IsNull(DataLength(targetappname), 1)
+ IsNull(DataLength(targetappworkingset), 1)
)/1073741824) AS session_log_size
From
BPASessionLog_NonUnicode
GROUP BY
processname
ORDER BY session_log_size DESC
04-06-22 08:07 AM
SELECT A.processname,COUNT(A.logid) AS log_entries
FROM [Blue Prism].[dbo].[BPASessionLog_NonUnicode] A INNER JOIN [Blue Prism].[dbo].[BPAProcess] B
ON A.processname = B.name
GROUP BY A.processname
ORDER BY COUNT(A.logid) DESC
04-06-22 08:15 AM
SELECT A.processname,COUNT(A.logid) AS log_entriesFROM [Blue Prism].[dbo].[BPASessionLog_NonUnicode] A INNER JOIN [Blue Prism].[dbo].[BPAProcess] B ON A.processname = B.nameGROUP BY A.processnameORDER BY COUNT(A.logid) DESC
04-06-22 08:51 AM
SELECT processname,
Sum(
IsNull(DataLength(sessionnumber), 1)
+ IsNull(DataLength(stageid), 1)
+ IsNull(DataLength(stagename), 1)
+ IsNull(DataLength(stagetype), 1)
+ IsNull(DataLength(processname), 1)
+ IsNull(DataLength(pagename), 1)
+ IsNull(DataLength(objectname), 1)
+ IsNull(DataLength(actionname), 1)
+ IsNull(DataLength(result), 1)
+ IsNull(DataLength(resulttype), 1)
+ IsNull(DataLength(startdatetime), 1)
+ IsNull(DataLength(enddatetime), 1)
+ IsNull(DataLength(attributexml), 1)
+ IsNull(DataLength(automateworkingset), 1)
+ IsNull(DataLength(targetappname), 1)
+ IsNull(DataLength(targetappworkingset), 1)
) session_log_size
From
BPASessionLog_NonUnicode
GROUP BY
processname
ORDER BY session_log_size DESC
04-06-22 09:05 AM
SELECT processname,Sum(IsNull(DataLength(sessionnumber), 1)+ IsNull(DataLength(stageid), 1)+ IsNull(DataLength(stagename), 1)+ IsNull(DataLength(stagetype), 1)+ IsNull(DataLength(processname), 1)+ IsNull(DataLength(pagename), 1)+ IsNull(DataLength(objectname), 1)+ IsNull(DataLength(actionname), 1)+ IsNull(DataLength(result), 1)+ IsNull(DataLength(resulttype), 1)+ IsNull(DataLength(startdatetime), 1)+ IsNull(DataLength(enddatetime), 1)+ IsNull(DataLength(attributexml), 1)+ IsNull(DataLength(automateworkingset), 1)+ IsNull(DataLength(targetappname), 1)+ IsNull(DataLength(targetappworkingset), 1)) session_log_sizeFromBPASessionLog_NonUnicodeGROUP BYprocessnameORDER BY session_log_size DESC
06-06-22 09:12 AM
06-06-22 09:35 AM
SELECT processname,CONVERT(DECIMAL(7,2),Sum(
IsNull(DataLength(sessionnumber), 1)
+ IsNull(DataLength(stageid), 1)
+ IsNull(DataLength(stagename), 1)
+ IsNull(DataLength(stagetype), 1)
+ IsNull(DataLength(processname), 1)
+ IsNull(DataLength(pagename), 1)
+ IsNull(DataLength(objectname), 1)
+ IsNull(DataLength(actionname), 1)
+ IsNull(DataLength(result), 1)
+ IsNull(DataLength(resulttype), 1)
+ IsNull(DataLength(startdatetime), 1)
+ IsNull(DataLength(enddatetime), 1)
+ IsNull(DataLength(attributexml), 1)
+ IsNull(DataLength(automateworkingset), 1)
+ IsNull(DataLength(targetappname), 1)
+ IsNull(DataLength(targetappworkingset), 1)
)/1073741824) AS session_log_size
From
BPASessionLog_NonUnicode
GROUP BY
processname
ORDER BY session_log_size DESC
06-06-22 09:49 AM
06-06-22 10:04 AM
SELECT processname,CONVERT(DECIMAL(38,2),Sum(
CONVERT(DECIMAL(38,2), IsNull(DataLength(sessionnumber), 1))
+ CONVERT(DECIMAL(38,2), IsNull(DataLength(stageid), 1))
+ CONVERT(DECIMAL(38,2), IsNull(DataLength(stagename), 1))
+ CONVERT(DECIMAL(38,2), IsNull(DataLength(stagetype), 1))
+ CONVERT(DECIMAL(38,2), IsNull(DataLength(processname), 1))
+ CONVERT(DECIMAL(38,2), IsNull(DataLength(pagename), 1))
+ CONVERT(DECIMAL(38,2), IsNull(DataLength(objectname), 1))
+ CONVERT(DECIMAL(38,2), IsNull(DataLength(actionname), 1))
+ CONVERT(DECIMAL(38,2), IsNull(DataLength(result), 1))
+ CONVERT(DECIMAL(38,2), IsNull(DataLength(resulttype), 1))
+ CONVERT(DECIMAL(38,2), IsNull(DataLength(startdatetime), 1))
+ CONVERT(DECIMAL(38,2), IsNull(DataLength(enddatetime), 1))
+ CONVERT(DECIMAL(38,2), IsNull(DataLength(attributexml), 1))
+ CONVERT(DECIMAL(38,2), IsNull(DataLength(automateworkingset), 1))
+ CONVERT(DECIMAL(38,2), IsNull(DataLength(targetappname), 1))
+ CONVERT(DECIMAL(38,2), IsNull(DataLength(targetappworkingset), 1))
)/1073741824) AS session_log_size
From
BPASessionLog_NonUnicode
GROUP BY
processname
ORDER BY session_log_size DESC
NOTE: If you have very small values even 2 decimal precision will show you 0.00 and won't suffice in that case try with some parameter like (38,12)
. You need to experiment here and see what fits your use case according to your data
SELECT processname,CONVERT(DECIMAL(38,12),Sum(
CONVERT(DECIMAL(38,12), IsNull(DataLength(sessionnumber), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(stageid), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(stagename), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(stagetype), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(processname), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(pagename), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(objectname), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(actionname), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(result), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(resulttype), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(startdatetime), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(enddatetime), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(attributexml), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(automateworkingset), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(targetappname), 1))
+ CONVERT(DECIMAL(38,12), IsNull(DataLength(targetappworkingset), 1))
)/1073741824) AS session_log_size
From
BPASessionLog_NonUnicode
GROUP BY
processname
ORDER BY session_log_size DESC
06-06-22 07:29 PM
SELECT processname,CONVERT(DECIMAL(38,2),Sum(CONVERT(DECIMAL(38,2), IsNull(DataLength(sessionnumber), 1))+ CONVERT(DECIMAL(38,2), IsNull(DataLength(stageid), 1))+ CONVERT(DECIMAL(38,2), IsNull(DataLength(stagename), 1))+ CONVERT(DECIMAL(38,2), IsNull(DataLength(stagetype), 1))+ CONVERT(DECIMAL(38,2), IsNull(DataLength(processname), 1))+ CONVERT(DECIMAL(38,2), IsNull(DataLength(pagename), 1))+ CONVERT(DECIMAL(38,2), IsNull(DataLength(objectname), 1))+ CONVERT(DECIMAL(38,2), IsNull(DataLength(actionname), 1))+ CONVERT(DECIMAL(38,2), IsNull(DataLength(result), 1))+ CONVERT(DECIMAL(38,2), IsNull(DataLength(resulttype), 1))+ CONVERT(DECIMAL(38,2), IsNull(DataLength(startdatetime), 1))+ CONVERT(DECIMAL(38,2), IsNull(DataLength(enddatetime), 1))+ CONVERT(DECIMAL(38,2), IsNull(DataLength(attributexml), 1))+ CONVERT(DECIMAL(38,2), IsNull(DataLength(automateworkingset), 1))+ CONVERT(DECIMAL(38,2), IsNull(DataLength(targetappname), 1))+ CONVERT(DECIMAL(38,2), IsNull(DataLength(targetappworkingset), 1)))/1073741824) AS session_log_sizeFromBPASessionLog_NonUnicodeGROUP BYprocessnameORDER BY session_log_size DESC
NOTE: If you have very small values even 2 decimal precision will show you 0.00 and won't suffice in that case try with some parameter like (38,12)
. You need to experiment here and see what fits your use case according to your data
SELECT processname,CONVERT(DECIMAL(38,12),Sum(CONVERT(DECIMAL(38,12), IsNull(DataLength(sessionnumber), 1))+ CONVERT(DECIMAL(38,12), IsNull(DataLength(stageid), 1))+ CONVERT(DECIMAL(38,12), IsNull(DataLength(stagename), 1))+ CONVERT(DECIMAL(38,12), IsNull(DataLength(stagetype), 1))+ CONVERT(DECIMAL(38,12), IsNull(DataLength(processname), 1))+ CONVERT(DECIMAL(38,12), IsNull(DataLength(pagename), 1))+ CONVERT(DECIMAL(38,12), IsNull(DataLength(objectname), 1))+ CONVERT(DECIMAL(38,12), IsNull(DataLength(actionname), 1))+ CONVERT(DECIMAL(38,12), IsNull(DataLength(result), 1))+ CONVERT(DECIMAL(38,12), IsNull(DataLength(resulttype), 1))+ CONVERT(DECIMAL(38,12), IsNull(DataLength(startdatetime), 1))+ CONVERT(DECIMAL(38,12), IsNull(DataLength(enddatetime), 1))+ CONVERT(DECIMAL(38,12), IsNull(DataLength(attributexml), 1))+ CONVERT(DECIMAL(38,12), IsNull(DataLength(automateworkingset), 1))+ CONVERT(DECIMAL(38,12), IsNull(DataLength(targetappname), 1))+ CONVERT(DECIMAL(38,12), IsNull(DataLength(targetappworkingset), 1)))/1073741824) AS session_log_sizeFromBPASessionLog_NonUnicodeGROUP BYprocessnameORDER BY session_log_size DESC