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!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...