topic Re: Compare CSV to search in Splunk Search

Compare CSV to search

H4waiianPunch — Wed, 25 Sep 2024 19:14:25 GMT

Hello everyone,

I'd like to start out by saying I'm really quite new to Splunk, and we run older versions(6.6.3 and 7.2.3).

I'm looking to have a search that will do the following:

- Look up the current hosts in our system, which I can get with the following search

index=* "daily.cvd" | dedup host | table host

- Then compare to a CSV file that has 1 column with A1 being "host" and then all other entries are the hosts that SHOULD be present/accounted for.

-- Using ChatGPT I was able to get something like below which on it's own will properly read the CSV file and output the hosts in it.

- However when I combine the 2, it will show me 118 results(should only be 59) and there are no results in the "current_hosts" column, and after 59 blank results, the "known_hosts" will then show the correct results from the CSV.

I'd love to have any help on this, I'm wouldn't be surprised if ChatGPT is making things more difficult than needed.

Thanks in advance!

Re: Compare CSV to search

marnall — Wed, 25 Sep 2024 19:43:39 GMT

If I understand correctly, you would like the final output to be two columns, where one shows the machines that SHOULD appear, and the second shows the machines that DO appear? Then you could see which machines are not appearing and therefore need attention?

E.g.

SHOULD_APPEAR	DO_APPEAR
host1	host1
host2
host3	host3
...	...

Re: Compare CSV to search

H4waiianPunch — Wed, 25 Sep 2024 19:46:01 GMT

Either two columns as you described, or two columns with machines that SHOULD appear and another column saying Missing if it's not there or New if it's new and unexpected. That way I wouldn't need to look through them as thoroughly and at a glance be able to see if something is wrong.

Re: Compare CSV to search

marnall — Wed, 25 Sep 2024 20:18:27 GMT

Have a go at this:

Make sure you have a lookup table (hosts.csv) with a single "host" column containing all your expected hosts.

Re: Compare CSV to search

yuanliu — Thu, 26 Sep 2024 04:00:34 GMT

ChatGPT is perhaps the last place you want to learn SPL from. The task is relative straightforward.

Re: Compare CSV to search

H4waiianPunch — Thu, 26 Sep 2024 16:53:33 GMT

Hey, I certainly agree that ChatGPT isn't the best place to learn, but it comes in handy sometimes. I need to start taking some actual training though.

Your solution did work, so thank you for sharing it with me. I did then go and use GPT to help explain the details to me and I think I understand it all so that's nice. Setting sources to different values and comparing them that way is neat and I'm glad I've seen that now.

Re: Compare CSV to search

H4waiianPunch — Thu, 26 Sep 2024 16:54:50 GMT

Hey, thank you very much for this query! I've decided to go with yours out of the 2 responses here as it displays just the one host in the end instead of all of them which will be nicer at a glance.

You made it seem very simple and I appreciate that, I have a lot to learn!