Reports

[mike]

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

[wmmayms]

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.

[mike]

Thanks that would great if you could do so.

 

I will try to create the script from your advice and see if I can make it work.

 

Appreciate your help,

Mike

[wmmayms]

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:

 

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:

 

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

[anyweb]

great work wmmayms ! keep the reports coming

[mike]

Thanks for the great help wmmayms!

I will continue to work with the reports and hopefully try to grow from them!

Appreciate your time,

Mike

[mike]

I just combined your two reports and it works GREAT!

 

thanks again,

Mike

[wmmayms]

Glad to be able to help