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

Account list report

I cannot find a built-in report or even the user parameters to use in the web-based report manager to build a user report that lists the accounts in Solarwinds and the management levels or items. the information I am looking for is available under the settings>Admin>Accounts view, this table is exactly what i need however it does not appear to be exportable.

Orion-accounts.PNG

Since this table exists for both user and groups tabs(therefore the queries are already there, somewhere)  is there a built-in report for this information that i simply have not found? Either having this report or at the very least the ability to export this data on the page would be very helpful and greatly appreciated.

Thanks,

Jeff

12 Replies
Level 15

This should take care of your request. You'll want to use this in a Custom Query Resource, and those can be added in web reports as well if you want to export the results.

Query (in SWQL):

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]
,CASE
  WHEN AllowAdmin = 'Y' THEN 'Yes'
  ELSE 'No'
END AS [ADMIN RIGHTS]
,CASE
  WHEN AllowAdmin = 'Y' THEN '/Orion/Admin/Accounts/images/icons/ok_enabled.png'
  ELSE '/Orion/Admin/Accounts/images/icons/disable.png'
END AS [_IconFor_ADMIN RIGHTS]
,CASE
  WHEN AllowNodeManagement = 'Y' THEN 'Yes'
  ELSE 'No'
END AS [NODE MGMT]
,CASE
  WHEN AllowNodeManagement = 'Y' THEN '/Orion/Admin/Accounts/images/icons/ok_enabled.png'
  ELSE '/Orion/Admin/Accounts/images/icons/disable.png'
END AS [_IconFor_NODE MGMT]
,CASE
  WHEN AllowReportManagement = 'Y' THEN 'Yes'
  ELSE 'No'
END AS [REPORT MGMT]
,CASE
  WHEN AllowReportManagement = 'Y' THEN '/Orion/Admin/Accounts/images/icons/ok_enabled.png'
  ELSE '/Orion/Admin/Accounts/images/icons/disable.png'
END AS [_IconFor_REPORT MGMT]
,CASE
  WHEN AllowAlertManagement = 'Y' THEN 'Yes'
  ELSE 'No'
END AS [ALERT MGMT]
,CASE
  WHEN AllowAlertManagement = 'Y' THEN '/Orion/Admin/Accounts/images/icons/ok_enabled.png'
  ELSE '/Orion/Admin/Accounts/images/icons/disable.png'
END AS [_IconFor_ALERT MGMT]
,CASE
  WHEN AllowUnmanage = 'Y' THEN 'Yes'
  ELSE 'No'
END AS [ALLOW UNMANAGE]
,CASE
  WHEN AllowUnmanage = 'Y' THEN '/Orion/Admin/Accounts/images/icons/ok_enabled.png'
  ELSE '/Orion/Admin/Accounts/images/icons/disable.png'
END AS [_IconFor_ALLOW UNMANAGE]
,CASE
  WHEN AllowDisableAction = 'Y' THEN 'Yes'
  ELSE 'No'
END AS [ALLOW DISABLE ACTIONS]
,CASE
  WHEN AllowDisableAction = 'Y' THEN '/Orion/Admin/Accounts/images/icons/ok_enabled.png'
  ELSE '/Orion/Admin/Accounts/images/icons/disable.png'
END AS [_IconFor_ALLOW DISABLE ACTIONS]
,CASE
  WHEN AllowDisableAlert = 'Y' THEN 'Yes'
  ELSE 'No'
END AS [ALLOW DISABLE ALERTS]
,CASE
  WHEN AllowDisableAlert = 'Y' THEN '/Orion/Admin/Accounts/images/icons/ok_enabled.png'
  ELSE '/Orion/Admin/Accounts/images/icons/disable.png'
END AS [_IconFor_ALLOW DISABLE ALERTS]
,CASE
  WHEN AllowDisableAllActions = 'Y' THEN 'Yes'
  ELSE 'No'
END AS [ALLOW DISABLE ALL ACTIONS]
,CASE
  WHEN AllowDisableAllActions = 'Y' THEN '/Orion/Admin/Accounts/images/icons/ok_enabled.png'
  ELSE '/Orion/Admin/Accounts/images/icons/disable.png'
END AS [_IconFor_ALLOW DISABLE ALL ACTIONS]
,CASE
  WHEN AllowCustomize = 'Y' THEN 'Yes'
  ELSE 'No'
END AS [VIEW CUSTOMIZATIONS]
,CASE
  WHEN AllowCustomize = 'Y' THEN '/Orion/Admin/Accounts/images/icons/ok_enabled.png'
  ELSE '/Orion/Admin/Accounts/images/icons/disable.png'
END AS [_IconFor_VIEW CUSTOMIZATIONS]
FROM Orion.Accounts
WHERE AccountType IN ('1','2','3')
--AND AccountID LIKE '%${SEARCH_STRING}%'
ORDER BY AccountType, AccountID

Notice Line 104 is commented out. You can remove this if you want your resource to be searchable by Account Name (remove the '--' from the beginning of the line)

search.png

This is what the end result looks like:

results.png

Hope that helps!

Level 8

Hello Zack,

Even I was looking to extract the list of user accounts. Your inputs worked well. Thanks a lot..!

Wow!  I'm impressed.  Thank you for sharing your hard work!

Level 8

Hey Zack

This is perfect for what my manager has asked me to do,

I have 1 issue though,

why isn't my icon for Account type showing?

Thanks in advance

AJ

pastedImage_0.png

0 Kudos

Did you use a Custom Table or a Custom Query? - This was written to be used in a Custom Query.

- David Smith
Level 8

That's solved it,

thanks so much for your quick reply David.

0 Kudos
Level 13

zackm​,

I'm having some trouble getting this to display in a report. When I drop this into the content popup of a custom table and test it, the data shows up.

pastedImage_1.png

When I add it to the layout and preview the report, it comes up blank.

pastedImage_2.png

Any idea why this is occurring?

0 Kudos
Level 9

You need to add the selected fields in the Layout view.Edit Table view.JPG

We are greater than me.
Level 13

Thanks that was it!

0 Kudos
Level 15

no idea; it was designed for a custom query resource, but my assumption is it would have worked with a custom table as well. (especially if it previews correctly in the datasource)

0 Kudos
Level 9

zackm

I followed you in to San Mateo. Great Report!!!! Hope you are enjoying Cardinal.

We are greater than me.
Level 10

Thanks Zack that is precisely what I needed. My Boss and I are very appreciative.

Jeff