SWQL/SQL to query account setting

muyokuuu

I am currently setting up report in SolarWinds SAM,

the report is for gathering account setting,

but I can only query some field of the account setting, 

can someone list out all field location in the database?

List for field in account setting

Account Enabled
Account Expires
Disable Session Timeout
Allow Administrator Rights
Allow Node Management Rights
Allow management of Network Atlas Maps
Allow management of Intelligent Maps
Allow upload of images to Intelligent Maps
Manage Views
Manage Dashboards

Reports
Allow Report Management Rights
Report Limitation Category

Alerts
Allow Alert Management Rights
Alert Limitation Category

Allow Account to Unmanage Objects & Mute Alerts
Allow Account to Disable Actions
Allow Account to Disable Alerts
Allow Account to Disable All Actions
Allow Account to Clear/Acknowledge messages
Allow Browser Integration
Number of items in the breadcrumb list

Account Limitations

Default Menu Bar and Views
HomeTab Menu Bar
ApplicationsTab Menu Bar
CloudTab Menu Bar
Show Anomaly-Based Alerts Menu
Show AlertStack Menu
Show Alerts Menu
Show Events Menu
Show Message Center Menu
Show Syslogs Menu
Show Traps Menu
Show All Reports Menu
Tabs ordering
Home Page View
Default Network Device
Default Summary View

Server & Application Monitor Settings
Application Summary View
Application Details View
Component Details View
SAM User Role
Real-Time Process Explorer
Service Control Manager
Allow Service Actions Rights
Real-Time Event Log Viewer
Allow nodes to be rebooted
Allow IIS Action Rights
AppInsight for SQL Application View
AppInsight for SQL Database View
AppInsight for SQL Database File View
AppInsight for SQL Statistic View
AppInsight for SQL Job Info Details
AppInsight for SQL Query Info Details
AppInsight for Exchange Application View
AppInsight for Exchange Database View
AppInsight for Exchange Statistic View
AppInsight for Exchange Mailbox View
AppInsight for Exchange Database File View
AppInsight for Exchange Database Copy Details
AppInsight for Exchange Replication Status Details
AppInsight for Exchange Mailbox Quota View
Wstm BlackBox Application Details
Wstm BlackBox Component Details
Wstm BlackBox Task Info Details
AppInsight for IIS Application View
AppInsight for IIS Application Pool View
AppInsight for IIS Site View
AppInsight for IIS Statistic View
AppInsight for Active Directory Application View

Cloud Monitoring Settings
Cloud Summary View

Quality of Experience Settings
QoE Application Details View
QoE Application Category Details View
QoE Productivity Rating Details View
QoE Risk Level Details View

General Settings
Node Details View
Volume Details View
Group Details View
Active Alert Details View

Log Manager for SolarWinds Platform Settings
Log Entry Details View

Virtualization Manager Settings
Integrated Virtualization Manager Summary View
Virtualization Manager Summary View
Virtualization Manager VMware Summary View
Virtualization Manager Hyper-V Summary View
Virtualization Manager Nutanix Summary View
Host Details View
Virtual Machine Details View
Cluster Details View
Datacenter Details View
Datastore Details View
Virtualization Manager Storage Summary View
Virtualization Manager Sprawl View
Virtual Machine Power Management
Snapshot Management
Resources Settings Management
Delete virtual machines and datastore files
Migration Management

Hardware Health Package Settings
Temperature Unit

Hardware Health Baseboard Management Controller Settings
Chassis Details View

Network Interface Settings
Interface Details View

Performance Analysis Settings
Allow Real-Time Polling

Power Control Unit Settings
Temperature unit

Recommendations Settings
Allow Recommendations

SSH Settings
SSH Access

Cloud Monitoring per Provider Settings
AWS Cloud Instance Details View
Azure Cloud VM Details View

bourlis

hi muyokuuu,

It kind of sounds like you have a bunch of individual user accounts that you're trying to manage, correct?  If so my I offer a few suggestions.  Move as many people into groups as you can, like AD groups, then associate views and rights to those groups.  It's so much easier keeping track of group rights than user rights!  It would, seriously, eliminate most of the items you have listed.

A simple example of this would be 'read-only group', 'server-user group' & then 'server-elevated group'.  Each of these groups would have their own rights, limitations, views or any-other means of segregation that fits your needs.  For example, I grant all users basic read-only rights, but then I created two elevated group accounts, each with unique rights and views.  In doing this I can say base 'User A' your limited to just seeing nodes.  Elevated 'User A' you can manage reports and mute/manage nodes but nothing more and then Admin 'User A' I trust you so you can add remove nodes from SW and you can SSH into devices.

 But with that being said here's several SWQL queries that'll get you pretty close to what you're asking for:

