Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

report to show users that have never logged in

I need to show a report listing all user accounts that have never logged in. How do I format the Where statement to do that? I think those are the accounts that have a LastLogin value of "1/1/1900", right?

Find more posts tagged with

never logged in

user

Accepted answers

monitoringlife

What kind of accounts are you using? Orion or AD/SAML?

If your using AD/SAML you would not see records until the user actually logs in. You would have to pull the AD group in compair against who is missing from Orion DB.

If your using Orion accounts you can reference the last login date. The default date is '1900-01-01 05:00:00'.

SELECT AccountID, Enabled, LastLogin
FROM Orion.Accounts A
WHERE A.LastLogin != '1900-01-01 05:00:00'

All comments

patriot

Anyone please?

monitoringlife

What kind of accounts are you using? Orion or AD/SAML?

If your using AD/SAML you would not see records until the user actually logs in. You would have to pull the AD group in compair against who is missing from Orion DB.

If your using Orion accounts you can reference the last login date. The default date is '1900-01-01 05:00:00'.

SELECT AccountID, Enabled, LastLogin
FROM Orion.Accounts A
WHERE A.LastLogin != '1900-01-01 05:00:00'

monitoringlife

You might also like this dashboard (sorry I don't remember the post to give proper credit to the creator)

'Custom SQWL Query' resource

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]
,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

patriot

Thanks for the reply. Yes, these are AD accounts, and those that have never logged in have '1/1/1900' in the LastLogin field. However, your SWQL query comes back with an error when used in the Custom Query resource.

monitoringlife

What version of SolarWinds are you running?

Can screen capture the error your getting?

Here is what it looks like for me in edit mode:

FormerMember

For what its worth, I placed this code in my Orion (12.5) and it work without error....

patriot

Yes, we are using the same resource. My formatting looks the same. The error I get is "There was an error processing your request." I'm using 2019.4. Could it be the data format of the LastLogin field?

bourlis

I don't know if this will help, but hears a SWQL query - dashboard that I built to search for people. This report takes into account local accounts, local AD user accounts and AD user groups and shows then the last logged in. If a local user or local ADuser hasn't logged it'll give the standard 1/1/1900 date stamp like you see in the screen shot below. If an user in an AD Group hasn't logged in they simply won't show up on the report.

It's pretty binary.

SWQL Query

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

SWQL Search Query

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 AccountID Like '%${SEARCH_STRING}%'
OR GroupInfo Like '%${SEARCH_STRING}%'
and AccountType<>'3'
Order BY AccountType ASC