Help Adding User to Disk Space By Computer Report

[amandaw33]

I have an existing report (sql below) to easily run reports based on amount of free disk space (I probably got the report from Connect).  It works great, except I'd love to have the primary user listed on the report.

Can some SQL pro help me modify the report to also have that?  Right now I have to look up the computer names one by one to correlate them to user's almost out of space (thanks windows update!).

 

Thank you!

I can't add code snippet withou an error, so will attach as text file

Attachment(s)

mysql.txt   2 KB 1 version

[Tyler Russell]

Hi Sally, 

I created a Low Disk Space report a year or so ago (probably with the help of Connect). You can see my code below but in plain English, you should be able to join the Inv_AeX_AC_Primary_User table to your query. This should contain the username. Also, I'm no SQL guru, so please go easy on me if there are better ways to do what I've done. 

 

SELECT
--vc.[Guid] AS ResourceGuid,
vc.[Name] AS [Computer Name],
pu.[User] as [Primary User],
vc.[OS Name] as [Operating System],
ld.[Device ID] AS Drive,
it.[Name] as [Site Name],
inv.LastBasicInventoryUpdate as [Last Basic Inventory Update],
--ld.[Description] AS [Description],
--ld.[Size (Bytes)] AS [Disk Size Bytes],
(ld.[Size (Bytes)])/1024/1024/1024 AS [Disk Size GB],
--ld.[Free Space (Bytes)] AS [Free Space Bytes],
(ld.[Free Space (Bytes)])/1024/1024 AS [Free Space MB],
(ld.[Free Space (Bytes)])/1024/1024/1024 AS [Free Space GB],
CONVERT(DECIMAL(5,2),100.0 * ld.[Free Space (Bytes)] / ld.[Size (Bytes)]) AS [Free Space(%)] --this is the percentage field

FROM vComputer vc

INNER JOIN vHWLogicalDisk ld ON vc.[Guid] = ld._ResourceGuid
LEFT JOIN Inv_AeX_AC_Primary_User pu ON vc.[Guid] = pu._ResourceGuid
left join Inv_AeX_AC_Client_Status inv on inv._ResourceGuid = vc.Guid
Left Join  [ResourceAssociation] raloc on vc.guid =  raloc.[ParentResourceGuid] and raloc.ResourceassociationtypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
Left Join item it on it.Guid = raloc.[ChildResourceGuid] and raloc.ResourceassociationtypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'

WHERE pu.[Month] = DATENAME(mm, GETDATE())
 AND LOWER(ld.[Description]) LIKE '%local%'
 AND (ld.[Free Space (Bytes)]/1024/1024/1024) < '10' --free space as GB number
 --AND CONVERT(DECIMAL(5,2),100.0 * ld.[Free Space (Bytes)] / ld.[Size (Bytes)]) <= '.01' ---this is the percentage parameter
 AND (ld.[Size (Bytes)]/1024/1024/1024) > '100' --exclude computers with disks smaller than 100 GB
 AND ld.[Device ID] like 'C%' --only look at C drive
 AND vc.[OS Name] not like '%server%'
 AND vc.IsManaged = 1
 AND it.[Name] like 'US-%'
AND DATEDIFF (DD, inv.LastBasicInventoryUpdate, GETDATE()) < 7
-- and vc.Name like 'FRG%'
 
--ORDER BY [Free Space(%)] ASC, [Free Space MB] ASC
ORDER BY it.Name		

Hope this helps! 

[JoeVan]

Sally,

I modified your SQL to add the UserID and User Name.  I also adjusted the displayed drive values to GB since its a better measure than MB these days. 

DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
SELECT DISTINCT i.Guid, 
i.Name [Computer Name], 
i.Domain [Domain], 
i.[User] [User ID],
vu.[Display Name],
CAST (ISNULL (logical.[Total Disk Space]/ (1024),0) AS DECIMAL (15,0)) [Total Disk Space (GB)], 
ISNULL (storage.[Number of Hard Drives],0) [Number of Hard Drives], 
CAST (ISNULL (logical.FreeSpace /1024 ,0)  AS Decimal (15,2)) [Free Disk Space (GB)],
CAST (ISNULL (primarydrive.[Free Space on Primary Drive] / (1024),0) AS DECIMAL (15,0)) [Free space on primary logical volume (GB)]
FROM dbo.vComputer i
JOIN dbo.Inv_Inventory_Results ir
    ON ir._ResourceGuid = i.Guid
JOIN dbo.ScopeMembership sm
    ON sm.[ResourceGuid] = i.Guid
    AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope)) 
LEFT JOIN (SELECT COUNT (1) [Number of Hard Drives], s._ResourceGuid FROM dbo.vHWStorage s
WHERE s.[Media Type] = 29 GROUP BY s._ResourceGuid) storage
    ON storage._ResourceGuid = i.Guid
LEFT JOIN 
(
    --select summary info for logical disks based on hard drives
       -- ET#1832291 Sagar Bhat 02-Dec-2010 Removed single quotes from defination of 'FreeSapce'
       SELECT SUM ([Free Space (Bytes)] / (1024.0 * 1024.0)) FreeSpace, MAX ([Free Space (Bytes)] / (1024.0 * 1024.0)) [Max Free Space], SUM ([Size (Bytes)]/ (1024 * 1024)) [Total Disk Space] ,
    _ResourceGuid [Guid] FROM vHWLogicalDisk WHERE [Logical Disk Type] = 3 GROUP BY _ResourceGuid
) logical ON i.Guid = logical.Guid
LEFT JOIN dbo.vOSOperatingSystem os
    ON os._ResourceGuid = i.Guid
LEFT JOIN (SELECT DISTINCT ld.[Free Space (Bytes)] / (1024 * 1024) [Free Space on Primary Drive], ld._ResourceGuid  FROM vHWLogicalDisk ld
        LEFT JOIN dbo.vOSOperatingSystemWindows osw
            ON ld.[Device ID] = SUBSTRING (osw.[Windows Directory], 1, 2)
            AND ld._ResourceGuid = osw._ResourceGuid
        LEFT JOIN dbo.vOSOperatingSystem os
            ON os._ResourceGuid = ld._ResourceGuid
        WHERE CASE WHEN osw._ResourceGuid IS NULL AND ld.[Device ID] = os.[Boot Device] THEN 1
        WHEN ld.[Device ID] = SUBSTRING (osw.[Windows Directory], 1, 2) THEN 1 
        ELSE 0 END = 1) primarydrive 
    ON primarydrive._ResourceGuid = i.Guid
    LEFT JOIN vUser vu on vu.Name = i.[User] and i.[User] != ''
WHERE LOWER (i.Name) LIKE LOWER ('%Computer Name%')
    AND (('%Less than 'N' Free disk space on primary drive%' = '%') OR (primarydrive.[Free Space on Primary Drive] / (1.0) IS NULL) OR ('%Less than 'N' Free disk space on primary drive%' <> '%' AND primarydrive.[Free Space on Primary Drive] < CAST (REPLACE ('%Less than 'N' Free disk space on primary drive%','%','')AS FLOAT)))
    AND (('%Total free disk space in MB or %%' = '%') OR ('%Total free disk space in MB or %%' <> '%' AND CAST (ISNULL (logical.FreeSpace,0)  AS Decimal (15,3))  >= CAST (REPLACE ('%Total free disk space in MB or %%','%','') AS FLOAT)))
    AND ('%More than 'N' Free Space in MB%' = '%' OR CAST (REPLACE ('%More than 'N' Free Space in MB%','%','') AS FLOAT) < CAST (ISNULL (logical.FreeSpace,0)  AS Decimal (15,3))) -- ET#1832291 Sagar Bhat 02-Dec-2010 Changed table column from [Max Free Space] to FreeSapce

[--]

Hi Tyler,

 

the SQL provided by Joe is very good!, I have added display name as it appears in windows start menu:

DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
SELECT DISTINCT i.Guid, 
i.Name [Computer Name], 
i.Domain [Domain], 
i.[User] [User ID],
vu.[Display Name],
,case when ua.[Full Name] is NULL then ISNULL(vc.[User],'') else ISNULL(ua.[Full Name],'') end [Full Name as displayed in windows start menu] 
CAST (ISNULL (logical.[Total Disk Space]/ (1024),0) AS DECIMAL (15,0)) [Total Disk Space (GB)], 
ISNULL (storage.[Number of Hard Drives],0) [Number of Hard Drives], 
CAST (ISNULL (logical.FreeSpace /1024 ,0)  AS Decimal (15,2)) [Free Disk Space (GB)],
CAST (ISNULL (primarydrive.[Free Space on Primary Drive] / (1024),0) AS DECIMAL (15,0)) [Free space on primary logical volume (GB)]
FROM dbo.vComputer i
JOIN dbo.Inv_Inventory_Results ir
    ON ir._ResourceGuid = i.Guid
JOIN dbo.ScopeMembership sm
    ON sm.[ResourceGuid] = i.Guid
    AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope)) 
LEFT JOIN (SELECT COUNT (1) [Number of Hard Drives], s._ResourceGuid FROM dbo.vHWStorage s
WHERE s.[Media Type] = 29 GROUP BY s._ResourceGuid) storage
    ON storage._ResourceGuid = i.Guid
LEFT JOIN 
(
    --select summary info for logical disks based on hard drives
       -- ET#1832291 Sagar Bhat 02-Dec-2010 Removed single quotes from defination of 'FreeSapce'
       SELECT SUM ([Free Space (Bytes)] / (1024.0 * 1024.0)) FreeSpace, MAX ([Free Space (Bytes)] / (1024.0 * 1024.0)) [Max Free Space], SUM ([Size (Bytes)]/ (1024 * 1024)) [Total Disk Space] ,
    _ResourceGuid [Guid] FROM vHWLogicalDisk WHERE [Logical Disk Type] = 3 GROUP BY _ResourceGuid
) logical ON i.Guid = logical.Guid
LEFT JOIN dbo.vOSOperatingSystem os
    ON os._ResourceGuid = i.Guid
LEFT JOIN (SELECT DISTINCT ld.[Free Space (Bytes)] / (1024 * 1024) [Free Space on Primary Drive], ld._ResourceGuid  FROM vHWLogicalDisk ld
        LEFT JOIN dbo.vOSOperatingSystemWindows osw
            ON ld.[Device ID] = SUBSTRING (osw.[Windows Directory], 1, 2)
            AND ld._ResourceGuid = osw._ResourceGuid
        LEFT JOIN dbo.vOSOperatingSystem os
            ON os._ResourceGuid = ld._ResourceGuid
        WHERE CASE WHEN osw._ResourceGuid IS NULL AND ld.[Device ID] = os.[Boot Device] THEN 1
        WHEN ld.[Device ID] = SUBSTRING (osw.[Windows Directory], 1, 2) THEN 1 
        ELSE 0 END = 1) primarydrive 
    ON primarydrive._ResourceGuid = i.Guid
    LEFT JOIN vUser vu on vu.Name = i.[User] and i.[User] != ''
    left join Inv_UG_User_Account ua on ua._ResourceGuid = vc.[Guid] and i.Domain+'\'+i.[User] = ua.[User Name]
WHERE LOWER (i.Name) LIKE LOWER ('%Computer Name%')
    AND (('%Less than 'N' Free disk space on primary drive%' = '%') OR (primarydrive.[Free Space on Primary Drive] / (1.0) IS NULL) OR ('%Less than 'N' Free disk space on primary drive%' <> '%' AND primarydrive.[Free Space on Primary Drive] < CAST (REPLACE ('%Less than 'N' Free disk space on primary drive%','%','')AS FLOAT)))
    AND (('%Total free disk space in MB or %%' = '%') OR ('%Total free disk space in MB or %%' <> '%' AND CAST (ISNULL (logical.FreeSpace,0)  AS Decimal (15,3))  >= CAST (REPLACE ('%Total free disk space in MB or %%','%','') AS FLOAT)))
    AND ('%More than 'N' Free Space in MB%' = '%' OR CAST (REPLACE ('%More than 'N' Free Space in MB%','%','') AS FLOAT) < CAST (ISNULL (logical.FreeSpace,0)  AS Decimal (15,3))) -- ET#1832291 Sagar Bhat 02-Dec-2010 Changed table column from [Max Free Space] to FreeSapce

