Special Report (Full Version)

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



Message

MeenEnta -> Special Report (9/24/2008 1:32:52 PM)

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.



bwilms -> RE: Special Report (9/24/2008 2:49:39 PM)

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.



jnelson993 -> RE: Special Report (9/24/2008 2:57:33 PM)

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




MeenEnta -> RE: Special Report (9/24/2008 5:26:47 PM)

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

[image]local://upfiles/40892/CB5D0CDD84D54BE4873DB3F649FF9B62.gif[/image]



jnelson993 -> RE: Special Report (9/24/2008 5:37:59 PM)

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.



MeenEnta -> RE: Special Report (9/24/2008 5:45:50 PM)

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



jnelson993 -> RE: Special Report (9/24/2008 7:14:45 PM)

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




MeenEnta -> RE: Special Report (9/25/2008 10:55:00 AM)

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.




[image]local://upfiles/40892/12068ED673C740F0873585ED2A35C7CC.gif[/image]



MeenEnta -> RE: Special Report (9/26/2008 5:20:27 PM)

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.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.203125