I have been working on this issue for a couple of days now.
My customer wants a report to show:
WHEN every SERVER has been UNMANAGED and by what USER in the past week Mon-Mon.
I have not been able to find any table to have all the info needed in one place. Here is what I am using for my 2 queries.
_______________________________________________
Statement 1:
select
IP_Address as 'IP',
caption as 'Name',
UnManageFrom as 'DTG Unmanaged',
StatusDescription as 'Status',
S06_PRIORITY as 'Priority',
S05_ENVIRONMENT,
S07_STATUS
from Nodes
where N01_NODE_TYPE = 'SERVER' and UnManaged = 1
AND S05_ENVIRONMENT IN ('PROD', 'DR')
ORDER BY UnManageFrom, S05_ENVIRONMENT, S06_PRIORITY ASC
Statement 2:
select
TimeLoggedUtc as 'DTG',
AccountID as 'User',
AuditEventMessage as 'Message'
from AuditingEvents
Where AuditEventMessage like '%unmanaged node%'
ORDER BY DTG ASC
__________________________________
I am needing to find a way to get these two to merge together but cannot find a common denominator to link them. I am trying to SUBSTRING but I am not finding the right way to pull the information from the statement. Basically I need to pull everything behind unmanaged node. Any ideas?
AuditEventMessage
User AA\hendabfddeb1 unmanaged node BECAUSEIMSTUCK.
User AA\henddeb1 unmanaged node HARRYPOTTER.
User AA\fgfhend6yhbgdeb1 unmanaged node PEANUTS.
User AA\henfgae4hddeb1 unmanaged node 46782GJMFHCF.
User AA\hesdcvgnddeb1 unmanaged node JUSTAPLACE.
User AA\henplokijuhygfddeb1 unmanaged node NEVERBEENTHERE.
User AA\fgfhendsv841srrdeb1 unmanaged node NAMETHISNAME.
User AA\hsfdgenddeb1 unmanaged node ADLKDINC.
User AA\hgh666enddeb1 unmanaged node DONEDY,
User AA\henghdg5ddeb1 unmanaged node 3456TYHGFTG.
User AA\henddeb1 unmanaged node SDFASDFAD77.
User AA\s5hj7ujhenddeb1 unmanaged node 65R4FGHU6.
User AA\hendgdfsgfsgdfgeb1 unmanaged node 22.
User AA\fghheeffnddeb41 unmanaged node ifjygh-AR-1 - 000000000.
I tried to Substring but because everything behind unmanaged node is a different length, i am unsure how to include it all.
select
substring(AuditEventMessage,31,45) as 'new',
AuditEventMessage as 'Message'
from AuditingEvents
Where AuditEventMessage like '%unmanaged node%'
ORDER BY DTG ASC