sql

[SHORETEL] Get workgroup, user extension, user login and workgroup description.

To get a list of workgroup, user extension, user login and workgroup description, connect to the Shoretel Shoreware database and run the following SQL:

SELECT workgroupmembers.WorkgroupDN,workgroupmembers.UserDN,users.GuiLoginName, dn.Description FROM workgroupmembers 
INNER JOIN DN ON workgroupmembers.WorkgroupDN=dn.DN
INNER JOIN users ON workgroupmembers.UserDN=users.UserDN

 

[Lansweeper] Report of all active printers

Select Top 1000 tblAssets.AssetName,
tblAssets.AssetID,
tblAssets.IPAddress,
web40repprinterstatus.Status,
web40repprinterstatus.[DeviceĀ  model],
tblAssets.Mac,
tblAssets.LastIPScan,
tblAssets.Lastseen,
tblAssets.Firstseen,
tblAssets.Lasttried
From tblAssets
Inner Join web40repprinterstatus On tblAssets.AssetID = web40repprinterstatus.AssetID
And tblAssets.Assettype = 16

[Lansweeper] Report of hard disk error

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblNtlog.Eventcode,
tblNtlogSource.Sourcename,
Max(tblNtlog.TimeGenerated) As LastOccurrence,
tblADusers.Displayname,
tblAssets.OScode,
tblNtlogMessage.Message
From tblAssets
Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID
Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =
tblNtlog.SourcenameID
Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Where tblNtlog.Eventcode In (57, 55, 51, 26, 16, 7) And
tblNtlogSource.Sourcename In (‘Disk’, ‘volsnap’, ‘Application Popup’, ‘Ntfs’)
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblNtlog.Eventcode,
tblNtlogSource.Sourcename,
tblADusers.Displayname,
tblAssets.OScode,
tblNtlogMessage.Message
Order By tblAssets.AssetName

[Lansweeper] Report of computers without monitors

Select Top 10000 m.AssetID,
m.AssetName,
m.Username,
m.LastActiveScan,
m.IPAddress,
m.Description
From tblAssets As m,
upgrade_web40ActiveComputers As a
Where a.Computername = m.AssetID And — Filter out non-active computers
Not Exists(Select r.AssetID From tblMonitor As r Where r.AssetID = m.AssetID) And — Filter out computers with monitors
Not Exists(Select e.AssetID From web50repallscanningerrors As e Where e.AssetID = m.AssetID) — Filter out scan error PC
Order By m.LastActiveScan Desc

— a for Active Computer
— m for main assets
— r for relationship
— e for errorscan