Report help (Full Version)

All Forums >> [Management Products] >> Microsoft Systems Management Server >> SMS 2003



Message

JDS300 -> Report help (10/3/2008 11:07:44 AM)

Looking to create a report to find all PST files on our machines so we can get an estimate size of all of the PST files in the organization.  We already have a count from our servers, so I need to limit this to workstations (Windows XP) only.

Here is the SQL for the report that shows me PST files on anything with the SMS Client.  How do I change it to only show Windows XP machines? 

select SYS.User_Name0 as 'Last Logon User', SYS.Netbios_Name0, 
SF.FileName, SF.FileSize, SF.FileModifiedDate, SF.FilePath
From v_GS_SoftwareFile SF
join v_R_System SYS on SYS.ResourceID = SF.ResourceID 
Where SF.FileName LIKE '%.pst' 
ORDER BY SF.FileSize DESC


Thanks!! [:D]



Rickym61 -> RE: Report help (10/3/2008 2:13:25 PM)

Try this

select  SYS.User_Name0 as 'Last Logon User',
          SYS.Netbios_Name0,
          SF.FileName,
          SF.FileSize,
          SF.FileModifiedDate,
          SF.FilePath
From   v_GS_SoftwareFile SF
join      v_R_System SYS    on SYS.ResourceID = SF.ResourceID
join      v_GS_OPERATING_SYSTEM OS on OS.ResourceID = SYS.ResourceID
Where        SF.FileName    LIKE '%.pst'
and OS.Caption0 = 'Microsoft Windows XP Professional'
-- and OS.CSDVersion0 = 'Service Pack 2'
ORDER BY    SF.FileSize    DESC

You can break it down by Service Packs if required, just uncomment the above in Red.



jnelson993 -> RE: Report help (10/3/2008 2:18:15 PM)

You've already got V_R_System, instead of another join, I'd just add a little filter predicate to the query to use the Operating_System_Name_And0 LIKE '%Server%' (and I've thrown in a little something to remove obsolete/decommissioned/non-client records)

SELECT  
  SYS.User_Name0      AS 'Last Logon User',
  SYS.Netbios_Name0,
  SF.FileName,
  SF.FileSize,
  SF.FileModifiedDate,
  SF.FilePath
FROM    
  v_GS_SoftwareFile SF
  JOIN v_R_System SYS
    ON SYS.ResourceID = SF.ResourceID
    AND sys.obsolete0 = 0
   AND sys.decommissioned0 = 0
   AND sys.client0 = 1
   AND sys.Operating_System_Name_And0 LIKE '%Server%'
WHERE    SF.FileName LIKE '%.pst'
ORDER BY SF.FileSize DESC



MeenEnta -> RE: Report help (10/3/2008 3:05:52 PM)

Do we have to modify the sms_def.mof for this? if not, will this look for the pst file in the C:\ drive or any place it can find it?

Because i'm not getting any records.

Thx.



jnelson993 -> RE: Report help (10/3/2008 3:10:18 PM)

You have to turn on file inventory for that file extension.  Not through the MOF, through the normal file inventory settings dialog thing within the console.  The SQL here will find them anywhere they are.



MeenEnta -> RE: Report help (10/3/2008 5:37:47 PM)

Sensei, that worked fine, i just added the pst to the collection and tested it on one machine, found the PST on th C drive, and since our users' PSTs are on network drives (User-Home directory folder) I'm wondering if i have to point to the variable Path?

Have a Nice Weekend.

[image]local://upfiles/40892/30CE579DCC2943AD8DFCEC598F45E799.gif[/image]



gjones -> RE: Report help (10/6/2008 10:39:48 AM)

If you are an international company you might want to use this one because the SYS.SystemRole0 will always show “workstation” for Workstation PCs, instead of using v_R_System.Operating_System_Name_And0 LIKE '%Server%' since it is not always in the title of the OS.
 
Like this http://smsug.ca/blogs/garth_jones/archive/2008/10/06/list-psts.aspx



jnelson993 -> RE: Report help (10/6/2008 10:42:38 AM)

No, you likely won't be able to (nor should you) inventory files on network shares.  1) SMS/CM runs as SYSTEM and thus the machine account has to have network rights to that share (doubtful) and 2) I don't even know if SMS/CM would look there anyway even if you did have the UNC in a variable setup right and had the network rights to do such a thing.  If the files exist on a network share on a server somewhere, they should be inventoried by the SMS/CM client on that server (if you have one.



jnelson993 -> RE: Report help (10/6/2008 11:20:10 AM)

Garth, we're an international company...but I don't see anything in the Operating_System_Name_And0 that indicates a different name and version...what versions do you see there?  Here's what I see:

Microsoft Windows NT Advanced Server 5.0
Microsoft Windows NT Advanced Server 5.2
Microsoft Windows NT Server 5.0
Microsoft Windows NT Server 5.2
Microsoft Windows NT Server 6.0
Microsoft Windows NT Workstation 5.0
Microsoft Windows NT Workstation 5.1
Microsoft Windows NT Workstation 5.2
Microsoft Windows NT Workstation 6.0

Of course, when you join to V_GS_Operating_System and look at the Caption, that's a different story...



gjones -> RE: Report help (10/6/2008 12:35:54 PM)

Sometime I found that the OS is NULL and I thought that French version of the OS would show up as displayed similar to the caption text. I don’t have access to site with French servers and more to double check.



JDS300 -> RE: Report help (10/6/2008 3:54:21 PM)

Thanks guys!  Ended up going with Rickym61's solution and seems to be working.  Damn we have a lot of PSTs on the local drives...[:(]



MeenEnta -> RE: Report help (10/7/2008 10:08:38 AM)

John, instead of scanning the user mapped drives, I'm wondring if the "reg" file can be read to see the PST files that outlook is pointing too?

(Obviously it is user based).

Best regards.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.265625