[--]

sooory, there was an issue: should work now :)

 

DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
SELECT DISTINCT i.Guid, 
i.Name [Computer Name], 
i.Domain [Domain], 
i.[User] [User ID],
vu.[Display Name],
,case when ua.[Full Name] is NULL then ISNULL(i.[User],'') else ISNULL(ua.[Full Name],'') end [Full Name as displayed in windows start menu] 
CAST (ISNULL (logical.[Total Disk Space]/ (1024),0) AS DECIMAL (15,0)) [Total Disk Space (GB)], 
ISNULL (storage.[Number of Hard Drives],0) [Number of Hard Drives], 
CAST (ISNULL (logical.FreeSpace /1024 ,0)  AS Decimal (15,2)) [Free Disk Space (GB)],
CAST (ISNULL (primarydrive.[Free Space on Primary Drive] / (1024),0) AS DECIMAL (15,0)) [Free space on primary logical volume (GB)]
FROM dbo.vComputer i
JOIN dbo.Inv_Inventory_Results ir
    ON ir._ResourceGuid = i.Guid
JOIN dbo.ScopeMembership sm
    ON sm.[ResourceGuid] = i.Guid
    AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope)) 
LEFT JOIN (SELECT COUNT (1) [Number of Hard Drives], s._ResourceGuid FROM dbo.vHWStorage s
WHERE s.[Media Type] = 29 GROUP BY s._ResourceGuid) storage
    ON storage._ResourceGuid = i.Guid
LEFT JOIN 
(
    --select summary info for logical disks based on hard drives
       -- ET#1832291 Sagar Bhat 02-Dec-2010 Removed single quotes from defination of 'FreeSapce'
       SELECT SUM ([Free Space (Bytes)] / (1024.0 * 1024.0)) FreeSpace, MAX ([Free Space (Bytes)] / (1024.0 * 1024.0)) [Max Free Space], SUM ([Size (Bytes)]/ (1024 * 1024)) [Total Disk Space] ,
    _ResourceGuid [Guid] FROM vHWLogicalDisk WHERE [Logical Disk Type] = 3 GROUP BY _ResourceGuid
) logical ON i.Guid = logical.Guid
LEFT JOIN dbo.vOSOperatingSystem os
    ON os._ResourceGuid = i.Guid
LEFT JOIN (SELECT DISTINCT ld.[Free Space (Bytes)] / (1024 * 1024) [Free Space on Primary Drive], ld._ResourceGuid  FROM vHWLogicalDisk ld
        LEFT JOIN dbo.vOSOperatingSystemWindows osw
            ON ld.[Device ID] = SUBSTRING (osw.[Windows Directory], 1, 2)
            AND ld._ResourceGuid = osw._ResourceGuid
        LEFT JOIN dbo.vOSOperatingSystem os
            ON os._ResourceGuid = ld._ResourceGuid
        WHERE CASE WHEN osw._ResourceGuid IS NULL AND ld.[Device ID] = os.[Boot Device] THEN 1
        WHEN ld.[Device ID] = SUBSTRING (osw.[Windows Directory], 1, 2) THEN 1 
        ELSE 0 END = 1) primarydrive 
    ON primarydrive._ResourceGuid = i.Guid
    LEFT JOIN vUser vu on vu.Name = i.[User] and i.[User] != ''
    left join Inv_UG_User_Account ua on ua._ResourceGuid = vc.[Guid] and i.Domain+'\'+i.[User] = ua.[User Name]