select

A.GroupInfo AS [Group]
, CASE
WHEN A.Enabled = 'Y' THEN 'Yes'
ELSE 'No'
END AS [Enabled]
, CASE
WHEN A.AllowAdmin = 'Y' THEN 'Yes'
ELSE 'No'
END AS [Admin Rights]
, CASE
WHEN A.AllowManageDashboards = 'Y' THEN 'Yes'
ELSE 'No'
END AS [Manage Dashboards]
, CASE
WHEN A.AllowNodeManagement = 'Y' THEN 'Yes'
ELSE 'No'
END AS [Manage Nodes]
,CASE
WHEN A.AllowReportManagement = 'Y' THEN 'Yes'
ELSE 'No'
END AS [Manage Reports]
,CASE
WHEN A.AllowMapManagement = 'Y' THEN 'Yes'
ELSE 'No'
END AS [Edit Maps]
,CASE
WHEN A.AllowAlertManagement = 'Y' THEN 'Yes'
ELSE 'No'
END AS [Manage Alerts]
,CASE
WHEN A.AllowUnmanage = 'Y' THEN 'Yes'
ELSE 'No'
END AS [Unmanage Nodes]
,CASE
WHEN A.AllowCustomize = 'Y' THEN 'Yes'
ELSE 'No'
END AS [Allow Customizations]
,CASE
WHEN A.AllowOrionMapsManagement = 'Y' THEN 'Yes'
ELSE 'No'
END AS [Allow Maps Management]
,CASE
WHEN A.AllowUploadImagesToOrionMaps = 'Y' THEN 'Yes'
ELSE 'No'
END AS [Allow Maps Image Upload]

,CASE
WHEN A.AllowDisableAction = 'Y' THEN 'Yes'
ELSE 'No'
END AS [Disable Actions]
,CASE
WHEN A.AllowDisableAlert = 'Y' THEN 'Yes'
ELSE 'No'
END AS [Disable Alerts]
,CASE
WHEN A.AllowDisableAllActions = 'Y' THEN 'Yes'
ELSE 'No'
END AS [Disable All Actions]
,CASE
WHEN A.CanClearEvents = 'Y' THEN 'Yes'
ELSE 'No'
END AS [Clear Events]

FROM Orion.Accounts A
WHERE A.AccountType='4' AND a.LimitationID1='0'

GROUP BY A.GroupInfo,
A.Enabled,
A.AllowAdmin,
A.AllowManageDashboards,
A.AllowNodeManagement,
A.AllowReportManagement,
A.AllowMapManagement,
A.AllowAlertManagement,
A.AllowOrionMapsManagement,
A.AllowUploadImagesToOrionMaps,
A.AllowUnmanage,
A.AllowCustomize,
A.AllowDisableAction,
A.AllowDisableAlert,
A.AllowDisableAllActions,
A.CanClearEvents

ORDER BY a.AllowAdmin DESC

WITH NOLOCK

SELECT
AccountID AS [User Account]
, LastLogin AS [Last Login]
, CASE
WHEN AccountType='1' THEN 'Orion Account'
WHEN AccountType='2' THEN 'Local AD account in Solarwinds'
WHEN AccountType='3' THEN 'AD group'
WHEN AccountType='4' THEN 'User account in a AD Group'
End as [Account Type]
, GroupInfo AS [Group]

FROM Orion.Accounts

WHERE AccountType='4'
OR AccountType='2'
OR AccountType='1'
--OR AccountType='3'

Order BY AccountType ASC
WITH NOLOCK

select

A.GroupInfo AS [Group Name]
,A.MenuName AS [Menu Name]
,OV.ViewTitle AS [Home Page View]
,OV.ViewGroupName AS [view]
,OV.ViewType AS [view]
,CASE
WHEN OV.NOCView = 'True' THEN 'Yes'
ELSE 'No'
END AS ['NOCView']
FROM Orion.Accounts A
JOIN Orion.Views OV ON A.HomePageViewID=OV.ViewID
WHERE A.AccountType = '4'

GROUP BY A.GroupInfo
,A.MenuName
,OV.ViewTitle
,OV.ViewGroupName
,OV.ViewType
,OV.NOCView

WITH NOLOCK