sixdollarshirt

I'm trying to get a report that includes how many instances of a certain application is installed and which version it is. I have everything worked out except how many instances are in our environment. Can someone point me to the correct table?

I don't need anyone to write the SQL query for me. If I can find the table I will be able to figure the rest of it out. I haven't had any luck understanding let alone editing any of the canned reports.

Thanks so much for your time. I'm at my wits end!



Sorry: I'm using NS 6.0

gwilmington

Inv_AeX_OS_Add_Remove_Programs
Inv_AeX_SW_Audit_Software

Those should get you started.

JeffreyJRiggs

you can try something like this...

Select a.Name, a.version,Count(a.name)
From [inv_aex_os_add_remove_programs] a
--Change below line to find app
Where [name] LIKE 'microsoft .net %'
Group by a.Name, a.version

__________________
SQL Fundamentals 101:
SELECT * FROM dbo.User WHERE Clue > '0'
Query batch completed, 0 rows

sixdollarshirt

This worked out VERY well. Thanks so much. Can you explain how the "a" works at the end of the table name or possibly point to a doc that explains it? I've been trying to build reports using simple SQL queries, but haven't grasped this concept yet.

Thanks again for your help. This worked out great!

JeffreyJRiggs

im a sql newb too...

it is like this... you need to designate a letter for different tables if you want to join them..

example...
enter computer name at the end... what I have if you put in a report... you can setup a variable called %computer%.. when you run the report it asks for the computer name....

what if you had multiple tables joined and more than one had a field called name... you need a way to selectively select which you want... thus the tagging of t0, t1, etc...
hope that helps...

Select t0.[Name], t4.[Computer Model], [Serial Number] as 'Tag Number', t1.[Type] as 'CPU Type', t1.[Number] as 'CPU Count', t1.[Speed] as 'CPU Speed', t2.[Total Physical Memory (MB)] as 'Memory (MB)',
t3.[Name], t3.[Size in MBytes], t0.[OS Name], t0.[OS Type], t0.[OS Revision]
from [Inv_AeX_AC_Identification] t0 left outer join
[INV_AeX_HW_CPU] t1 on t0._ResourceGuid = t1._ResourceGuid left outer join
[INV_AeX_HW_Memory] t2 on t1._ResourceGuid = t2._ResourceGuid left outer join
[INV_AeX_HW_Logical_Disk] t3 on t2._ResourceGuid = t3._ResourceGuid left outer join
[INV_AeX_HW_Serial_Number] t4 on t3._ResourceGuid = t4._ResourceGuid
Where t3.[name] in ('c:','D:','E:','F:','G:','H:','I:') and t3.[Description] ='Local Disk'
and t0.[name] like '%Computer%'

__________________
SQL Fundamentals 101:
SELECT * FROM dbo.User WHERE Clue > '0'
Query batch completed, 0 rows

sixdollarshirt

absolutely helpful. Thanks so much. I always want to "learn to fish" and this is what you've done here. Thanks again!

JeffreyJRiggs

it is "Dirty" but it works...

__________________
SQL Fundamentals 101:
SELECT * FROM dbo.User WHERE Clue > '0'
Query batch completed, 0 rows