cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Report of users that can do things in Orion

I am in the process of changing the authentication domain that we use for Solarwinds and I found a few things on Thwack that kind of give me a list of users but the reports end up being massive and they cause a time out and blow up. 

I found this Account list report that generates local accounts - https://thwack.solarwinds.com/t5/Report-Lab-Discussions/Account-list-report/m-p/245608

I am thinking I should be able to modify the report to give me all of the users that have logins to Solarwinds.  I am looking at this section and I think that I need to enable something here for the report but not entirely sure.   I am hoping someone here can point me in the right direction. 

SELECT
AccountID AS [NAME]
,CASE
  WHEN AccountType = 0 THEN 'Orion Service Account'
  WHEN AccountType = 1 THEN 'Local Orion'
  WHEN AccountType = 2 THEN 'AD Individual'
  WHEN AccountType = 3 THEN 'AD Group'
  WHEN AccountType = 4 THEN 'Group Member'
END AS [ACCOUNT TYPE]
,CASE
  WHEN AccountType IN ('0','1') THEN '/Orion/Admin/Accounts/images/icons/orion_generic_icon_orange.gif'
  ELSE '/Orion/Admin/Accounts/images/icons/windows.gif'
END AS [_IconFor_ACCOUNT TYPE]
,CASE
  WHEN Enabled = 'Y' THEN 'Yes'
  ELSE 'No'
END AS [ENABLED]
,CASE
  WHEN Enabled = 'Y' THEN '/Orion/Admin/Accounts/images/icons/ok_enabled.png'
  ELSE '/Orion/Admin/Accounts/images/icons/disable.png'
END AS [_IconFor_ENABLED]
,Expires AS [EXPIRATION]
,LastLogin AS [LAST LOGIN]
,CASE
  WHEN LimitationID1 IS NULL OR LimitationID1 = '0' THEN 'None'
  WHEN LimitationID2 IS NULL OR LimitationID2 = '0' THEN 'None'
  WHEN LimitationID3 IS NULL OR LimitationID3 = '0' THEN 'None'
  ELSE 'Limitations Exist'
END AS [ACCOUNT LIMITAITON]

Thanks!  

4 Replies
Level 16

--Also, your query is missing FROM Orion.Accounts at the end. Should be like this

SELECT
AccountID AS [NAME]
,CASE
WHEN AccountType = 0 THEN 'Orion Service Account'
WHEN AccountType = 1 THEN 'Local Orion'
WHEN AccountType = 2 THEN 'AD Individual'
WHEN AccountType = 3 THEN 'AD Group'
WHEN AccountType = 4 THEN 'Group Member'
END AS [ACCOUNT TYPE]
,CASE
WHEN AccountType IN ('0','1') THEN '/Orion/Admin/Accounts/images/icons/orion_generic_icon_orange.gif'
ELSE '/Orion/Admin/Accounts/images/icons/windows.gif'
END AS [_IconFor_ACCOUNT TYPE]
,CASE
WHEN Enabled = 'Y' THEN 'Yes'
ELSE 'No'
END AS [ENABLED]
,CASE
WHEN Enabled = 'Y' THEN '/Orion/Admin/Accounts/images/icons/ok_enabled.png'
ELSE '/Orion/Admin/Accounts/images/icons/disable.png'
END AS [_IconFor_ENABLED]
,Expires AS [EXPIRATION]
,LastLogin AS [LAST LOGIN]
,CASE
WHEN LimitationID1 IS NULL OR LimitationID1 = '0' THEN 'None'
WHEN LimitationID2 IS NULL OR LimitationID2 = '0' THEN 'None'
WHEN LimitationID3 IS NULL OR LimitationID3 = '0' THEN 'None'
ELSE 'Limitations Exist'
END AS [ACCOUNT LIMITAITON]
FROM Orion.Accounts

So the SWQL query continues with more 'stuff' after what I posted.  When I add the FROM Orion.Accounts it breaks the report.  If you look at the link above you will see the entire query. 

0 Kudos

I set this up as a report using a custom SWQL query and it does work.  I am trying to figure out how to export as an Excel Doc. I tried to add &DataFormat=Excel at the end of the report but it still comes up as a PDF. 

I set up an SQWL query straight out of the DB:

SELECT AccountID, Enabled, AllowNodeManagement, AllowMapManagement, AllowAdmin, CanClearEvents, AllowReportManagement, AllowAlertManagement, AllowCustomize, AllowUnmanage, AllowDisableAction, AllowDisableAlert, AllowDisableAllActions, AlertSound, MenuName, HomePageViewID, DefaultNetObjectID, DisableSessionTimeout, ReportFolder, AlertCategory, Expires, LastLogin, LimitationID1, LimitationID2, LimitationID3, AccountSID, AccountType, GroupInfo, GroupPriority, AllowViewCopCheck, DisplayName, Description, InstanceType, Uri, InstanceSiteId FROM Orion.Accounts

I added this query into a report and then set up a daily schedule and chose Email, and clicked the box to send an attached Excel document. It worked fine. You should be able to do Excel or PDF. It does throw an error in red, ignore the error and keep going it will still work.