Need a report that shows the date of last updated record for licensed users so We can more effectively manage their license allocation.

[maheshchaudhary]

Hi All, 

 I have a requirement in that 

1. Create report that shows when either Entity or Remediation records were last updated and who the user was.
2. This only applies to Active licensed users 
3. It should only show the user, last updated date, and record type updated (Entity/Remediation)

I’m wondering if in Community anyone else has tried to identify when a user last updated a record (any record really).  Perhaps we look at it from that perspective – the date a user last made an update on any record in ServiceNow? 

  I’m totally open to suggestions here.

[maheshchaudhary]

i want to query Remediation (grc_remediation) table's records

[Priyanka Gupta]

I think I have found something that should work.

You need to-

• Create a view joining sys_user, sys_user_role, incident tables (I chose Incident table because I don't have remediation table in my instance but what shouldn't matter since we are talking about inbuilt fields) like this -
• You can then run a report on this view and you should get what you need.

Let me know if this helps.

Priyanka

[maheshchaudhary]

Hi Priyanka, I tried to create Database View but it seems i'm missing something. it's not giving proper complete data. please find below.

[Priyanka Gupta]

It is working if you remove usr_active='True' in the where clause on sys_user. I am trying to find out why it wouldn't take that in the where clause. Meanwhile, try without that.

[Priyanka Gupta]

I think by default inactive users do not show in the list. Here's how I can see that-

So, try without usr_active and it should work fine.