WHERE LOWER (i.Name) LIKE LOWER ('%Computer Name%')
    AND (('%Less than 'N' Free disk space on primary drive%' = '%') OR (primarydrive.[Free Space on Primary Drive] / (1.0) IS NULL) OR ('%Less than 'N' Free disk space on primary drive%' <> '%' AND primarydrive.[Free Space on Primary Drive] < CAST (REPLACE ('%Less than 'N' Free disk space on primary drive%','%','')AS FLOAT)))
    AND (('%Total free disk space in MB or %%' = '%') OR ('%Total free disk space in MB or %%' <> '%' AND CAST (ISNULL (logical.FreeSpace,0)  AS Decimal (15,3))  >= CAST (REPLACE ('%Total free disk space in MB or %%','%','') AS FLOAT)))
    AND ('%More than 'N' Free Space in MB%' = '%' OR CAST (REPLACE ('%More than 'N' Free Space in MB%','%','') AS FLOAT) < CAST (ISNULL (logical.FreeSpace,0)  AS Decimal (15,3))) -- ET#1832291 Sagar Bhat 02-Dec-2010 Changed table column from [Max Free Space] to FreeSapce

[--]

ahhhh not so good start, tested in SQL works now...

 

 

DECLARE @v1_TrusteeScope nvarchar(max)

SET @v1_TrusteeScope = N'%TrusteeScope%'

SELECT DISTINCT i.Guid,

i.Name [Computer Name],

i.Domain [Domain],

i.[User] [User ID],

vu.[Display Name],

case when ua.[Full Name] is NULL then ISNULL(i.[User],'') else ISNULL(ua.[Full Name],'') end [Full Name as displayed in windows start menu],

CAST (ISNULL (logical.[Total Disk Space]/ (1024),0) AS DECIMAL (15,0)) [Total Disk Space (GB)],

ISNULL (storage.[Number of Hard Drives],0) [Number of Hard Drives],

CAST (ISNULL (logical.FreeSpace /1024 ,0)  AS Decimal (15,2)) [Free Disk Space (GB)],

CAST (ISNULL (primarydrive.[Free Space on Primary Drive] / (1024),0) AS DECIMAL (15,0)) [Free space on primary logical volume (GB)]

FROM dbo.vComputer i

JOIN dbo.Inv_Inventory_Results ir

    ON ir._ResourceGuid = i.Guid

JOIN dbo.ScopeMembership sm

    ON sm.[ResourceGuid] = i.Guid

    AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope))

LEFT JOIN (SELECT COUNT (1) [Number of Hard Drives], s._ResourceGuid FROM dbo.vHWStorage s

WHERE s.[Media Type] = 29 GROUP BY s._ResourceGuid) storage

    ON storage._ResourceGuid = i.Guid

LEFT JOIN

(

    --select summary info for logical disks based on hard drives

       -- ET#1832291 Sagar Bhat 02-Dec-2010 Removed single quotes from defination of 'FreeSapce'

       SELECT SUM ([Free Space (Bytes)] / (1024.0 * 1024.0)) FreeSpace, MAX ([Free Space (Bytes)] / (1024.0 * 1024.0)) [Max Free Space], SUM ([Size (Bytes)]/ (1024 * 1024)) [Total Disk Space] ,

    _ResourceGuid [Guid] FROM vHWLogicalDisk WHERE [Logical Disk Type] = 3 GROUP BY _ResourceGuid

) logical ON i.Guid = logical.Guid

LEFT JOIN dbo.vOSOperatingSystem os

    ON os._ResourceGuid = i.Guid

