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

auditing views

Jump to solution

Hi,

 

I need a sql query to list out the number of total views, last modified / created / deleted in entire solarwinds.

Please assist here.

 

Thanks in advance.

1 Solution

So, this is SQL script that you have asked for:

 

 

SELECT 
	COUNT(*) AS 'Total'
FROM AuditingEvents ae
JOIN AuditingActionTypes aat ON aat.ActionTypeID = ae.ActionTypeID
WHERE
	aat.ActionType IN 
	(
		'Orion.ViewAdded',
		'Orion.ViewChanged',
		'Orion.ViewDeleted'
	)

 

 

However, I personally don't find it very useful, as you do not know over what time this number is and what is actual split between added/created/modified

If I were you, I would:

  1. Add a split between created, modified and deleted
  2. Add a condition over how many days I want these numbers
  3. Add 3 more actions - resource added/modified/deleted, as these are elements of the view and sort of count towards view change as well

So, you would get something like this for the last 7 days:

 

 

SELECT 
	MIN(ae.TimeLoggedUtc) AS 'AuditStart',
	MAX(ae.TimeLoggedUtc) AS 'AuditEnd',
	DATEDIFF(DAY, MIN(ae.TimeLoggedUtc), MAX(ae.TimeLoggedUtc)) AS 'AuditDays',
	SUM(CASE WHEN aat.ActionType = 'Orion.ViewAdded' THEN 1 ELSE 0 END) AS 'ViewAdded',
	SUM(CASE WHEN aat.ActionType = 'Orion.ViewChanged' THEN 1 ELSE 0 END) AS 'ViewChanged',
	SUM(CASE WHEN aat.ActionType = 'Orion.ViewDeleted' THEN 1 ELSE 0 END) AS 'ViewDeleted',
	SUM(CASE WHEN aat.ActionType = 'Orion.ResourceAdded' THEN 1 ELSE 0 END) AS 'ResourceAdded',
	SUM(CASE WHEN aat.ActionType = 'Orion.ResourceChanged' THEN 1 ELSE 0 END) AS 'ResourceChanged',
	SUM(CASE WHEN aat.ActionType = 'Orion.ResourceDeleted' THEN 1 ELSE 0 END) AS 'ResourceDeleted'
FROM AuditingEvents ae
JOIN AuditingActionTypes aat ON aat.ActionTypeID = ae.ActionTypeID
WHERE
	aat.ActionType IN 
	(
		'Orion.ViewAdded',
		'Orion.ViewChanged',
		'Orion.ViewDeleted',
		'Orion.ResourceAdded',
		'Orion.ResourceChanged',
		'Orion.ResourceDeleted'
	)

	AND DATEDIFF(DAY, ae.TimeLoggedUtc, getUTCdate()) <= 7 --over the last X days

 

 

Output:
01.PNG

And lastly, having these numbers would immediately raise a question - who has done it, when and why. Below is a flat-view script that will give you nice output for every modification above, including user ID, so you can go and chase your team to stop changing them, as well as educate them that any change has a global effect ... ye, I know what you feel buddy - I have the same problem 😉

 

 

SELECT 
	ae.TimeLoggedUtc,
	ae.AccountID,
	aat.ActionTypeDisplayName,
	ae.AuditEventMessage
FROM AuditingEvents ae
JOIN AuditingActionTypes aat ON aat.ActionTypeID = ae.ActionTypeID
WHERE
	aat.ActionType IN 
	(
		'Orion.ViewAdded',
		'Orion.ViewChanged',
		'Orion.ViewDeleted',
		'Orion.ResourceAdded',
		'Orion.ResourceChanged',
		'Orion.ResourceDeleted'
	)

	AND DATEDIFF(DAY, ae.TimeLoggedUtc, getUTCdate()) <= 7 --over the last X days

ORDER BY ae.TimeLoggedUtc desc

 

 

 

View solution in original post

4 Replies

So, this is SQL script that you have asked for:

 

 

SELECT 
	COUNT(*) AS 'Total'
