Splunk Search

How to edit my coalesce search to obtain a list of hostnames occurring in specific sources in my data?

renems
Communicator

Hi All,

I have several CSV's from management tools. All containing hostinfo, all of course in their own, beautiful way.

Some snippets:

sccm:
"ADAH0053";"";"10.34.6.53";"VMware, Inc.";"VMware Virtual Platform";"16777663";"8388152";"43002";"None";"GUID:8249E39E-52C4-4962-B023-F2F52DD87B36"
"ADAH0104";"";"10.34.6.204";"VMware, Inc.";"VMware Virtual Platform";"16777675";"4193848";"40954";"None";"GUID:3BB0D76A-DE24-40CB-8424-C8B76B4C6CA1"

Rain:
"ADHOST011";"C2710AF21NG0003";"Yes";"Supermicro";"XXXXX";"SYS-2082TP-HC1R";"No";"VMware ESXi";"Active";"192.168.1.126,192.168.4.126";"2017-01-04T01:00:15+01:00"
"adhost013";"C2710AF22NG0200";"Yes";"Supermicro";"XXXXX";"SYS-2082TP-HC1R";"No";"VMware ESXi";"Active";"192.168.1.018,192.168.4.018";"2017-01-04T01:00:15+01:00"

TopDesk:
ADDS0110,ADDS0110,Enabled,10.34.48.10,00:0C:29:0A:30:25,"Microsoft Windows Server 2012 R2 Datacenter",VMware-56 4d 77 e2 72 e4 43 47-85 e7 55 ad bb 0a 30 25,
ADDS0111,ADDS0111,Enabled,10.34.48.11,00:50:56:9D:05:60,"Microsoft Windows Server 2012 R2 Datacenter",VMware-42 1d 68 18 93 2e 32 44-d1 c6 f6 8b 16 9d 9c 3f,

Each file contains hostnames. I tied the hostnames together by using coalesce:

| eval host_new=coalesce(HostName,COMP_Name,'Object ID')

Now, my question: I want to have a list of hostnames that are occurring in sources "SCCM" and "RAIN", but not in source "TopDesk". Who can help me out? Bonus question: is using coalesce the best possible way to correlate over the different (hostname-) fields?

Thanx in advance!

0 Karma
1 Solution

rjthibod
Champion

I think you are OK to use coalesce. The only thing I can think of that is more explicit would be using a case statement.

Regardless of how you determine the host_new field, here is a query that will show you the count by host and sourcetype (assuming "SCCM", "Rain", "TopDesk", etc. are differentitated by sourcetype). If sourcetype is not correct, then change that field to the correct field that identifies the sources.

 <YOUR QUERY> ....
| eval host_new=coalesce(HostName,COMP_Name,'Object ID', ...)
| chart count by host_new sourcetype
| where (SCCM > 0 AND Rain > 0 AND TopDesk = 0)

View solution in original post

0 Karma

rjthibod
Champion

I think you are OK to use coalesce. The only thing I can think of that is more explicit would be using a case statement.

Regardless of how you determine the host_new field, here is a query that will show you the count by host and sourcetype (assuming "SCCM", "Rain", "TopDesk", etc. are differentitated by sourcetype). If sourcetype is not correct, then change that field to the correct field that identifies the sources.

 <YOUR QUERY> ....
| eval host_new=coalesce(HostName,COMP_Name,'Object ID', ...)
| chart count by host_new sourcetype
| where (SCCM > 0 AND Rain > 0 AND TopDesk = 0)
0 Karma
Get Updates on the Splunk Community!

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...

DevSecOps: Why You Should Care and How To Get Started

 WATCH NOW In this Tech Talk we will talk about what people mean by DevSecOps and deep dive into the different ...

Introducing Ingest Actions: Filter, Mask, Route, Repeat

WATCH NOW Ingest Actions (IA) is the best new way to easily filter, mask and route your data in Splunk® ...