How to efficiently get a list of of Windows hosts WITH the Target Domain Name field?

[sm600]

Howdy. For quite a while we have been using this to generate a useful and pretty list of all Windows Server hosts, showing also the last time they reported to Splunk.

|metadata type=hosts index=ms_evt_security| convert timeformat=" %m/%d/%Y" ctime(*) none(host) none(type) none(totalCount) |rename firstTime AS first, recentTime AS last, totalCount as total | table host,first,last,total | sort – total

Unfortunately -- metadata does not include some key attributes of the server that we need for additional analysis. One of those fields is the TargetDomainName.

So...instead of the quick and pretty metadata search, I tried using this:

index=ms_evt_security | stats count by host TargetDomainName | dedup host

But...this will either take 3 hours to run (especially if we are interested learning when a host stopped reporting, say 2 months ago), and it also does not provide the "first reported" and "last reported" data.

I'd love some suggestions as to how I might "simply" integrate the TargetDomainName field from the log data, into the metadata listing.

Thanks,

Steve

[dstaulcu]

I maintain a lookup table of adComputers having attributes of interest for each computer object such as: domain, name, sid, operatingSystem, lastLogonTimestamp, etc.

I export AD computer objects using powershell script, import that into a SQL server, and then export the table contents on SQL server as lookup table in Splunk using DBConnect.

If your active directory size is relatively small you could probably get away with using the splunk support for active directory app to keep all moving parts internal to Splunk.

[Richfez]

sm600,

appendcols might be what you want to use. The documentation for appendcols is great, and so is this answer by somesoni2 on how it works. If somesoni2's answer is helpful, please upvote it!