topic Re: chart by source and hostname in Splunk Search

chart by source and hostname

lbogle — Mon, 28 Sep 2020 17:08:41 GMT

Hello Splunkers,
I am trying to correlate hostnames to multiple sources (4 .csv host files) to see if I can find where the same hostnames show up in those sources. I have the data into Splunk and have individually done initial searches through each of the host files to give myself a clean list of hostnames but I don't quite know how to sort through the resulting data to chart which hostnames appear in what sources.

index=assets source="/scratch/assets/DG-Windows.csv" | dedup MACHINE_NAME
index=assets source="/scratch/assets/DG-Mac.csv" | dedup MACHINE_NAME
index=assets source="/scratch/assets/Altiris.csv | dedup System Name
index=assets source="/scratch/assets/Asset-Mgmt.csv" | dedup HostName

Does anyone know how this could be done?

Re: chart by source and hostname

strive — Wed, 23 Jul 2014 01:29:47 GMT

Do you want to chart hostname count VS source?
Also, i assume in the search #3 it is SystemName

Re: chart by source and hostname

lbogle — Wed, 23 Jul 2014 03:07:48 GMT

Hi,
Essentially, I'm trying to find a way to show which hostname is in which source. I'm imagining a chart where rows are hostnames and columns are the source.
Does that make sense?
Thanks!

Re: chart by source and hostname

lbogle — Wed, 23 Jul 2014 03:09:36 GMT

And the field it extracts is "System Name".
Thanks,

Re: chart by source and hostname

strive — Mon, 28 Sep 2020 17:08:46 GMT

Are you sure that you are clear with your use case. Since you have done dedup the maximum possible count for any host after |dedup MACHINE_NAME | stats count by MACHINE_NAME will be 1. When you put it table format, the values will be either 0 or 1. Something like below table.
Host_Name Source1 Source2 Source3 Source4
A 1 1 0 0
B 0 0 1 0
If your need is like above table then we can help you with the searches.

Re: chart by source and hostname

somesoni2 — Wed, 23 Jul 2014 14:50:52 GMT

Give this a try

index=assets source="/scratch/assets/DG-Windows.csv" OR source="/scratch/assets/DG-Mac.csv" OR  source="/scratch/assets/Altiris.csv" OR source="/scratch/assets/Asset-Mgmt.csv" 
| eval MACHINE_NAME=coalesce(coalesce(MACHINE_NAME,HostName),'System Name') | dedup source MACHINE_NAME | chart count over MACHINE_NAME by source

This will give you chart for which host appears in which source. Select the type of chart as stacked column chart to see the host on x-axis with name of source at columns.

Updated
Give this a try

|multisearch [search index=assets source="/scratch/assets/DG-Windows.csv" | dedup MACHINE_NAME][search index=assets source="/scratch/assets/DG-Mac.csv" | dedup MACHINE_NAME ][search index=assets source="/scratch/assets/Altiris.csv | dedup "System Name" |rename "SystemName" as MACHINE_NAME] [searchindex=assets source="/scratch/assets/Asset-Mgmt.csv" | dedup HostName | rename HostName as MACHINE_NAME] | chart count over MACHINE_NAME by source

Re: chart by source and hostname

lbogle — Wed, 23 Jul 2014 15:30:25 GMT

Thats exactly the information and format I am looking for! The search by somesoni2 is pretty close but it only seems to identify hosts that are in DG-Windows.csv OR DG-Mac.csv. It doesn't seem to identify any other .csv files in which the assets reside (which is the case). Thank you both for your valuable assistance.

Re: chart by source and hostname

lbogle — Wed, 23 Jul 2014 20:57:42 GMT

I am getting the following error when running this query:
Error in 'multisearch' command: Multisearch subsearches may only contain purely streaming operations (subsearch 1 contains a non-streaming command.)

Re: chart by source and hostname

lbogle — Mon, 28 Sep 2020 17:09:35 GMT

This worked for me. It's kind of cludgey looking but it gave me what I needed. Check it out:

index=assets source="/scratch/cadence_assets/*.csv" (NOT "System Type"="Virtual" NOT "System Type"="Server" NOT "System Type"="Thin Client") | rename "System Name" as MACHINE_NAME | rename "HostName" as MACHINE_NAME | dedup source MACHINE_NAME | chart count over MACHINE_NAME by source

Thank you all very much for your excellent suggestions and helpful queries!