jgillean
Posts: 100
Score: 10
Joined: 1/17/2006
From: Memphis, TN
Status: offline
|
OK, I may be making some incorrect assumptions here, but it looks like you want the ranks associated with NON top users for a particular machine, in addition to being able to distinguish both the last logged on user and the top user for that machine. With those assumptions...try creating a report with this query:
select distinct sys.name0 as 'Machine Name',
sys.user_name0 as 'Last Logged On User',
con.topconsoleuser0 as 'Top User',
comp.manufacturer0 as 'Manufacturer',
comp.model0 as 'Model',
os.caption0 as 'Operating System',
os.csdversion0 as 'OS Version',
ws.lasthwscan as 'Last HW Scan'
from v_r_system sys
inner join v_gs_system_console_usage con
on sys.resourceid=con.resourceid
inner join v_gs_computer_system comp
on sys.resourceid=comp.resourceid
inner join v_gs_operating_system os
on sys.resourceid=os.resourceid
inner join v_gs_workstation_status ws
on sys.resourceid=ws.resourceid
order by sys.name0
Save the report as something like "Last logged on user and top user per machine". (Remember whichever name you choose here)
Now create another report with the following:
select
sys.Name0 as 'Machine Name',
user_logon.UserName0 as 'User',
user_logon.UserRank0 as 'Rank (Top User = 1)'
from v_R_System sys
inner join v_GS_User_Logon_Info0 user_logon
on sys.resourceid = user_logon.resourceid
where sys.Name0 = @variable
and user_logon.Username0 != 'domjoin'
and user_logon.Username0 != 'Administrator'
and user_logon.Username0 NOT LIKE 'IWAM_%'
order by user_logon.UserRank0
On the General tab in the Report Properties dialog, choose "Edit SQL Statement..." and then click on the "Prompts..." button.
Create a new prompt called "variable" with the following SQL statement:
begin
if (@__filterwildcard = '')
SELECT DISTINCT SYS.Name0 from v_R_System SYS Order by SYS.Name0
else
SELECT DISTINCT SYS.Name0 from v_R_System SYS
WHERE SYS.Name0 like @__filterwildcard
Order by SYS.Name0
end
Make sure the "Allow an empty value" checkbox is unchecked.
Once the prompt is complete, click OK, and then OK again to close the SQL dialog window.
Enter a name for the report (e.g. "Ranked Users").
Choose the Links tab on the Report Properties dialog.
For the link type, choose "Link to another report" and select the first report that you just created (above).
Under prompts, make sure column 1 is chosen.
Finally, click OK.
When you're done, you should have a report, "Last logged on user and top user per machine", that lists all machines in your environment, and allows you to drill down into each machine to see the ranked users per machine. Hopefully, this helps.
_____________________________
Jeremy Gillean
|
Printable Version
I got a Headache - 
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread