Solved: Re: How to compare search and csv file? - Page 2

vrmandadi · ‎03-24-2016

Hello ,

I have a search which gives me all the unique hostnames and csv file which also has the hostnames ,but I am trying to compare both and I want three reports

1)to get all the unique hostnames which are there in both search and csv file
2)get the unique host names which are there only in csv file NOT in search
3)get the unique host names which are there in search but NOT in csv file

the problem here is the search has the hostname field as Host_name and the csv file has assestname as hostname and has an extension for few of them
example

host_name(search)

s222pnlp
S5234gnn
S5135

assestname(csv)

s222pnlp.asd
S5234gnn.yuv
S5135

somesoni2 · ‎03-24-2016

Give this a try

your search giving field host_name | eval From="Search" | append [ Your search to get field assetname from csv | eval host_name=mvindex(split(assetname,"."),0) | table host_name | eval From="CSV" ] | stats values(From) as From by host_name
| eval status=case(mvcount(From)=2,"Present in both", From="Search","Only available in search",1=1,"Only available in CSV") | table host_name status

Updated*

 index=os sourcetype="app-inventory" ad_hostname="*" |table ad_hostname |dedup ad_hostname | rename ad_hostname as Host_Name
 | append [search index=os sourcetype="app-inventory"  altiris_hostname="*" | table altiris_hostname |dedup altiris_hostname | rename altiris_hostname as Host_Name]
 | append [search index=os sourcetype="app-inventory" dns_hostname="*" |table dns_hostname| dedup dns_hostname | rename dns_hostname as Host_Name] | eval From=1
 | append [| inputlookup nexposeallassets.csv |table "Asset Names" | eval Host_Name=mvindex(split('Asset Names',"."),0) | table host_name | eval From=2 ] | stats sum(From) as From by Host_Name
  | eval status=case(From=3,"Present in both", From=1,"Only available in search",1=1,"Only available in CSV") | table Host_Name status

To filter the values which are unique to both combined and not present in both, add following to above search

above search | where status!="Present in both"

View solution in original post

muebel · ‎03-24-2016

if you need to format one of the values or the other, you can use the rex command like:

rex hostname="^(?<hostname>[^\.]).*"

will take all hostname values, and set them to be everything up until the first dot

If this isn't exactly what you are after, you can use combinations of different regexes and the rex command, as well as the various eval functions in order to massage the values as needed.

vrmandadi · ‎03-24-2016

well my csv file has hostname with .abc.int or .auther.inc ,i just want to remove these extensions in the csv ,can we do that

example

host_name
s4000cawv.abc.int
achaesdb01.auther.inc

How to compare search and csv file?

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Monitoring AI Agents with Splunk Observability Cloud

[Puzzles] Solve, Learn, Repeat: Tiling

SOK it to Me: Top 3 Benefits of Using Splunk Operator on Kubernetes that’ll Make ...

Join the Conversation