- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
04-06-22 04:39 AM
Answered! Go to Answer.
Helpful Answers
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
06-06-22 09:35 AM
Please find the below query and also just to note you will get 0.00 values for logs having very less bytes in contrast to other higher values:
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
Also if it resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future
------------------------------
----------------------------------
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
Hope this helps you out and if so, please mark the current thread as the 'Answer', so others can refer to the same for reference in future.
Regards,
Devneet Mohanty,
SS&C Blueprism Community MVP 2024,
Automation Architect,
Wonderbotz India Pvt. Ltd.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
04-06-22 08:07 AM
You can refer to the below query for your requirement:
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
This query will give you the count of the log entries available for each process in descending order
------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
Hope this helps you out and if so, please mark the current thread as the 'Answer', so others can refer to the same for reference in future.
Regards,
Devneet Mohanty,
SS&C Blueprism Community MVP 2024,
Automation Architect,
Wonderbotz India Pvt. Ltd.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
04-06-22 08:15 AM
Thanks for the query, is there way that we could fetch the size of the logs per process in gb/mb
Original Message:
Sent: 06-04-2022 08:06
From: Devneet Mohanty
Subject: SQL query to fetch results for process logging extensively
Hi ,
You can refer to the below query for your requirement:
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
This query will give you the count of the log entries available for each process in descending order
------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
04-06-22 08:51 AM
Please find the below updated query:
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
Hope this helps you out.
------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
Hope this helps you out and if so, please mark the current thread as the 'Answer', so others can refer to the same for reference in future.
Regards,
Devneet Mohanty,
SS&C Blueprism Community MVP 2024,
Automation Architect,
Wonderbotz India Pvt. Ltd.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
04-06-22 09:05 AM
Original Message:
Sent: 06-04-2022 08:51
From: Devneet Mohanty
Subject: SQL query to fetch results for process logging extensively
Hi,
Please find the below updated query:
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
Hope this helps you out.
------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
06-06-22 09:12 AM
Can you please enhance the query to be in gb
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
06-06-22 09:35 AM
Please find the below query and also just to note you will get 0.00 values for logs having very less bytes in contrast to other higher values:
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
Also if it resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future
------------------------------
----------------------------------
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
Hope this helps you out and if so, please mark the current thread as the 'Answer', so others can refer to the same for reference in future.
Regards,
Devneet Mohanty,
SS&C Blueprism Community MVP 2024,
Automation Architect,
Wonderbotz India Pvt. Ltd.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
06-06-22 09:49 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
06-06-22 10:04 AM
Like I said depends on the precision on the values you have with you. If you increase the precision to 38 which is maximum for a decimal you will get the proper value but you need to do it for all the columns:
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
------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
------------------------------
Hope this helps you out and if so, please mark the current thread as the 'Answer', so others can refer to the same for reference in future.
Regards,
Devneet Mohanty,
SS&C Blueprism Community MVP 2024,
Automation Architect,
Wonderbotz India Pvt. Ltd.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
06-06-22 07:29 PM
Process name displaying as Null is occupying the highest space above 300 gb in prod, how to find that name
Original Message:
Sent: 06-06-2022 10:04
From: Devneet Mohanty
Subject: SQL query to fetch results for process logging extensively
Hi
Like I said depends on the precision on the values you have with you. If you increase the precision to 38 which is maximum for a decimal you will get the proper value but you need to do it for all the columns:
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
------------------------------
----------------------------------
Hope it helps you out and if my solution resolves your query, then please mark it as the 'Best Answer' so that the others members in the community having similar problem statement can track the answer easily in future
Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
----------------------------------
