Jump to content

Sign in to follow this  

Custom Report Help SCCM

Recommended Posts

Hello, I'm trying to customize a SQL query. I took a few things out from the original for my inviroment but was wondering if it is possible to add "Total Minutes on Console" and "Last Console use" to this report? Any help is much appriciated!


Mine looks like this now:


SELECT CASE WHEN v_GS_PC_BIOS.SerialNumber0 LIKE 'vmware%' THEN 'VM' ELSE v_GS_PC_BIOS.SerialNumber0 END AS [serial No],
v_GS_PC_BIOS.Manufacturer0, v_GS_PROCESSOR.Name0 AS [Processor],
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 / 1024 AS [Memory(MB)], c.IPAddress AS [iP Address],
v_GS_OPERATING_SYSTEM.InstallDate0, 101) AS [installed Date], CONVERT(nvarchar(10), v_GS_Operating_System.LastBootUpTime0, 101) AS [Rebooted On],
v_R_System.User_Name0, v_R_System.Resource_Domain_OR_Workgr0

(SELECT t1.resourceid AS rsid2, IPAddress = substring
((SELECT (IP_Addresses0 + ', ')

FROM v_RA_System_IPAddresses t2
WHERE t2.IP_Addresses0 NOT LIKE '169%' AND IP_Addresses0 NOT LIKE '0.%' AND IP_Addresses0 NOT LIKE '%::%' AND
IP_Addresses0 NOT LIKE '192.%' AND t1.resourceid = t2.resourceid
ORDER BY resourceid FOR xml path('')), 1, 50000)

FROM v_RA_System_IPAddresses t1
GROUP BY resourceid) c ON c.rsid2 = v_R_System.resourceid JOIN

v_GS_PC_BIOS ON v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID JOIN
v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_Operating_System.ResourceID JOIN
v_GS_X86_PC_MEMORY ON v_R_System.ResourceID = v_GS_X86_PC_MEMORY.ResourceID JOIN
v_GS_PROCESSOR ON v_R_System.ResourceID = v_GS_PROCESSOR.ResourceID
ORDER BY v_R_System.Netbios_Name0, v_GS_PC_BIOS.SerialNumber0, v_GS_Operating_System.Caption0,
v_GS_Operating_System.Version0, v_GS_Operating_System.LastBootUpTime0, v_R_System.User_Name0, v_R_System.Resource_Domain_OR_Workgr0,

Share this post

Link to post
Share on other sites

Total Minutes on Console" and "Last Console use" are available from v_GS_SYSTEM_CONSOLE _USER .

try to use v_GS_SYSTEM_CONSOLE_USER SCU ON SCU.ResourceID=v_R_System.ResourceID in your query to report the values.

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Create New...