Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

User Actions in Last N Days report

Created: 22 Jan 2014 • Updated: 22 Jan 2014 | 13 comments
SK's picture
0 0 Votes
Login to vote

Although this report was created for 6.x, it does still run on 7.x as well.

Its dropdowns allow you select the type of action as well as the user you are interested.

It also has a dialog box where you can enter the number of days you wish the report to display data for.

Comments 13 CommentsJump to latest comment

Sally5432's picture

Interesting report... thanks for sharing.  For me, the user drop down only has 2 of our users.. not sure why.

---
Don't forget to mark posts as helpful if they are, and mark answers as solutions.

0
Login to vote
SK's picture

The report is only interrogating the Evt_NS_Item_Management table, as it contains all required information.

 

The user selection dropdown uses the following query:

SELECT DISTINCT [User] FROM Evt_NS_Item_Management ORDER BY [User] ASC

 

As the dropdown only lisst two users, then could it be that only two console users have performed any of those actions during the time period contained within that table?

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

0
Login to vote
Sally5432's picture

I was thinking that, but the user name field is populated and doesn't change when I change the days.  I have 2 helpdesk techs that use quick delivery pretty often that aren't in the drop down. 

I never had the info before, so no big deal, just woulda been something nice to have available.

---
Don't forget to mark posts as helpful if they are, and mark answers as solutions.

0
Login to vote
SK's picture

How many users does the following query return?

SELECT DISTINCT [User] FROM Evt_NS_Item_Management ORDER BY [User] ASC

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

0
Login to vote
Sally5432's picture

Same 3 users.  Maybe the helpdesk users running quick runs to install software doesn't translate to that table?  Both helpdesk techs installed software via quick delivery today.

---
Don't forget to mark posts as helpful if they are, and mark answers as solutions.

0
Login to vote
SK's picture

Perhaps they are logging in as the AppId?

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

0
Login to vote
Sally5432's picture

We use windows auth.  Not sure how they'd do this.  maybe NS is logging it as our CMS notification user or something.

---
Don't forget to mark posts as helpful if they are, and mark answers as solutions.

0
Login to vote
SK's picture

I have just added a user to a role and ran a quick delivery and the report now lists that user, which indicates that the table being used only holds X number of days worth of data.

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

0
Login to vote
skhs's picture

SK thank you so much, this is great. Just one question is it possible to know what exactly a user done. For example if I edit a computer in this table it only shows as "modify", but it will be valuable to know what I changed so if we need to review that can be done. 

0
Login to vote
SK's picture

Unfortunately the SMP does not record that type of detail.

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

0
Login to vote
ortolani's picture

You can use this query

SELECT [User]
    ,ItemName AS TaskName
    ,ItemAttributes AS TaskAttributes
    ,Action AS TaskAction
    ,convert(varchar, _eventTime, 120) AS TaskDateTime
    ,[Description] AS [TaskDescription]
FROM Evt_NS_Item_Management
WHERE ItemName <> ''
    AND replace(convert(varchar, _eventTime, 111), '/', '-') BETWEEN '2014-01-10' AND '2014-01-20'
ORDER BY _eventTime ASC

this will list the username, task_name, action (modify, New, Run, Delete), and when was run

 

one good thing is that this info stay for a long time, so you can group the users a have reports displaying what tasks was run by specific user and when.

0
Login to vote
SK's picture

That still doesn't tell you what was modified; however, the good thing about tasks is that they do record that information but I can't remember where it is. 

Select a task, and then press the "Modified N times" link, where N = the number of times the task has been modified.  Select each rom and a picture will be displayed.

It's a pity that no other item records it.

Connect Etiquette: "Mark as Solution" those posts which resolve your problem, and give a thumbs up to useful comments, articles and downloads.

0
Login to vote
ortolani's picture

I think I got something that mind help

SELECT EvtItem.[User]
    ,EvtItem.ItemName AS TaskName
    ,EvtItem.ItemAttributes AS TaskAttributes
    ,EvtItem.Action AS TaskAction
    ,convert(varchar, _eventTime, 120) AS TaskDateTime
    ,EvtItem.[Description] AS [TaskDescription]
    ,EvtItem.ItemGuid
    ,ItemVerData.[State]
    ,ItemVerData.[Name]
    ,ItemVerData.[ModifiedBy]
    ,ItemVerData.[ModifiedDate]    
FROM Evt_NS_Item_Management AS EvtItem
    LEFT JOIN [Symantec_CMDB].[dbo].[ItemVersions] AS ItemVer
        ON EvtItem.ItemGuid = ItemVer.ItemGuid
    LEFT JOIN [Symantec_CMDB].[dbo].[ItemVersionData] AS ItemVerData
        ON ItemVer.VersionGuid = ItemVerData.VersionGuid
WHERE ItemName <> ''
    AND replace(convert(varchar, _eventTime, 111), '/', '-') = '2014-01-10'
    AND EvtItem.ItemGuid = '2C309C89-E88F-45EA-A70A-E98675415200'
ORDER BY _eventTime ASC

 

this will show a specific task, and all the modification that was done to the task in a xml format, it show the user and date modified, inside the XML column [State] show the settings changes as well script if the task was a script. The problem is the info on the XML is not easy to read, so it will need to be redone so is easy to read the changes :)

bold part is just a random Guid that I use, if you want it could change FROM EvtItem.ItemGuid to EvtItem.ItemName, so is easy to search for it.

0
Login to vote