Jump to content


  • 0
mike

Reports

Question

Hi,

 

This site is great! Keep up the great work.

 

 

I am trying to generate a report that will scan the comptuers in the CM and report back if they have a certain software (i.e. Adobe, Symantec, etc). Then, eventually the version and so on. (I am trying to start off small then eventuall grow on the reports). So I created the report below, but it returns an error when ever the % signs are in place. The error that follows when trying to run is :

 

An error occurred when the report was run. The details are as follows:

Conversion failed when converting the varchar value 'Adobe PDF Broker Process for Internet Explorer' to data type int.

Error Number: -2147217913

Source: Microsoft OLE DB Provider for SQL Server

Native Error: 245

 

I tried some trouble shooting like removing the % signs and then changing the like name but either the reports fail with another error or they report back nothing(report runs but no data).

 

SELECT Netbios_Name0, User_Name0,Operating_System_Name_and0

FROM v_R_System

RIGHT JOIN v_SoftwareProduct ON v_R_System.ResourceID =v_SoftwareProduct.ProductName

WHERE ProductName like '%Adobe%'

(So in this report, I think I am saying that I want a list of all the systems in the CM with Adobe. Is this correct.)

 

 

I would appericate if someone could let me know what is wrong in my script or point me in the right direction.

 

Thanks,

Mike

Share this post


Link to post
Share on other sites

7 answers to this question

Recommended Posts

  • 0

If you wan´t a report that shows results when a computer has a spesific program installed i would use the v_R_System table (to get basic information about the client) and then join the v_GS_Add_Remove_programs view (holds information about everything in add remove programs).

 

Your where clause is absolutly correct you coulduse it that way. But you can´t join 2 views if you don´t have thesame data in booth of the tables/views.

 

I would use the views i mentioned above and join them on the resourceID. I havent got access to any enviroment from here. but ican post a complete script for you little later tonight.

Share this post


Link to post
Share on other sites

  • 0

This report will list all computers that has a program with the name: %Adobe% listed in add/remove programs (note if a computer has 4 programs that has "Adobe in it´s name you will then get 4 rows in the report"):

SELECT

a.Netbios_Name0,

a.User_Name0,

a.Operating_System_Name_and0

 

FROM

v_R_System a,

v_GS_ADD_REMOVE_PROGRAMS b

WHERE a.ResourceID = b.ResourceID

AND DisplayName0 LIKE '%adobe%'

 

Example:

post-2500-1239298386_thumb.jpg

 

This report will list all your adobe product and count how many you have got of each product (note that it will count unique rows (so if you have 2 diffrent versions of flash player then you will get 1 count on each)):

 

SELECT

DisplayName0, Count(*) AS 'Count',

Publisher0, Version0,

ProdID0

FROM v_GS_ADD_REMOVE_PROGRAMS

WHERE Publisher0 Like 'adobe%'

OR Publisher0 Like '%adobe'

GROUP BY DisplayName0, Publisher0, Version0, ProdID0

 

Example:

post-2500-1239298574_thumb.jpg

 

This report can allso be linked to another report, so you can for exmaple see what 9 computers has Adobe CMaps installed. Tell me and i will show you :)

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.

Guest
Answer this question...

×   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.

Loading...


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.