FROM AuditingEvents ae
JOIN AuditingActionTypes aat ON aat.ActionTypeID = ae.ActionTypeID
WHERE
	aat.ActionType IN 
	(
		'Orion.ViewAdded',
		'Orion.ViewChanged',
		'Orion.ViewDeleted'
	)

 

 

However, I personally don't find it very useful, as you do not know over what time this number is and what is actual split between added/created/modified

If I were you, I would:

  1. Add a split between created, modified and deleted
  2. Add a condition over how many days I want these numbers
  3. Add 3 more actions - resource added/modified/deleted, as these are elements of the view and sort of count towards view change as well

So, you would get something like this for the last 7 days:

 

 

SELECT 
	MIN(ae.TimeLoggedUtc) AS 'AuditStart',
	MAX(ae.TimeLoggedUtc) AS 'AuditEnd',
	DATEDIFF(DAY, MIN(ae.TimeLoggedUtc), MAX(ae.TimeLoggedUtc)) AS 'AuditDays',
	SUM(CASE WHEN aat.ActionType = 'Orion.ViewAdded' THEN 1 ELSE 0 END) AS 'ViewAdded',
	SUM(CASE WHEN aat.ActionType = 'Orion.ViewChanged' THEN 1 ELSE 0 END) AS 'ViewChanged',
	SUM(CASE WHEN aat.ActionType = 'Orion.ViewDeleted' THEN 1 ELSE 0 END) AS 'ViewDeleted',
	SUM(CASE WHEN aat.ActionType = 'Orion.ResourceAdded' THEN 1 ELSE 0 END) AS 'ResourceAdded',
	SUM(CASE WHEN aat.ActionType = 'Orion.ResourceChanged' THEN 1 ELSE 0 END) AS 'ResourceChanged',
	SUM(CASE WHEN aat.ActionType = 'Orion.ResourceDeleted' THEN 1 ELSE 0 END) AS 'ResourceDeleted'
FROM AuditingEvents ae
JOIN AuditingActionTypes aat ON aat.ActionTypeID = ae.ActionTypeID
WHERE
	aat.ActionType IN 
	(
		'Orion.ViewAdded',
		'Orion.ViewChanged',
		'Orion.ViewDeleted',
		'Orion.ResourceAdded',
		'Orion.ResourceChanged',
		'Orion.ResourceDeleted'
	)

	AND DATEDIFF(DAY, ae.TimeLoggedUtc, getUTCdate()) <= 7 --over the last X days

 

 

Output:
01.PNG

And lastly, having these numbers would immediately raise a question - who has done it, when and why. Below is a flat-view script that will give you nice output for every modification above, including user ID, so you can go and chase your team to stop changing them, as well as educate them that any change has a global effect ... ye, I know what you feel buddy - I have the same problem 😉

 

 

SELECT 
	ae.TimeLoggedUtc,
	ae.AccountID,
	aat.ActionTypeDisplayName,
	ae.AuditEventMessage
FROM AuditingEvents ae
JOIN AuditingActionTypes aat ON aat.ActionTypeID = ae.ActionTypeID
WHERE
	aat.ActionType IN 
	(
		'Orion.ViewAdded',
		'Orion.ViewChanged',
		'Orion.ViewDeleted',
		'Orion.ResourceAdded',
		'Orion.ResourceChanged',
		'Orion.ResourceDeleted'
	)

	AND DATEDIFF(DAY, ae.TimeLoggedUtc, getUTCdate()) <= 7 --over the last X days

ORDER BY ae.TimeLoggedUtc desc

 

 

 

View solution in original post

@AlexSoul ,

 

Wonderful and Thank you for such script.

Appreciate your help.

 

0 Kudos
This is a great question. I am on mobile now but I will check this when back at PC. Out of my head - it is likely not possible, due to the fact that auditing events for such actions may not be there. If they are however - SQL is very simple and I will help if no one else replies by then...
0 Kudos
The audit type events are only stored for 30 days by default, so you'll only be able to see the created, edited, deleted stuff for that period of time.

With that said, pretty sure you could get all the audit info you are asking for without sql or anything that elaborate. Just a regular audit events report filtered to those 3 types of events.
- Marc Netterfield, Github