Report to show computers based on IP range/Location
I want to get a report of all computers based on a IP range or location. I already have my device collections based on boundary groups. I'd like to use that if possible. Anyone have any ideas please? Thanks!
September 5th, 2013 4:53pm

I want to get a report of all computers based on a IP range or location. I already have my device collections based on boundary groups. I'd like to use that if possible. Anyone have any ideas please? Thanks!

I've found this http://wmug.co.uk/wmug/b/eskonr/archive/2010/12/15/sccm_2d00_report_2d00_to_2d00_list_2d00_computers_2d00_with_2d00_ip_2d00_address_2d00_with_2d00_their_2d00_subnets_2d00_from_2d00_given_2d00_collection.aspx

But I really do not know how to create these types of reports. How do I add prompt parameters using Report Builder 3.0?

Free Windows Admin Tool Kit Click here and download it now
September 5th, 2013 5:50pm

Create a new report, add the query code to the dataset (right-click the dataset > Query). We'll call this DataSet1. Using the code from the link that you specified...

After clicking OK to save the query, RB will automatically create the PARAMETER (e.g. COLLID).


Expand the Parameters node and select Properties for COLLID.

You can create a new dataset (DataSet2) to grab the collection ID and names. You can display the collection name in a dropbox and use the collectionID for the query (user friendly)...

SELECT
   CollectionID, Name
FROM
   v_Collection
ORDER BY 
   Name ASC

Properties of COLLID:

- Available Variables > Get the values from a quey

- Dataset: DataSet2

- Value field: CollectionID

- Label field: Name

September 5th, 2013 6:17pm

Create a new report, add the query code to the dataset (right-click the dataset > Query). We'll call this DataSet1. Using the code from the link that you specified...

After clicking OK to save the query, RB will automatically create the PARAMETER (e.g. COLLID).


Expand the Parameters node and select Properties for COLLID.

You can create a new dataset (DataSet2) to grab the collection ID and names. You can display the collection name in a dropbox and use the collectionID for the query (user friendly)...

SELECT
   CollectionID, Name
FROM
   v_Collection
ORDER BY 
   Name ASC

Properties of COLLID:

- Available Variables > Get the values from a quey

- Dataset: DataSet2

- Value field: CollectionID

- Label field: Name

I got this to work, but I tried to run the report, and it gave this error.

An error has occured during reporting processing. (rsProcessingAborted)
Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)
The SELECT permission was denied on the object 'v_GS_NETWORK_ADAPTER_CONFIGUR', database 'CM_JH1', schema 'dbo'.

  • Edited by JHBPJF Thursday, September 05, 2013 6:41 PM
Free Windows Admin Tool Kit Click here and download it now
September 5th, 2013 6:29pm

try to copy another report that runs properly, and then modify its DataSet... I don't have that error, but it sounds like it's related to the configuration of your DataSource...


My datasource is set to "Use a shared connection or report model", then Browse to the object {GUID} at the root of my report server... E.g. http://my-ssrs-server/reportserver/ConfigMgr_MySiteCode


September 5th, 2013 6:46pm

try to copy another report that runs properly, and then modify its DataSet... I don't have that error, but it sounds like it's related to the configuration of your DataSource...


My datasource is set to "Use a shared connection or report model", then Browse to the object {GUID} at the root of my report server... E.g. http://my-ssrs-server/reportserver/ConfigMgr_MySiteCode


That is what I am using.

I don't see a copy feature.

Free Windows Admin Tool Kit Click here and download it now
September 5th, 2013 6:54pm

In RB you can "Save As" and give it a different name.

Start over with a built-in report that works, then Save As.

Using the newly copied instance of the report...

- test run it

- modify the DataSet with the query code

- test run it

- add DataSet2 for COLLID...


  • Edited by fern.santos Thursday, September 05, 2013 7:00 PM
September 5th, 2013 6:59pm

I must not have permissions or something. I'm using Windows Authentication to connect to the Datasource. When I use the service account we have setup for SQL, it doesn't allow me to make queries.
Free Windows Admin Tool Kit Click here and download it now
September 5th, 2013 7:10pm

are you referring to the prompt "Enter Data Source Credentials"? I use an account for execution (subscriptions) and I must select the option "Use the current Windows user"... Assuming your credentials have rights to SSRS...

Then I can edit the query.

September 5th, 2013 7:13pm

are you referring to the prompt "Enter Data Source Credentials"? I use an account for execution (subscriptions) and I must select the option "Use the current Windows user"... Assuming your credentials have rights to SSRS...

Then I can edit the query.

I think it has something to do with this, but I can't find this at all. 

http://social.technet.microsoft.com/Forums/systemcenter/en-US/71458890-0a71-45d2-8a54-83fccd2e5083/sccm-report-error-the-select-permission-was-denied-on-the-object-vcollecttosubcollect-error

http://tech.scorpits.com/2013/04/sccm-reporting-select-permission-was.html
  • Edited by JHBPJF Thursday, September 05, 2013 7:27 PM
Free Windows Admin Tool Kit Click here and download it now
September 5th, 2013 7:26pm

Any update on this?
I have the same issue in SCCM 2012:

The SELECT permission was denied on the object 'v_GS_NETWORK_ADAPTER_CONFIGUR', database 'CM_PS1', schema 'dbo'.

The webreport_approle account is indeed available in SCCM 2007 but not in SCCM 2012. I am running SCCM 2012 R2 with SQL 2012 11.0.3000

smsschm_users has select rights on 'v_GS_NETWORK_ADAPTER_CONFIGUR'

