Wednesday, December 29, 2010

SMS Report for the packages advertised but not yet updated the DP

SELECT a.AdvertisementID,a.AdvertisementName,a.PresentTime as 'Advert Start Date',b.Name as 'Package Name',b.PackageID,count(b.PackageID) as 'Count of pending DP'
FROM v_Advertisement a
INNER JOIN v_Package b ON a.PackageID = b.PackageID
INNER JOIN v_DistributionPoint c ON b.PackageID = c.PackageID
INNER JOIN v_PackageStatusDistPointsSumm d ON c.PackageID = d.PackageID
WHERE d.state=1
GROUP BY a.AdvertisementID,a.AdvertisementName,a.PresentTime,b.PackageID,b.Name

Description: This report gives the count of each distribution points in which a specific advertised package is not updated.

Wednesday, December 15, 2010

Query to find non sms clients with recent AD discovery

SELECT
[Name],
[Resourceid],
[Obsolete],
[Client],
[LastSWscan],
[LastHWscan],
[Last discovery run]
FROM
(SELECT
row_number() over (partition by a.Name0 order by e.AgentTime desc) as rownum,
a.Name0 as [Name],
a.resourceid as [Resourceid],
a.Obsolete0 as [Obsolete],
a.Client0 as [Client],
b.LastScandate AS [LastSWscan],
c.LastHWscan as [LastHWscan],
e.AgentTime as [Last discovery run]
FROM v_r_system AS a
LEFT OUTER JOIN v_GS_LastSoftwareScan AS b
ON a.resourceid = b.resourceid
LEFT OUTER JOIN v_GS_WORKSTATION_STATUS AS c
ON a.resourceid = c.resourceid
LEFT OUTER JOIN v_AgentDiscoveries e
on e.resourceid=a.resourceid
WHERE a.Name0 IN (SELECT Name0
FROM v_r_system
GROUP BY Name0
HAVING COUNT(*) < 2) AND (c.LastHWscan is NULL and b.LastScandate is NULL and a.Client0 is NULL) AND e.AgentTime > getdate()-10
AND e.AgentName='SMS_AD_SYSTEM_DISCOVERY_AGENT')as dt
where rownum=1

Description: This query will get you all the resources which are having a recent Active directory system discovery date but do not have SMS client agents installed.

Thursday, December 9, 2010

SQL Query to find the users logged on to SMS console recently

select stat.MachineName as 'Accessing the console from',stat.Sitecode,att1.AttributeValue as 'UserName',att1.AttributeTime as 'Loggedin Time'
FROM vStatusMessages stat
left join v_StatMsgAttributes as att1 on stat.RecordID = att1.RecordID
WHERE
stat.MessageType = 768
and stat.Sitecode like '%'
and stat.Time <= getdate()
and stat.MessageID=30102
order by stat.Time desc

Description: This query gives you the list of users who have created a session with SMS console recently.