Database View help and help with creating where clauses

[Denise Smith]

I need help to track cpu count changes for servers

a/ tables can be sys audit or sys history line - which is preferred?

b/ tables can be linux or windows tables, or just overall the computer table?

c/ I am in a domain separated instance, so I will need Company name, Class, and the cpu count for Old and New Values for each class. eg.for windows, linux servers.

d/ What i need is the database view example, as well as the where clauses that I would need. I can't find help anywhere. And time is important for me to get this. Any help much appreciated!

Overall ask:  I am trying to track cpu increases and decreases for my accounts on their servers.

ps. I attach what I need, but again, due to me just creating a report and just on the audit table (sysaudit) - it is not enough: I would need a database view to pull in fields such as class, and customer/domain field from either the computer table or Server table(s) where the fields exist.

Thanks

Denise.

[Robert H · Accepted solution]

Hello @Denise Smith ,

Please create a new Database view, enter some name and label, and add two View Tables:

1. Server [cmdb_ci_server], with Variable prefix = ci
2. Audit [sys_audit], with Variable prefix = audit, and the following Where clause:
   ci_sys_id = audit_documentkey && audit_fieldname = 'cpu_count'

Then click Try it, configure the columns you would like to see and you will get something like this:

Note: the view will have two "Created" columns, one for the server and one for the audit record (= the time where the change was made), so please make sure you pick the correct one.

Regards,

Robert

[Denise Smith]

One last question: I ran report and discovery source field called 'servicenow' only shows for my result how can I show other values eg. empty or unknown?

whereclause: for 2 companies in my domain: I need to show all servers for a company where cpu count changed and what the discovery source was.  This ran fine, but only brought in 'Servicenow' as source

aud_documentkey = comp_sys_id && comp_company="axxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" && aud_fieldname="cpu_count" || aud_documentkey = comp_sys_id && comp_company="f9xxxxxxxxxxxxxxxxx" && aud_fieldname="cpu_count"

[Denise Smith]

Thanks!