LEFT JOIN (SELECT DISTINCT ld.[Free Space (Bytes)] / (1024 * 1024) [Free Space on Primary Drive], ld._ResourceGuid  FROM vHWLogicalDisk ld

        LEFT JOIN dbo.vOSOperatingSystemWindows osw

            ON ld.[Device ID] = SUBSTRING (osw.[Windows Directory], 1, 2)

            AND ld._ResourceGuid = osw._ResourceGuid

        LEFT JOIN dbo.vOSOperatingSystem os

            ON os._ResourceGuid = ld._ResourceGuid

        WHERE CASE WHEN osw._ResourceGuid IS NULL AND ld.[Device ID] = os.[Boot Device] THEN 1

        WHEN ld.[Device ID] = SUBSTRING (osw.[Windows Directory], 1, 2) THEN 1

        ELSE 0 END = 1) primarydrive

    ON primarydrive._ResourceGuid = i.Guid

    LEFT JOIN vUser vu on vu.Name = i.[User] and i.[User] != ''

    left join Inv_UG_User_Account ua on ua._ResourceGuid = i.[Guid] and i.Domain+'\'+i.[User] = ua.[User Name]

WHERE LOWER (i.Name) LIKE LOWER ('%Computer Name%')

    AND (('%Less than 'N' Free disk space on primary drive%' = '%') OR (primarydrive.[Free Space on Primary Drive] / (1.0) IS NULL) OR ('%Less than 'N' Free disk space on primary drive%' <> '%' AND primarydrive.[Free Space on Primary Drive] < CAST (REPLACE ('%Less than 'N' Free disk space on primary drive%','%','')AS FLOAT)))

    AND (('%Total free disk space in MB or %%' = '%') OR ('%Total free disk space in MB or %%' <> '%' AND CAST (ISNULL (logical.FreeSpace,0)  AS Decimal (15,3))  >= CAST (REPLACE ('%Total free disk space in MB or %%','%','') AS FLOAT)))

    AND ('%More than 'N' Free Space in MB%' = '%' OR CAST (REPLACE ('%More than 'N' Free Space in MB%','%','') AS FLOAT) < CAST (ISNULL (logical.FreeSpace,0)  AS Decimal (15,3))) -- ET#1832291 Sagar Bhat 02-Dec-2010 Changed table column from [Max Free Space] to FreeSapce

[amandaw33]

Thanks Joe and Sterling.  I really appreciate your time.

