Help needed in T Sql Query

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:

Help needed in T Sql Query

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 >> Help needed in T Sql Query

Page: [1]

Message

<< Older Topic Newer Topic >>

Help needed in T Sql Query - 9/16/2008 10:27:48 AM

kpandey

Posts: 134
Score: 2
Joined: 4/23/2007
Status: offline

Hi Everyone,
                       The senario is as follows . As the part of regular SMS Client Management task i have created the dashboard with those machines which have not reported their HW inventory in last 10 days . and fixing those issues with custom script . I identified few machines in our enviroment have does not have admin$ accessible so we cannot repushed and even analyze the log files . We performed following task to track down those machines and the reson of this access denied problem is the Software Proventia installed which kills the Admin$

We are leveraging the SMS client Monitoring tool database table SMS clientresult table which is the part of SMSClienthealth Database and we have created one DTS Package to import the Data from the Clienthealth database to the SMS database . I am quering aginast the those machines which resulted access denied . Same time i want to figure out if the proventia installed on it though these machines have not reported their invenotory for a while may be th eresult could not be very accurate but still we are at least trying to figure maximum number of machines which we can with the effort. I user the V_GS_Service view to figure out all machines with BlackICE service Installed with and running the following query

Select distinct sys.Netbios_Name0,
fcm.SiteCode,
sys.User_Domain0,
sys.User_Name0,
sys.Operating_System_Name_and0,
case fcm.IsObsolete
when '0' then 'No'
else 'Yes'
End as Obsolete,
DateDiff(day,WK.LastHWScan,getdate()) as 'Last HW Scan',
sys.AD_Site_Name0 as [AD Site name],
case sys.Client_version0
when '2.50.3174.1018' then 'SMS SP1 Client'
When '2.50.4160.2000' then 'SMS SP2 Client'
When '2.50.4253.3000' then 'SMS SP3 Client'
Else ' SMS 2.0 Client'
End as [Client Version],
CHR.LastPingResult,
CHR.LastPing,
DateDiff(Day,CHR.LastPing,getdate()) as [Days Last Ping],
Case SRV.DisplayName0
When 'BlackICE' Then 'Yes'
Else 'No'
End as [Proventia Installed ?]
FROM v_R_System sys
Left Outer JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
left join v_GS_Workstation_Status as WK on SYS.ResourceID = WK.ResourceID
left join v_ClientHealthResults CHR on SYS.ResourceID = CHR.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID=OPSYS.ResourceID
LEFT JOIN V_GS_SYSTEM_ENCLOSURE on SYS.ResourceId = V_GS_SYSTEM_ENCLOSURE.ResourceID
Join v_GS_SERVICE SRV on SYS.ResourceID=SRV.ResourceID
WHERE V_GS_SYSTEM_ENCLOSURE.Tag0 = 'System Enclosure 0'
AND Operating_System_Name_and0 NOT LIKE '%SERVER%'
and CHR.LastPingResult like '% Access Denied'
order by sys.Netbios_Name0

The problem is with this query i am getting the duplicate report which says yes and No for the single machine in the coulumn Proventia Installed ? I have around 403 machines which have access denied and could have multiple cause of it offcourse the proventia is the main reason. But while running with this query i am getting 703 results. i can see lots of duplicacy in the data as

Machine Name      Last HW scan SMS Client Version    Ping Result                         DaysSincelstPing Proventia Installed?
AD956455                 0                 SMS SP3 Client         Online - Access Denied     1                                        No
AD956455                 1                   SMS SP3 Client       Online - Access Denied     1                                        Yes

I am wondering what is wrong with the Query of the dataset reported back by V_GS_Service Data

Post #: 1

RE: Help needed in T Sql Query - 9/16/2008 10:30:52 AM

kpandey

Posts: 134
Score: 2
Joined: 4/23/2007
Status: offline

Sorry The last HW scan in the both cases are 0 so here it is :

Machine Name      Last HW scan SMS Client Version    Ping Result                         DaysSincelstPing Proventia Installed?
AD956455                 0                 SMS SP3 Client         Online - Access Denied     1                                        No
AD956455                 0                  SMS SP3 Client        Online - Access Denied     1                                        Yes

(in reply to kpandey)

Post #: 2

RE: Help needed in T Sql Query - 9/16/2008 1:34:41 PM

jnelson993

Posts: 959
Score: 132
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

Well, I see a couple of things right off the bat that I'd look at...

I'd make that join to v_GS_Service a left join and put a filter in the FROM clause so you only find service records with 'BlackICE' (less to make SQL join and will eliminate some of your YES AND NO problems)
I think the join to v_GS_System_Enclosure is unnecessary...you're not selecting from it, and having it in the WHERE clause is just making SQL do more work than it has to...unless I'm misunderstanding the reason you're including it, I'd just remove it.
The join to v_GS_Operating_system is unnecessary...you're not selecting from it, and you're not using it to search/filter anything, so it's just making SQL do more work than it needs to as well.
Your join to v_FullCollectionMembership just to display the obsolete field and the site code is probably not a great idea as it causes duplicates that have to be filtered out (multiple collections per machine, though you're not filtering out for a single collection) plus all that data can be found in the v_R_System view (obsolete field) and the v_RA_System_SMSAssignedSites (sitecode field) view. Actually, it might make more sense to just remove obsolete records altogether because you might have an obsolete record that shows Proventia as Uninstalled, but have an active record for the same machine that shows it as installed. Is there a reason to SHOW the obsolete records? They're going to be deleted by a maintenance task anyway.
The left join to you client health results won't act like a left join because you've got a filter predicate for CHR in the WHERE clause. That causes the query to act like an INNER JOIN -->READ THIS<-- for more info. I'd make it an INNER JOIN and move the filter predicate up to the FROM
BTW, you don't need a DTS job to get the data from the client health db into your SMS db -->READ THIS<-- if you want more info.

Other than that...I don't really see any problems :)

So, I'm thinking something more like this: (Untested as I don't have your client health table)
SELECT   DISTINCT
sys.Netbios_Name0,
sc.SiteCode,
sys.User_Domain0,
sys.User_Name0,
sys.Operating_System_Name_and0,
DateDiff(DAY,WK.LastHWScan,getdate()) AS 'Last HW Scan',
sys.AD_Site_Name0                      AS [AD Site name],
CASE sys.Client_version0
    WHEN '2.50.3174.1018' THEN 'SMS SP1 Client'
    WHEN '2.50.4160.2000' THEN 'SMS SP2 Client'
    WHEN '2.50.4253.3000' THEN 'SMS SP3 Client'
    ELSE ' SMS 2.0 Client'
END AS [Client Version],
CHR.LastPingResult,
CHR.LastPing,
DateDiff(DAY,CHR.LastPing,getdate())   AS [Days Last Ping],
CASE SRV.DisplayName0
     WHEN 'BlackICE' THEN 'Yes'
     ELSE 'No'
END AS [Proventia Installed ?]
FROM
v_R_System sys
INNER JOIN v_ClientHealthResults CHR
    ON SYS.ResourceID = CHR.ResourceID
   AND CHR.LastPingResult LIKE '% Access Denied'
LEFT JOIN v_RA_System_SMSAssignedSites sc
    ON sys.ResourceID = sc.ResourceID
LEFT JOIN v_GS_Workstation_Status AS WK
    ON SYS.ResourceID = WK.ResourceID
LEFT JOIN v_GS_SERVICE SRV
    ON SYS.ResourceID = SRV.ResourceID
   AND srv.DisplayName0 = 'BlackICE'
WHERE
sys.Operating_System_Name_and0 NOT LIKE '%SERVER%'
AND sys.obsolete0 = 0
AND sys.decommissioned0 = 0
AND sys.client0 = 1
ORDER BY sys.Netbios_Name0

_____________________________

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

(in reply to kpandey)

Post #: 3

RE: Help needed in T Sql Query - 9/16/2008 2:04:44 PM

MeenEnta

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

Since the issue is with the loss of Admin Shares, why don't you re-enable them as we did here.

we used a GPO to push this reg changes.

Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanManServer\Parameters]
"AutoShareWks"=dword:00000001
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanManServer\Parameters]
"AutoShareServer"=dword:00000001

(in reply to jnelson993)

Post #: 4

RE: Help needed in T Sql Query - 9/16/2008 3:20:21 PM

kpandey

Posts: 134
Score: 2
Joined: 4/23/2007
Status: offline

Hi John ,
I really appreaciate your help and knowledge .

"I'd make that join to v_GS_Service a left join and put a filter in the FROM clause so you only find service records with 'BlackICE' (less to make SQL join and will eliminate some of your YES AND NO problems)"

Yes with this My Yes No problem is being Resolved.

"I think the join to v_GS_System_Enclosure is unnecessary...you're not selecting from it, and having it in the WHERE clause is just making SQL do more work than it has to...unless I'm misunderstanding the reason you're including it, I'd just remove it."

My intention was to pull out and differentiate the Asset Type with Chessis Number hence included V_GS_System_Enclosure.

"The join to v_GS_Operating_system is unnecessary...you're not selecting from it, and you're not using it to search/filter anything, so it's just making SQL do more work than it needs to as well. "

Yes I Do agree it Initialily i wanted to filter the Workstation only later on i discovered this can be done with V_R_System table

"

The left join to you client health results won't act like a left join because you've got a filter predicate for CHR in the WHERE clause. That causes the query to act like an INNER JOIN -->READ THIS<-- for more info. I'd make it an INNER JOIN and move the filter predicate up to the FROM
BTW, you don't need a DTS job to get the data from the client health db into your SMS db -->READ THIS<-- if you want "

These are great Information indeed .

@ MeenEnta : As I said Proventia is the main Cause of this problem . It installs and runs two services rapapp and blackice till the time these services are running it will not allow to populate the admin$ share . More stopping this services is not easy. the rapapp Agent is password protected and security team does have this pass till the time rapapp services are running we cannot stop the blackice services.
We did tested those registry vlaues and made the changes with local admin rights However it didnt work for us at all as these services does cause these registry keys to deactivated.

Thanks to both of you......

(in reply to MeenEnta)

Post #: 5