Special Report

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:

Special Report

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 >> Special Report

Page: [1]

Message

<< Older Topic Newer Topic >>

Special Report - 9/24/2008 1:32:52 PM

MeenEnta

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

I have this Web SQL report to list all Laptops, and I need to take it one step further.
I would like to know if some or any of the users found in this report also logs on to Desktops and which one(s).
Of course I want this to be done in the same report, basicaly the logic is:
"If you have a laptop let's see if you use a Desktop too".

Here's the code that gives me the Laptops and related users.

SELECT Distinct SYS.Netbios_Name0, SYS.User_Name0, ENC.SerialNumber0, SYS.Client0, SYS.Active0, SYS.Obsolete0, PROC2.Manufacturer0,PROC2.Model0,OPSYS.Caption0 as "Operating System", OPSYS.CSDVersion0,ENC.ChassisTypes0, OPSYS.LastBootUpTime0
FROM v_FullCollectionMembership fcm
JOIN v_R_System SYS on fcm.ResourceID=SYS.ResourceID
JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID = OPSYS.ResourceID
JOIN v_GS_COMPUTER_SYSTEM PROC2 on SYS.ResourceID = PROC2.ResourceID
inner join v_GS_SYSTEM_ENCLOSURE ENC on SYS.ResourceID = ENC.ResourceId
where
[ENC.ChassisTypes0 = "8" or
ENC.ChassisTypes0 = "9" or
ENC.ChassisTypes0 = "10" or
ENC.ChassisTypes0 = "14" or
ENC.ChassisTypes0 = "11"]
and fcm.CollectionID=@CollID
ORDER BY SYS.Netbios_Name0

Thx for your Help.

Post #: 1

RE: Special Report - 9/24/2008 2:49:39 PM

bwilms

Posts: 168
Score: 4
Joined: 3/8/2006
Status: offline

I think this should do it.

SELECT 'Laptops Users'
SELECT Distinct SYS.Netbios_Name0, SYS.User_Name0, ENC.SerialNumber0, SYS.Client0, SYS.Active0, 
SYS.Obsolete0, PROC2.Manufacturer0,PROC2.Model0,OPSYS.Caption0 as "Operating System", OPSYS.CSDVersion0,ENC.ChassisTypes0, OPSYS.LastBootUpTime0 
FROM v_FullCollectionMembership fcm JOIN v_R_System SYS on fcm.ResourceID=SYS.ResourceID 
JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID = OPSYS.ResourceID 
JOIN v_GS_COMPUTER_SYSTEM PROC2 on SYS.ResourceID = PROC2.ResourceID 
inner join v_GS_SYSTEM_ENCLOSURE ENC on SYS.ResourceID = ENC.ResourceId 
where 
(ENC.ChassisTypes0 = '8' or 
ENC.ChassisTypes0 = '9' or 
ENC.ChassisTypes0 = '10' or 
ENC.ChassisTypes0 = '14' or 
ENC.ChassisTypes0 = '11')
and fcm.CollectionID='@CollID'
ORDER BY SYS.User_Name0 

SELECT 'Laptop Users Who Also Use Desktops'
SELECT Distinct SYS.Netbios_Name0, SYS.User_Name0, ENC.SerialNumber0, SYS.Client0, SYS.Active0, 
SYS.Obsolete0, PROC2.Manufacturer0,PROC2.Model0,OPSYS.Caption0 as "Operating System", OPSYS.CSDVersion0,ENC.ChassisTypes0, OPSYS.LastBootUpTime0 
FROM v_FullCollectionMembership fcm 
JOIN v_R_System SYS on fcm.ResourceID=SYS.ResourceID 
JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID = OPSYS.ResourceID 
JOIN v_GS_COMPUTER_SYSTEM PROC2 on SYS.ResourceID = PROC2.ResourceID 
inner join v_GS_SYSTEM_ENCLOSURE ENC on SYS.ResourceID = ENC.ResourceId 
WHERE (ENC.ChassisTypes0 = '3'and 
fcm.CollectionID='@CollID' and SYS.User_Name0 in (SELECT SYS.User_Name0 
FROM v_FullCollectionMembership fcm 
JOIN v_R_System SYS on fcm.ResourceID=SYS.ResourceID 
JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID = OPSYS.ResourceID 
JOIN v_GS_COMPUTER_SYSTEM PROC2 on SYS.ResourceID = PROC2.ResourceID 
inner join v_GS_SYSTEM_ENCLOSURE ENC on SYS.ResourceID = ENC.ResourceId 
WHERE (ENC.ChassisTypes0 = '8' or 
ENC.ChassisTypes0 = '9' or 
ENC.ChassisTypes0 = '10' or 
ENC.ChassisTypes0 = '14' or 
ENC.ChassisTypes0 = '11')
and fcm.CollectionID='@CollID'))
ORDER BY SYS.Netbios_Name0

In the second query you'll need to add more chassis types where I've defined ENC.ChassisTypes0 = '3'. You should be able to copy and paste this into one, single web report.

< Message edited by bwilms -- 9/24/2008 2:51:02 PM >

(in reply to MeenEnta)

Post #: 2

RE: Special Report - 9/24/2008 2:57:33 PM 1 votes

jnelson993

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

Well, if you just want to identify people that also have logged into a desktop, then this will do it (it won't show you WHICH desktops, just show you those people who do.

SELECT   DISTINCT
SYS.Netbios_Name0,
SYS.User_Name0,
   CASE
    WHEN EXISTS (SELECT
                   1
                 FROM
                   dbo.v_GS_SYSTEM_ENCLOSURE
                 WHERE ChassisTypes0 NOT IN ('8','9','10','11','14')
                   AND ResourceID IN (SELECT ResourceID FROM v_R_System WHERE User_name0 = SYS.User_Name0)) THEN 1
    ELSE 0
END AS HasDesktop,
ENC.SerialNumber0,
SYS.Client0,
SYS.Active0,
SYS.Obsolete0,
PROC2.Manufacturer0,
PROC2.Model0,
OPSYS.Caption0        AS "Operating System",
OPSYS.CSDVersion0,
ENC.ChassisTypes0,
OPSYS.LastBootUpTime0
FROM
v_FullCollectionMembership fcm
JOIN v_R_System SYS
    ON fcm.ResourceID = SYS.ResourceID
JOIN v_GS_OPERATING_SYSTEM OPSYS
    ON SYS.ResourceID = OPSYS.ResourceID
JOIN v_GS_COMPUTER_SYSTEM PROC2
    ON SYS.ResourceID = PROC2.ResourceID
INNER JOIN v_GS_SYSTEM_ENCLOSURE ENC
    ON SYS.ResourceID = ENC.ResourceId
WHERE
ENC.ChassisTypes0 IN ('8','9','10','11','14')
AND fcm.CollectionID = @CollID
ORDER BY SYS.Netbios_Name0

Now, if you also need to list the machines here too, then you gotta get a little funky. It's not pretty because you're looking up computers based on user_name0 which is not indexed and is pretty slow. This may timeout if you have a ton of records. Took 2:30 in our environment (~200K clients). I'm sure it could be optimized more, but I don't have that kind of time right now:

SELECT   DISTINCT
SYS.Netbios_Name0,
SYS.User_Name0,
   REPLACE(
           (SELECT Netbios_Name0 + ', '
              FROM dbo.v_R_System
             WHERE user_name0 = sys.user_name0
               AND resourceID NOT IN (SELECT ResourceID FROM dbo.v_GS_System_Enclosure WHERE ChassisTypes0 IN ('8','9','10','11','14'))
            ORDER BY Netbios_Name0
                FOR XML PATH(''))+'..',
           ', ..',
           '') AS Desktops,
ENC.SerialNumber0,
SYS.Client0,
SYS.Active0,
SYS.Obsolete0,
PROC2.Manufacturer0,
PROC2.Model0,
OPSYS.Caption0        AS "Operating System",
OPSYS.CSDVersion0,
ENC.ChassisTypes0,
OPSYS.LastBootUpTime0
FROM
v_FullCollectionMembership fcm
JOIN v_R_System SYS
    ON fcm.ResourceID = SYS.ResourceID
JOIN v_GS_OPERATING_SYSTEM OPSYS
    ON SYS.ResourceID = OPSYS.ResourceID
JOIN v_GS_COMPUTER_SYSTEM PROC2
    ON SYS.ResourceID = PROC2.ResourceID
INNER JOIN v_GS_SYSTEM_ENCLOSURE ENC
    ON SYS.ResourceID = ENC.ResourceId
WHERE    ENC.ChassisTypes0 IN ('8','9','10','11','14')
AND fcm.CollectionID = @CollID
ORDER BY SYS.Netbios_Name0

_____________________________

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

(in reply to MeenEnta)

Post #: 3

RE: Special Report - 9/24/2008 5:26:47 PM

MeenEnta

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

Thank You BWilms on the Code but is did not return any data.

John your Code (the second one ) is exactly what I wanted, see PIC. (just amazing...thank you).
I know you are very busy but when ever you have time to clean it more, can you show the Desktops "each" on a separete colunm with separate colunm for Model and Serial #. (not urgent).

we are much smaller environement 1200 PCs. took few seconds to report.

Thanks a lot

Thumbnail Image

Attachment (1)

(in reply to jnelson993)

Post #: 4

RE: Special Report - 9/24/2008 5:37:59 PM

jnelson993

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

You're very welcome.

No, it won't be possible to get computers each in their own columns. When you "PIVOT" rows to columns like you're asking, you have to explicitly spell out which values go to which columns and that's not going to happen with 1200 different possible values.

Now I suppose it's possible to just add the desktops as new rows and sort the results so you have the laptop first and any desktops in rows right after, or make a drill-down report that passes the username to a query that returns all of the desktops for the user on that line...but to add them dynamically to the existing report isn't very feasible.

_____________________________

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

(in reply to MeenEnta)

Post #: 5

RE: Special Report - 9/24/2008 5:45:50 PM

MeenEnta

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

Sorry john, what I meant Each Desktop on separate line of a Colunm.
Next to it the corresponding Model Coulm and Next the Corresponding Serial N3#.

(in reply to jnelson993)

Post #: 6

RE: Special Report - 9/24/2008 7:14:45 PM 1 votes

jnelson993

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

Oh, I get ya. That's easy then. I'd use a temp table so I can reference the first list of laptops without getting the data multiple times. Essentially, we need to find desktop machines other than those laptops but have the same users as the list of users associated with those laptops.

Maybe something like this:
--put laptop data into #tmp table
SELECT
SYS.Netbios_Name0,
SYS.User_Name0,
'Laptop' as ComputerType,
ENC.SerialNumber0,
SYS.Client0,
SYS.Active0,
SYS.Obsolete0,
PROC2.Manufacturer0,
PROC2.Model0,
OPSYS.Caption0        AS "Operating System",
OPSYS.CSDVersion0,
ENC.ChassisTypes0,
OPSYS.LastBootUpTime0
INTO #tmp
FROM
v_FullCollectionMembership fcm
JOIN v_R_System SYS
   ON fcm.ResourceID = SYS.ResourceID
JOIN v_GS_OPERATING_SYSTEM OPSYS
   ON SYS.ResourceID = OPSYS.ResourceID
JOIN v_GS_COMPUTER_SYSTEM PROC2
   ON SYS.ResourceID = PROC2.ResourceID
JOIN v_GS_SYSTEM_ENCLOSURE ENC
   ON SYS.ResourceID = ENC.ResourceId
WHERE    ENC.ChassisTypes0 IN ('8','9','10','11','14')
AND fcm.CollectionID = @CollID

--now pull the data from #tmp table...
SELECT
Netbios_Name0,
User_Name0,
ComputerType,
SerialNumber0,
Client0,
Active0,
Obsolete0,
Manufacturer0,
Model0,
[Operating System],
CSDVersion0,
ChassisTypes0,
LastBootUpTime0
FROM
#tmp

-- add the results above to results below
UNION
--this removes duplicates, UNION ALL would leave them

-- get list of desktops for those same users and add to the results
SELECT
SYS.Netbios_Name0,
SYS.User_Name0,
'Desktop' AS ComputerType,
ENC.SerialNumber0,
SYS.Client0,
SYS.Active0,
SYS.Obsolete0,
PROC2.Manufacturer0,
PROC2.Model0,
OPSYS.Caption0        AS [Operating System],
OPSYS.CSDVersion0,
ENC.ChassisTypes0,
OPSYS.LastBootUpTime0
FROM
v_FullCollectionMembership fcm
JOIN v_R_System SYS
   ON fcm.ResourceID = SYS.ResourceID
JOIN v_GS_OPERATING_SYSTEM OPSYS
   ON SYS.ResourceID = OPSYS.ResourceID
JOIN v_GS_COMPUTER_SYSTEM PROC2
   ON SYS.ResourceID = PROC2.ResourceID
JOIN v_GS_SYSTEM_ENCLOSURE ENC
   ON SYS.ResourceID = ENC.ResourceId
WHERE    ENC.ChassisTypes0 NOT IN ('8','9','10','11','14')
AND netbios_name0 NOT IN (SELECT netbios_name0 FROM #tmp)
AND user_name0 IN (SELECT user_name0 FROM #tmp)
AND fcm.CollectionID = @CollID
ORDER BY User_Name0, ComputerType DESC, Netbios_Name0

drop table #tmp

_____________________________

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

(in reply to MeenEnta)

Post #: 7

RE: Special Report - 9/25/2008 10:55:00 AM

MeenEnta

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

Worked great, and if possible can the Desktop colunms and related info colunms such as Model, serial numbers etc... be separated from Laptop colunms with related info colunms as in PIC.

I do understand this request can create duplicate lines either in the User names of latops and/or related asset info, and that will be okay.

Thank you John.
your SQL masterhood is impressive.

Thumbnail Image

Attachment (1)

(in reply to jnelson993)

Post #: 8

RE: Special Report - 9/26/2008 5:20:27 PM

MeenEnta

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

Also perhaps a separate challenge would be to show how many times (or frequency) the user logged on to that Desktop in the past 60 days.

I know I'm asking alot...but from john's blog he is into these types of requests.

I know I make light of things, but I'm Very appreciative John.

(in reply to MeenEnta)

Post #: 9