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!

Security Highlights: September 2022 Newsletter

 September 2022 The Splunk App for Fraud Analytics (SFA) is now Splunk SupportedUse your existing Splunk ...

Platform Highlights | September 2022 Newsletter

 September 2022 What’s New in 9.0 and How to UpgradeGet a walk through of what is new Splunk Enterprise 9.0 ...

Observability Highlights | September 2022 Newsletter

 September 2022 Splunk Observability SuiteAccess to "Classic" SignalFx Interface Will be Removed on Sept 30, ...