Neither work for me, unfortunately.  The error is in screenshot below (data source not in runnable state).  I think last time I got this error working on a report was because I use CMS and not ITMS (so I don't have asset management which that sql was referencing).  I could be wrong on that though and could be unrelated.

Error on CMS server is 

<<Failed to refresh the LinkRegion: UserConfigurationPanel

Failure saving changes to the Report.
   [Altiris.Reporting.Common.Exceptions.ReportSaveException @ Altiris.Reporting.UI]
   at Altiris.Reporting.UI.ReportEditControl.Save()
   at Altiris.Reporting.UI.Controls.LinkCallbackForm.OnRefresh(LinkRegionRefreshArgs args)
   at Altiris.Reporting.Common.ControlLinking.Controls.LinkRegion.RaiseCallbackEvent(String eventArgument)

An unexpected SQL error occurred when retrieving the schema from the RawSqlDataSource.
   [Altiris.DataSource.Exceptions.DataSourceSchemaException @ Altiris.Reporting.DataSource]
   at Altiris.Reporting.DataSource.RawSqlDataSource.RunSchema()
   at Altiris.Reporting.DataSource.RawSqlDataSourceEditControl.UpdateTarget()
   at Altiris.Reporting.Common.ReportContext.SaveStateRecursive(Control parentControl)
   at Altiris.Reporting.Common.ReportContext.SaveStateRecursive(Control parentControl)
   at Altiris.Reporting.Common.ReportContext.SaveStateRecursive(Control parentControl)
   at Altiris.Reporting.Common.ReportContext.SaveStateRecursive(Control parentControl)
   at Altiris.Reporting.Common.ReportContext.SaveStateRecursive(Control parentControl)
   at Altiris.Reporting.Common.ReportContext.SaveStateRecursive(Control parentControl)
   at Altiris.Reporting.Common.ReportContext.SaveStateRecursive(Control parentControl)
   at Altiris.Reporting.Common.ReportContext.SaveStateRecursive(Control parentControl)
   at Altiris.Reporting.Common.ReportContext.SaveStateRecursive(Control parentControl)
   at Altiris.Reporting.Common.ReportContext.SaveStateRecursive(Control parentControl)
   at Altiris.Reporting.Common.ReportContext.SaveStateRecursive(Control parentControl)
   at Altiris.Reporting.Common.ReportContext.SaveState()
   at Altiris.Reporting.UI.ReportEditControl.Save()

Incorrect syntax near ' Free disk space on primary drive%'.
   [System.Data.SqlClient.SqlException @ .Net SqlClient Data Provider]
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillSchemaInternal(DataSet dataset, DataTable datatable, SchemaType schemaType, IDbCommand command, String srcTable, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType schemaType, IDbCommand command, String srcTable, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType schemaType)
   at Altiris.NS.DataAccessLayer.DatabaseAbilities.ExecuteSchemaSet(IDatabaseContext ctx, SchemaType t, String sql, Object[] parampairs)
   at Altiris.Reporting.DataSource.RawSqlDataSource.RunSchema()

SQL Exception details: code=102, line=42

Exception logged from: 
   at Altiris.Reporting.Common.ControlLinking.Controls.LinkRegion.RaiseCallbackEvent(String)
   at System.Web.UI.Page.RenderCallback()
   at System.Web.UI.Page.ProcessRequestMain(Boolean, Boolean)
   at System.Web.UI.Page.ProcessRequest(Boolean, Boolean)
   at System.Web.UI.Page.ProcessRequest()
   at System.Web.UI.Page.ProcessRequest(System.Web.HttpContext)
   at Altiris.NS.UI.Controls.PageCachePage.ProcessRequest(System.Web.HttpContext)
   at Altiris.NS.UI.AltirisPage.ProcessRequest(System.Web.HttpContext)
   at System.Web.HttpApplication+CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(System.Web.HttpApplication+IExecutionStep, Boolean&)
   at System.Web.HttpApplication+PipelineStepManager.ResumeSteps(Exception)
   at System.Web.HttpApplication.BeginProcessRequestNotification(System.Web.HttpContext, AsyncCallback)
   at System.Web.HttpRuntime.ProcessRequestNotificationPrivate(System.Web.Hosting.IIS7WorkerRequest, System.Web.HttpContext)
   at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr, IntPtr, IntPtr, Int32)
   at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr, IntPtr, IntPtr, Int32)>>

 

Thanks for any thoughts.  I envy your sql skills!

[JoeVan]

Hmmm, the only reason you would get that error is if there is an error in the SQL code or one of the tables we added does not exist.  The only one I added is the view vUser which I would think would be there in a CMS only install.  To test this theory you could create a simple report with SQL Select * form vUser to see if that view exists. 

My guess is the view is not there because you have not imported user objects from AD.  If you are not importing users I would suggest doing so because it populates selected AD user attributes into Altiris and makes them available for reporting.  For example, you could add user email address or department name to your report if you want.  This also pre-populates information for Software Portal so users don't need to manually register.  

If you are not familiar with AD import, info is available in the Admin Guide: https://support.symantec.com/en_US/article.DOC9469.html 

For now, you can take that join out and report just the userid instead of the user full name, SQL below

DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
SELECT DISTINCT i.Guid, 
i.Name [Computer Name], 
i.Domain [Domain], 
i.[User] [User ID],
CAST (ISNULL (logical.[Total Disk Space]/ (1024),0) AS DECIMAL (15,0)) [Total Disk Space (GB)], 
ISNULL (storage.[Number of Hard Drives],0) [Number of Hard Drives], 
CAST (ISNULL (logical.FreeSpace /1024 ,0)  AS Decimal (15,2)) [Free Disk Space (GB)],
CAST (ISNULL (primarydrive.[Free Space on Primary Drive] / (1024),0) AS DECIMAL (15,0)) [Free space on primary logical volume (GB)]
FROM dbo.vComputer i
JOIN dbo.Inv_Inventory_Results ir
    ON ir._ResourceGuid = i.Guid
JOIN dbo.ScopeMembership sm
    ON sm.[ResourceGuid] = i.Guid
    AND sm.[ScopeCollectionGuid] IN (SELECT * FROM dbo.fnGetTrusteeScopeCollections (@v1_TrusteeScope)) 
