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
Store

SQL vs SWQL Query Returns different

If i use this in sql, i pull the information needed...

SELECT [AuditEventID] AS [Event ID]
      ,[TimeLoggedUtc] AS [DATE]
      ,[AccountID] AS [USER]
      ,[ActionTypeID]
      ,[AuditEventMessage] AS Message
  FROM [SolarWindsNPM1].[dbo].[AuditingEvents]
  WHERE ActionTypeID LIKE '25' AND TimeLoggedUTC Like '%2022%' 
  ORDER BY TimeLoggedUtc DESC

It will give me all the information I am looking for...

Howerver, If I use swql, with this query..it always pulls 1530 rows..If I add anything to do with ActionTypeID, i receive an error

I only get 1530 rows, and there are no ActionTypeID of 25... or users adding nodes...

SELECT TimeLoggedUtc
,AccountID
,AuditEventMessage
FROM Orion.AuditingEvents
WHERE TimeLoggedUTC LIKE '%2022%'
ORDER BY TimeLoggedUtc DESC

Find more posts tagged with

Accepted answers

KMSigma.SWI

OK - let's try this:

Log into the Orion Web Console.
Go to the Alerts & Activity / Message Center.
Under "Filter Devices"
- Set it to All things.
Under "Filter Messages"
- Set the timespan for whatever makes sense (probably last 30 days is good enough) and optionally change the number of returned records.
- Check the box for Show Audit Log.
- Select the type as "Node Added" (if no returned data, set it to "All Action Types)

This is your account's view of the audit log.

This should be exactly what you are getting out of SWQL Studio. Anything that's missing, it's because you don't have the necessary rights (within Orion) to see it.

All comments

KMSigma.SWI

In the SWQL, you don't have the ActionTypeID = 25 added to your WHERE clause.

I'd (personally) write the SWQL like this:

SELECT [AuditLog].TimeLoggedUtc AS [Timestamp]
     , [AuditLog].AccountID AS [User]
     , [AuditLog].AuditEventMessage
     , [AuditLog].AuditingActionType.ActionTypeDisplayName --depending on your needs, you can probably comment this out
FROM Orion.AuditingEvents AS [AuditLog]
WHERE [AuditLog].TimeLoggedUTC >= DATETRUNC('year', GETDATE()) -- Everything in the current year
     AND [AuditLog].AuditingActionType.ActionTypeDisplayName = 'Node added'
ORDER BY [AuditLog].TimeLoggedUtc DESC

John W Johnson

Sorry... I had mentioned if i added that to the query, i receive no data at all

The above is what i get when i use your swql...

John W Johnson

SELECT [AuditEventID]
      ,[TimeLoggedUtc]
      ,[AccountID]
      ,[ActionTypeID]
      ,[AuditEventMessage]
  FROM [SolarWindsNPM1].[dbo].[AuditingEvents]
  WHERE ActionTypeID LIKE '25' AND TimeLoggedUTC Like '%2022%' AND AuditEventMessage LIKE '%istat2012test%'

KMSigma.SWI

Do you get data back if you remove the WHERE clauses? You can just do a double-dash (--) in front of each.

Might want to also add a TOP 1000 after the SELECT if you have a large environment.

P.S. - What version of the Orion Platform are you running?

John W Johnson

This works .... in sql, but like i say...i can't get actiontypeID to give me a 25 in swql it gives me 1, 2, and 164 only...

What i am trying to do, is make a swql report to see the date, time, user and node that was added... I created a great report using the custom sql report with the above sql query. but i want to create a widget with custom swql to show the last 25 nodes added with the user, date, node added.

John W Johnson

If i comment from where to the end... i get data ... but is only user login, user logout, user account changed, WHICH would be the 1, 2, and 164

John W Johnson

2022.2

KMSigma.SWI

That is super weird behavior. Any chance the user your using with SWQL Studio can't see certain elements? Obviously any SQL user you are using has at least full read rights to the database and all tables.

John W Johnson

Ahhh... good question... let me check

John W Johnson

in swql, i am using server type 'Orion (v3) AD and doesn't require credentials

John W Johnson

hmmm if i try to change the connection type.. the only other type that tries to work is 'orion (v3) over https... but then i get an error 'Server Certificate has problem none.'

KMSigma.SWI

OK - let's try this:

Log into the Orion Web Console.
Go to the Alerts & Activity / Message Center.
Under "Filter Devices"
- Set it to All things.
Under "Filter Messages"
- Set the timespan for whatever makes sense (probably last 30 days is good enough) and optionally change the number of returned records.
- Check the box for Show Audit Log.
- Select the type as "Node Added" (if no returned data, set it to "All Action Types)

This is your account's view of the audit log.

This should be exactly what you are getting out of SWQL Studio. Anything that's missing, it's because you don't have the necessary rights (within Orion) to see it.

John W Johnson

Yes, the result when doing that is EXACTLY like using the SQL Query... Now, how do i get swql to work the same way?

John W Johnson

I closed all connections and restarted swql... i logged in with a different user and it is working... Thanks for your assistance!