Transcript MMS CM12 Data Mining

Midwest Management Summit
Session ID – CM12 Data Mining
Garth Jones and Sherry Kissinger
@GarthMJ
www.Enhansoft.com
#MMSMinnesota
#MMSSQL
Data Mining in CM12
•
•
•
•
•
SQL Views
Discovery Data
Hardware Inventory
Software Updates
Software Metering
Data Mining in CM12 -Continued
•
•
•
•
Status Messages
Collections
Software Inventory
Custom Inventory
SQL Views
• Use the Views
• Views are Microsoft supported way to create reports
Discovery Data
• Most Used Views
• v_R_System
• v_R_User
• Most Discovery Views are
• v_R_*
• v_RA_*
• Heartbeat
• AD Discovery
Data Discovery – Sample Query
Select
RV.Netbios_Name0,
case
When (Max(OU.System_OU_Name0) != Null or Max(OU.System_OU_Name0) != '')
Then Max(OU.System_OU_Name0)
Else max(cn.System_Container_Name0)
end as 'OU'
From
dbo.v_R_System_Valid RV
left outer join dbo.v_RA_System_SystemContainerName CN on RV.Resourceid = cn.resourceID
left outer join dbo.v_RA_System_SystemOUName OU on RV.Resourceid = OU.resourceID
Group by
RV.Netbios_Name0
Data Discovery
Demonstration
Hardware Inventory
• What is Hardware Inventory?
• Where does it come from?
• Why is it important?
• Sample Query
• Demo
Hardware Inventory – Sample Query
select
RV.Netbios_Name0 as 'Pc Name',
RV.User_Name0 as 'User Name',
ARP.DisplayName0 as 'Application Name',
ARP.Version0 as 'Version',
ARP.InstallDate0 as 'Install Date'
from
dbo.v_R_System_Valid RV
join dbo.v_Add_Remove_Programs ARP on RV.ResourceID = ARP.ResourceID
Where
ARP.DisplayName0 = 'Warranty Information Reporting'
Order by
RV.Netbios_Name0
Hardware Inventory
Demonstration
Software Updates (Patches)
• What is Software Updates?
• Where does it come from?
• Why is it important?
• Sample Query
• Demo
Software Updates – Sample Query
select s1.Netbios_name0 as [Computer], uss.lastscantime, uss.LastWUAVersion,
Vendor=vnd.CategoryInstanceName, UpdateClassification=cls.CategoryInstanceName,
ui.Title, ui.BulletinID, ui.ArticleID, ui.CI_UniqueID as UniqueUpdateID, ui.InfoURL as InformationURL
from v_UpdateComplianceStatus css
join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
join v_CICategoryInfo_All vnd on vnd.CI_ID=ui.CI_ID and vnd.CategoryTypeName='Company'
join v_CICategoryInfo_All cls on cls.CI_ID=ui.CI_ID and cls.CategoryTypeName='UpdateClassification'
left join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = css.ResourceID
join v_r_system s1 on s1.resourceid=css.resourceid
join v_UpdateScanStatus uss on uss.resourceid=css.resourceid
outer apply (
select Deadline=min(a.EnforcementDeadline)
from v_CIAssignment a
join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID and atc.CI_ID=css.CI_ID
) cdl
where s1.netbios_name0 in (‘ComputerName1‘,’ComputerName2’) and css.status = 2
order by ui.bulletinid DESC, ui.Title
Software Updates
Demonstration
Software Metering, OR “Recently Used Apps”
• What is Software Metering?
• Where does it come from?
• Why is it important?
• Why do my Citrix or Terminal Servers hate RecentlyUsedApps?
• Sample Query
• Demo
Recently Used Apps – Sample Query
--Set this to be the executable looked for
DECLARE @FileName varchar(30) = ‘acrobat.exe'
--set this to only display those older than this many days, if you want everyone regardless of lastUsedTime, set to 0
DECLARE @OlderThanDays int = 180
SELECT
s1.ResourceID, s1.netbios_name0, MAX(rua.LastUsedTime00) LastUsedTime, rua.ExplorerFileName00, rua.ProductName00,
rua.ProductVersion00, rua.FileDescription00, rua.FileVersion00, rua.FolderPath00, rua.msiDisplayName00
FROM
dbo.RecentlyUsedApps_DATA RUA
JOIN v_r_system s1 ON rua.MachineID = s1.ResourceID
WHERE rua.ExplorerFileName00 = @FileName
GROUP BY
s1.ResourceID, s1.Netbios_Name0, rua.ExplorerFileName00, rua.ProductName00, rua.ProductVersion00, rua.FileDescription00,
rua.FileVersion00, rua.FolderPath00, rua.msiDisplayName00
HAVING DateDiff(Day,MAX(rua.LastUsedTime00),GetDate()) > @OlderThanDays
ORDER BY
resourceID,6,8,10
Software Metering
Demonstration
Status Messages
• What are Status Messages?
• Where does it come from?
• Why is it important?
• Sample Query
• Demo
Status Messages – Sample Query
SELECT
msg.MachineName,
msg.Time,
msg.Component,
msg.SiteCode,
msg.MessageID
FROM
dbo.v_StatusMessage msg WITH(NOLOCK)
WHERE
msg.messageid=10800 --10800 happens to be "client installed" status message
and msg.time >= Dateadd(day, -12, getdate()) --just show machines in the last xx days
ORDER BY
msg.time desc
--Slightly OT: http://myitforum.com/cs2/blogs/jnelson/archive/2009/11/20/143120.aspx
Status Messages
Demonstration
Collections
• What are Collections?
• Collection Queries (WQL)
• Collection Statistics
• Why is it important?
• Sample Query
• Demo
Collection Statistics – Sample Query
SELECT
collectionid as [Collection ID],
Name AS [Collection Name],
DATEDIFF(s, EvaluationStartTime, LastRefreshTime) AS [Eval Time (s)]
FROM
v_collection
where
DATEDIFF(s, EvaluationStartTime, LastRefreshTime) > 0
and collectionid not like 'sms%'
ORDER
BY [Eval Time (s)] DESC
Collection – Sample Query
Based on years of finding long-running collection queries in a large environment, these were some of the common
things found in a WQL query which could be fixed and make the wql query faster.
1) Distinct has no purpose in a collection query
2) Using subselects and comparing a name instead of resourceid for your subselect
3) Note: there are other things that can make a long running query. These are just the ones easily detected so
you can find and adjust them.
select c.CollectionName, s.wql, s.sql
from Collection_Rules_SQL s
join collections c on c.collectionid=s.collectionid
where
s.wql like '% distinct %'
or s.wql like '%Computer_System.Name not in%'
or s.wql like '%Computer_System.Name in%'
Collections
Demonstration
Software Inventory
• What is Software Inventory?
• Where does it come from?
• Why is it important?
• Sample Query
• Demo
Software Inventory – Challenge
CHALLENGE
Custom Inventory
• What is Custom Inventory?
• Where does it come from?
• Why is it important?
• Sample Query
• Demo
Custom Inventory – Sample Query
select lgm.name0 [Name of the Local Group]
,lgm.Account0 [Account]
,lgm.Category0 [Category of Account]
,lgm.Domain0 [Domain for the Account]
,lgm.Type0 [Type of Account]
from v_gs_localGroupMembers0 lgm
join v_r_system sys1 on sys1.resourceid=lgm.resourceid
where lgm.name0 = 'Administrators'
Custom Inventory
Demonstration
Evaluations
Please provide session feedback by clicking the
Eval button in the scheduler app. One lucky
winner will get a free ticket to the next MMS!
#MMSMinnesota
#MMSSQL
Platinum Sponsors:
Gold Sponsors:
Visit all of our sponsors in the expo area and online!
MMS Minnesota 2014
SECRET SLIDE
• This was briefly demo’d…
• Import this sample Configuration Item for “using Cis like Sinv”, and the sample
.sql to use for reporting.
• All the .sql queries