sql

[Shoretel] Get list of user extension, login, workgroup description with firstname & last name based on the Workgroup number.

Below is the SQL where to get a list of user extension, login, workgroup description with firstname & last name based on the Workgroup numbeer where 1234, 2456 are workgroup extension numbers.

SELECT workgroupmembers.WorkgroupDN,workgroupmembers.UserDN,users.GuiLoginName,tabaddresses.FirstName,tabaddresses.LastName,dn.Description FROM workgroupmembers

INNER JOIN `dn` ON workgroupmembers.WorkgroupDN=dn.DN

INNER JOIN `users` ON workgroupmembers.UserDN=users.UserDN

INNER JOIN `tabaddresses` ON users.AddressID=tabaddresses.AddressID

WHERE workgroupmembers .WorkgroupDN IN (1234, 2456)

ORDER BY workgroupmembers.WorkgroupDN ASC
Advertisements

[SHORETEL] Extract user’s corresponding workgroups

Below is the SQL for Shoretel to user’s corresponding workgroups where 1234 is the user’s extension.

SELECT workgroupmembers.WorkgroupDN,workgroupmembers.UserDN,users.GuiLoginName, tabaddresses.FirstName,tabaddresses.LastName,dn.Description FROM workgroupmembers

INNER JOIN `dn` ON workgroupmembers.WorkgroupDN=dn.DN

INNER JOIN `users` ON workgroupmembers.UserDN=users.UserDN

INNER JOIN `tabaddresses` ON users.AddressID=tabaddresses.AddressID

WHERE users.UserDN IN (1234)

[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