LEFT JOIN (SELECT COUNT (1) [Number of Hard Drives], s._ResourceGuid FROM dbo.vHWStorage s
WHERE s.[Media Type] = 29 GROUP BY s._ResourceGuid) storage
    ON storage._ResourceGuid = i.Guid
LEFT JOIN 
(
    --select summary info for logical disks based on hard drives
       -- ET#1832291 Sagar Bhat 02-Dec-2010 Removed single quotes from defination of 'FreeSapce'
       SELECT SUM ([Free Space (Bytes)] / (1024.0 * 1024.0)) FreeSpace, MAX ([Free Space (Bytes)] / (1024.0 * 1024.0)) [Max Free Space], SUM ([Size (Bytes)]/ (1024 * 1024)) [Total Disk Space] ,
    _ResourceGuid [Guid] FROM vHWLogicalDisk WHERE [Logical Disk Type] = 3 GROUP BY _ResourceGuid
) logical ON i.Guid = logical.Guid
LEFT JOIN dbo.vOSOperatingSystem os
    ON os._ResourceGuid = i.Guid
LEFT JOIN (SELECT DISTINCT ld.[Free Space (Bytes)] / (1024 * 1024) [Free Space on Primary Drive], ld._ResourceGuid  FROM vHWLogicalDisk ld
        LEFT JOIN dbo.vOSOperatingSystemWindows osw
            ON ld.[Device ID] = SUBSTRING (osw.[Windows Directory], 1, 2)
            AND ld._ResourceGuid = osw._ResourceGuid
        LEFT JOIN dbo.vOSOperatingSystem os
            ON os._ResourceGuid = ld._ResourceGuid
        WHERE CASE WHEN osw._ResourceGuid IS NULL AND ld.[Device ID] = os.[Boot Device] THEN 1
        WHEN ld.[Device ID] = SUBSTRING (osw.[Windows Directory], 1, 2) THEN 1 
        ELSE 0 END = 1) primarydrive 
    ON primarydrive._ResourceGuid = i.Guid
WHERE LOWER (i.Name) LIKE LOWER ('%Computer Name%')
    AND (('%Less than 'N' Free disk space on primary drive%' = '%') OR (primarydrive.[Free Space on Primary Drive] / (1.0) IS NULL) OR ('%Less than 'N' Free disk space on primary drive%' <> '%' AND primarydrive.[Free Space on Primary Drive] < CAST (REPLACE ('%Less than 'N' Free disk space on primary drive%','%','')AS FLOAT)))
    AND (('%Total free disk space in MB or %%' = '%') OR ('%Total free disk space in MB or %%' <> '%' AND CAST (ISNULL (logical.FreeSpace,0)  AS Decimal (15,3))  >= CAST (REPLACE ('%Total free disk space in MB or %%','%','') AS FLOAT)))
    AND ('%More than 'N' Free Space in MB%' = '%' OR CAST (REPLACE ('%More than 'N' Free Space in MB%','%','') AS FLOAT) < CAST (ISNULL (logical.FreeSpace,0)  AS Decimal (15,3))) -- ET#1832291 Sagar Bhat 02-Dec-2010 Changed table column from [Max Free Space] to FreeSapce 

 

[Alex Hedley]

Do you have access to SSMS or 'SSE Tools' with the SQL Viewer, on the 'Advanced' tab?

Before I create any Report I always check that the SQL works against the db, as if it doesn't work there you know it won't work in the Report.

The Altiris Log Viewer can help with the errors from the Report.

[amandaw33]

@Alex, yea log viewer is where I got the error above.

 

I was still getting the error on Joe's newest SQL posted an hour ago, but then I remembered last time I had this problem, it was when I created a new mysql report.  If I clone an existing report and then replace the sql, works fine.  Who knows why....  UserID is great.  We don't import from AD but will look into that.

 

Thanks so much everyone who helped me, as always, this will save me a good amount of cross referencing!