Report help

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

Report help

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

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

Page: [1]

Message

<< Older Topic Newer Topic >>

Report help - 10/3/2008 11:07:44 AM

JDS300

Posts: 67
Score: 0
Joined: 8/16/2006
Status: offline

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!!

Post #: 1

RE: Report help - 10/3/2008 2:13:25 PM 1 votes

Rickym61

Posts: 92
Score: 4
Joined: 4/4/2007
Status: offline

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.

< Message edited by Rickym61 -- 10/3/2008 2:15:15 PM >

(in reply to JDS300)

Post #: 2

RE: Report help - 10/3/2008 2:18:15 PM

jnelson993

Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

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

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to Rickym61)

Post #: 3

RE: Report help - 10/3/2008 3:05:52 PM

MeenEnta

Posts: 107
Score: 0
Joined: 9/9/2008
Status: offline

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.

(in reply to jnelson993)

Post #: 4

RE: Report help - 10/3/2008 3:10:18 PM

jnelson993

Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

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.

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to MeenEnta)

Post #: 5

RE: Report help - 10/3/2008 5:37:47 PM

MeenEnta

Posts: 107
Score: 0
Joined: 9/9/2008
Status: offline

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.

Thumbnail Image

Attachment (1)

(in reply to jnelson993)

Post #: 6

RE: Report help - 10/6/2008 10:39:48 AM

gjones

Posts: 824
Score: 50
Joined: 6/5/2001
From: Ottawa, Ontario, Canada
Status: offline

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

_____________________________

Garth@enhansoft.com

For a List of my Articles
http://www.myitforum.com/contrib/default.asp?cid=116
Blogs:
http://smsug.ca/blogs/garth_jones/default.aspx
http://myitforum.com/cs2/blogs/gjones/default.aspx

(in reply to JDS300)

Post #: 7

RE: Report help - 10/6/2008 10:42:38 AM

jnelson993

Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

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.

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to MeenEnta)

Post #: 8

RE: Report help - 10/6/2008 11:20:10 AM

jnelson993

Posts: 900
Score: 127
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

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

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to gjones)

Post #: 9

RE: Report help - 10/6/2008 12:35:54 PM

gjones

Posts: 824
Score: 50
Joined: 6/5/2001
From: Ottawa, Ontario, Canada
Status: offline

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.

_____________________________

Garth@enhansoft.com

For a List of my Articles
http://www.myitforum.com/contrib/default.asp?cid=116
Blogs:
http://smsug.ca/blogs/garth_jones/default.aspx
http://myitforum.com/cs2/blogs/gjones/default.aspx

(in reply to jnelson993)

Post #: 10

RE: Report help - 10/6/2008 3:54:21 PM

JDS300

Posts: 67
Score: 0
Joined: 8/16/2006
Status: offline

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

(in reply to gjones)

Post #: 11

RE: Report help - 10/7/2008 10:08:38 AM

MeenEnta

Posts: 107
Score: 0
Joined: 9/9/2008
Status: offline

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.

(in reply to JDS300)

Post #: 12