January 22nd, 2014 9:37am

Any update on this?

smsschm_users has select rights on 'v_GS_NETWORK_ADAPTER_CONFIGUR'

The smschm_user right should be all you need. Exactly what are you trying to do?

How is you datasourse setup?

Free Windows Admin Tool Kit Click here and download it now
January 22nd, 2014 11:19am

Here is a report of which generates the error in SCCM 2012.

In SCCM 2007 it was working fine but know in SCCM 2012 I have denied permissions on the object 'v_GS_NETWORK_ADAPTER_CONFIGUR', database 'CM_PS1', schema 'dbo'

smsschm_users has select rights and the query works fine when I run it in the Query Designer.

SELECT
 A.Name0,
 MAX (B.SerialNumber0) AS 'Serialnumber',
 A.Manufacturer0,
 A.Model0, C.Name0 AS 'Processor',
 D.TotalPhysicalMemory0 AS 'Memory (KBytes)',
 MAX ( E.Size0 ) AS 'Size (MBytes)',
 MAX (F.MACAddress0) AS 'MAC Adress',
 MAX (F.IPAddress0) AS 'IP Adress',
 G.AD_Site_Name0 AS 'AD Site',
 MAX (A.UserName0) AS 'Last user logged in',
 H.Caption0 AS 'Operating System',
 H.CSDVersion0 AS 'Service Pack',
 G.Creation_Date0 AS 'Creationdate in SMS',
 I.LastHWScan
 
FROM
 v_GS_COMPUTER_SYSTEM A,
 v_GS_PC_BIOS B,
 v_GS_PROCESSOR C,
 v_GS_X86_PC_MEMORY D,
 v_GS_DISK E,
 v_GS_NETWORK_ADAPTER_CONFIGUR F,
 v_R_System G,
 v_GS_OPERATING_SYSTEM H,
 v_GS_WORKSTATION_STATUS I
 
WHERE
 A.ResourceID = B.ResourceID AND
 A.ResourceID = C.ResourceID AND
 A.ResourceID = D.ResourceID AND
 A.ResourceID = E.ResourceID AND
 A.ResourceID = F.ResourceID AND
 A.ResourceID = G.ResourceID AND
 A.ResourceID = H.ResourceID AND
 A.ResourceID = I.ResourceID
 
GROUP BY A.Name0, A.Manufacturer0, A.Model0, C.Name0, D.TotalPhysicalMemory0, G.AD_Site_Name0, A.UserName0, H.Caption0, H.CSDVersion0, G.Creation_Date0, I.LastHWScan


  • Edited by Jthys Wednesday, January 22, 2014 1:25 PM additional info
January 22nd, 2014 1:23pm

How is your SSRS data source setup?

What level of rights does your user that is trying to run the report have? In CM12 and SQL?

Free Windows Admin Tool Kit Click here and download it now
January 22nd, 2014 2:06pm

Hi I was able to solve this issue by granting the 'Reporting Services Point Account' select rights on the SCCM database.

It was working in Query designer but not when you run it from the console which is using the SCCM SQL reporting account.

Thanks Garth you send me in the right direction!

January 22nd, 2014 2:58pm

FYI - I suggested this to our DBA who preferred to enable some SQL tracing instead, and in the end resolved it by running the following:

GRANT SELECT ON dbo.v_GS_NETWORK_ADAPTER_CONFIGUR TO smsschm_users
I realise this will only fix the permissions on the v_GS_NETWORK_ADAPTER_CONFIGUR  however this is the only view we are encountering this issue with, so less is better in the DBA's mind :)

Steve


Free Windows Admin Tool Kit Click here and download it now
March 9th, 2015 9:11pm

FYI - I suggested this to our DBA who preferred to enable some SQL tracing instead, and in the end resolved it by running the following:

GRANT SELECT ON dbo.v_GS_NETWORK_ADAPTER_CONFIGUR TO smsschm_users
I realise this will only fix the permissions on the v_GS_NETWORK_ADAPTER_CONFIGUR  however this is the only view we are encountering this issue with, so less is better in the DBA's mind :)

That sql view (all others ) should already those permissions, if it doesn't then there is a problem.
March 9th, 2015 9:29pm

That's a worry then, as all 3 environments we run had this issue. At least we are consistent!

Interesting then that others in this thread have this issue too for dbo.v_GS_NETWORK_ADAPTER_CONFIGUR. And yet the proposed fix is to give the 'Reporting Services Point Account' select rights on the SCCM database, which I would say sounds like a problem having to do that (considering this appears to be the only table with the issue so far)?.  No table we have come across in any of our custom reports has hit this except for dbo.v_GS_NETWORK_ADAPTER_CONFIGUR.  We have approximately 300 custom reports that all work fine except for any that reference this table (until we ran the command mentioned). Thanks for you comments anyway though. Cheers 

Free Windows Admin Tool Kit Click here and download it now
March 10th, 2015 12:40am

Well I just checked my CM07 lab and that view does have Both the Database Role and Application Role applied with the appropriate security. So Im not sure why your didnt get applied.

March 10th, 2015 8:24am

Sorry for some reason I thought we are talking about CM07. However I check my CM12 labs and all of them have the Correct Security applied.

I did notice one thing. The SQL view is called v_GS_NETWORK_ADAPTER_CONFIGURATION in all of my labs. Whereas in the query above the name is cut-off.

Free Windows Admin Tool Kit Click here and download it now
March 10th, 2015 8:28am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics