Client Management Suite

Hi

There have obviously been some quite extensive database changes from NS6 to NS7.  Many of our existing queries against the database no-longer work.  I'm struggling to re-create a couple of my scripts, and I wonder if anyone has some pointers.

I pull out a list of the .EXE file details from the database, including filename, size, path and where available the .EXE header.

I can find the filename and path in the table Inv_Installed_File_Details, and what looks like the filesize in Inv_File_Details.  If these are the correct tables, how are they linked?

Also, the .EXE file header details now seem to be within the item table.  How can I link this back to the file details in Inv_Installed_File_Details?

Any pointers would be most appreciated.

Many thanks

Gary
-=-

Can you use the view Inv_AeX_SW_Audit_Software which joins Cmn_SW_Common on Inv_AeX_SW_Audit_Software_spt via _KeyHash?

RS

 Unfortunatley not, they are NS 6 tables; they are not present in my NS 7 installation.  They are some of the tables I use in my existing scripts and the reason I'm struggling now.

Gary
-=-

Gary,
I haven't gone too far into my NS7 installs yet, but are there any views you could use in the DB to get what you're looking for?  In the NS5.5 -> NS6 migration, a lot of views were created for compatibility with existing reports etc (these are the AeXInv_* views you can see in the DB, along with Wrksta).  Again I haven't studied the new DB schema, but anything likely in there?

Something more like this:

SELECT    id.Name,
        id.Path,
        wf.*
FROM dbo.Inv_Installed_File_Details id
LEFT JOIN vWindowsFile wf ON wf.Guid = id.FileResourceGuid
WHERE id._ResourceGuid = 'GuidHere'

Or inner join vComputer to Inv_Installed_File_Details and choose the "where" to include a computer name or list of names.

I couldn't find where any other file information is stored other than what is above.  Well, I guess there was Inv_File_Details, but nothing too much there other than filesize.

When I want to look for key terms in a sproc or view, I use something like this:
SELECT OBJECT_NAME(id)
    FROM syscomments
    WHERE [text] LIKE '%%'
    AND OBJECTPROPERTY(id, 'IsView') = 1
    GROUP BY OBJECT_NAME(id)
    ORDER BY OBJECT_NAME(id)

inserting the search term into '%SearchTerm%', using IsView = 1 or IsProcedure = 1 allows you to look for certain items.  So, I was just looking for things like FileVersion, InternalName.

This doesn't look at all table column names, so maybe that would be a better search.

RS

Try cloning one of the existing software file reports and see what's going on in it; that's how I learned most of the report tricks I know with the NS6 DB schema.

 I've managed to get a bit closer, now linking in the File Size, but I'm still after the .EXE file header, which I can see in the dbo.Item table, which I am accessing from the vResourceItem view, but I do not know how to link the header information to the actual file.

Any ideas how to get the EXE header file from vResourceItem or similar views and link it to the file in Inv_Installed_File_Details?

This is my script so far:

I think you still want to joing to vWindowsFile which is pulling from vRM_File_Item:
select
  id.Name as [ComputerName],
  ifd.Path as [Path],
  ifd.Name as [FileName],
  ifd.ModifiedTime as [FileDate],
  fd.FileSize,
  wf.*
from Inv_AeX_AC_Identification id
left join Inv_Installed_File_Details ifd on id._ResourceGuid = ifd._ResourceGuid
left join Inv_File_Details fd on fd._ResourceGuid = ifd.FileResourceGuid
left JOIN vWindowsFile wf ON wf.Guid = ifd.FileResourceGuid
where
  ifd.Name like '%.exe' and
  fd.FileSize is not null
order by
  id.Name,
  ifd.Path

I don't see any file header information in the item table.  I see info on filters, jobs, legacy collections... but no file header info.

Do you have an example of what you are considering header information from the item table?

RS