topic Re: How to compare search and csv file? in Getting Data In

How to compare search and csv file?

vrmandadi — Thu, 24 Mar 2016 17:51:05 GMT

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

Re: How to compare search and csv file?

muebel — Thu, 24 Mar 2016 18:22:00 GMT

HI vrmandadi, I believe that this might be a good use for the "join" command : http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Join

You'll be able to break the results down based on the join type (outer, left, inner).

Please let me know if this answers your question!

Re: How to compare search and csv file?

vrmandadi — Thu, 24 Mar 2016 18:27:03 GMT

I tried using the join commands but the problem is that when it does the match the hostnames for csv has extensions for few of them and that is causing duplication,how can that be removed?

Re: How to compare search and csv file?

somesoni2 — Thu, 24 Mar 2016 18:33:55 GMT

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"

Re: How to compare search and csv file?

muebel — Thu, 24 Mar 2016 18:35:19 GMT

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.

Re: How to compare search and csv file?

woodcock — Thu, 24 Mar 2016 18:40:48 GMT

Try this as your base search:

hostnameSearchHere | eval From="Search" | appendpipe [|inputcsv max=0 assetname | eval From="CSVfile"] | rex field=Host_Name mode=sed "s/\.[^.]*$//" | stats dc(From) AS numFroms values(*) AS * by Host_Name

Then tack on what you need:
1: | where numFroms>1
2: | where numFroms=1 AND From="CSVfile"
3: | where numFroms=1 AND From="Search"

Re: How to compare search and csv file?

vrmandadi — Tue, 29 Sep 2020 09:14:32 GMT

I tried the below query

but the output only gives the hostnames available in search but not the whole list of all unique and csv

example

Host_Name status

s2324wee available

Re: How to compare search and csv file?

vrmandadi — Thu, 24 Mar 2016 19:00:23 GMT

Hello woodcock ,I am not able to understand what needs to be replaced in the append pipe

| appendpipe [|inputcsv max=0 assetname | eval From="CSVfile"] - yours

| appendpipe [|inputcsv max=0 "Asset Name" | eval From="nexposeallassets.csv"]-mine

and the numForms has only one value 2

Re: How to compare search and csv file?

vrmandadi — Thu, 24 Mar 2016 19:11:33 GMT

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

Re: How to compare search and csv file?

vrmandadi — Thu, 24 Mar 2016 19:12:41 GMT

Somesoni2

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.that will make easy to compare

example

host_name
s4000cawv.abc.int
achaesdb01.auther.inc

Re: How to compare search and csv file?

woodcock — Thu, 24 Mar 2016 19:15:49 GMT

You only need to replace assetname with your actual CSV file name. DO NOT CHANGE ANYTHING ELSE (until you see that it works).

Re: How to compare search and csv file?

vrmandadi — Thu, 24 Mar 2016 19:43:47 GMT

I can only see the values with numForms value 2

Re: How to compare search and csv file?

woodcock — Thu, 24 Mar 2016 19:48:16 GMT

That means that all Host_Name values occur in both data sets. And it is R-then-O, not O-then-R: numFroms.

Re: How to compare search and csv file?

vrmandadi — Thu, 24 Mar 2016 19:54:33 GMT

well my csv file has assestname with .abc.int or .auther.inc ,i just want to remove these extensions in the csv in splunk ,can we do that.that will make easy to compare

example

Assestname
s4000cawv.abc.int
achaesdb01.auther.inc

Re: How to compare search and csv file?

woodcock — Thu, 24 Mar 2016 20:20:02 GMT

My solution ALREADY DOES THAT. It is this part: | rex field=Host_Name mode=sed "s/\.[^.]*$//"

Re: How to compare search and csv file?

somesoni2 — Thu, 24 Mar 2016 20:39:22 GMT

I just checked your subsearch from the nexposeallassets.csv and the field name in eval is Host_Name but the one used in table is host_name. The field names are case sensitive and could be the reason you don't see anything from the CSV. Update "| table host_name" with "| table Host_Name" and try again

Re: How to compare search and csv file?

vrmandadi — Thu, 24 Mar 2016 20:41:22 GMT

I know you have taken a lot of time for me and helping me ,really thanks for that but Can you please tell me R-then-O, not O-then-R:numForms because I only see NumForm with 1. below is the query which I ran

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]|appendpipe[ inputlookup nexposeallassets.csv | table "Asset Names"]| appendpipe [|inputcsv max=0 "Asset Names" | eval From="CSVfile"] | rex field=Host_Name mode=sed "s/\.[^.]*$//" | stats dc(From) AS numFroms values(*) AS * by Host_Name

Re: How to compare search and csv file?

woodcock — Thu, 24 Mar 2016 21:23:47 GMT

You keep using f-O-R-m and I am using f-R-O-m. If you type it wrong, things will break.

These are the problems that I see with your search:
1(HUGE): Your search is much different than what you described in your question. I see 3 searches and 2 CSV files wherewas you described 1 search and 1 CSV.
2: You are missing a leading pipe (|) character before inputlookup so that segment of search will do nothing (no events).
3: You only have a single | eval From="<something> which is the key to getting the work done that you say that you need (I had | eval From="Search" that you did not use).

Re: How to compare search and csv file?

vrmandadi — Thu, 24 Mar 2016 21:24:15 GMT

I have changed that one also but I dont see anything from csv all I can see in status is field is only "Only available in search" but not the csv and a whole list of unique hostnames from both of them

Re: How to compare search and csv file?

somesoni2 — Thu, 24 Mar 2016 21:32:28 GMT

Give this a try. I changed the | eval Host_Name=mvindex(split("Asset Names","."),0) with | eval Host_Name=mvindex(split('Asset Names',"."),0) and the status logic

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