I have a field called hostname,domain,ipaddress all my 5 sourcetypes are having same fieldname, I want to compare all the sourcetypes with those filed values existence, if it's existing then the table values should be yes and if not No.
hostname|domain |ipaddress |sourcetype1|sourcetypes2|sourcetype3|sourcetype4|sourcetype5|
host1 |prod.com |10.50.45.34| Yes |NO |YES |YES |NO |
@vemurisurya, try the following run anywhere search extended from the sample data provided in your question. The commands from | makeresults
till | table _raw
cooks up dummy data. You would need to use your own base search to get the data from five sourcetypes you have. I have added sourcetype in data however you should already have it available in your base search. Also, if you already have field extraction in place already you will not need | extract pairdelim="," kvdelim="="
The foreach command sets the specific sourcetype value as Yes
if the count is 1
, otherwise it sets it as No
i.e. for 0
or NULL
PS: If there is a pattern in the five sourcetype names you should use wildcard in foreach and table command like for the above query I could have used | foreach sourcetype* ...
or | table hostname domain ipaddress sourcetype*
| makeresults
| eval data="2018-03-14 06:35:06.828, hostname=\"host1\", domain=\"prod.com\", ipaddress=\"10.50.45.34\", clustername=\"APIs\", sourcetype=\"sourcetype1\";2018-03-12 13:20:18.027, hostname=\"host1\", domain=\"prod.com\", ipaddress=\"10.50.45.34\", clustername=\"APIs\", sourcetype=\"sourcetype3\";2018-03-14 07:20:26.327, hostname=\"host1\", domain=\"prod.com\", ipaddress=\"10.50.45.34\", clustername=\"APIs\", sourcetype=\"sourcetype4\";2018-03-14 06:35:06.828, hostname=\"host2\", domain=\"stg.com\", ipaddress=\"10.50.45.35\", clustername=\"APIs\", sourcetype=\"sourcetype2\";2018-03-12 13:20:18.027, hostname=\"host2\", domain=\"stg.com\", ipaddress=\"10.50.45.35\", clustername=\"APIs\", sourcetype=\"sourcetype4\";2018-03-14 07:20:26.327, hostname=\"host2\", domain=\"stg.com\", ipaddress=\"10.50.45.35\", clustername=\"APIs\", sourcetype=\"sourcetype5\";2018-03-14 06:35:06.828, hostname=\"host3\", domain=\"prod.com\", ipaddress=\"10.50.45.36\", clustername=\"APIs\", sourcetype=\"sourcetype1\";2018-03-12 13:20:18.027, hostname=\"host3\", domain=\"prod.com\", ipaddress=\"10.50.45.36\", clustername=\"APIs\", sourcetype=\"sourcetype2\";2018-03-14 07:20:26.327, hostname=\"host3\", domain=\"prod.com\", ipaddress=\"10.50.45.36\", clustername=\"APIs\", sourcetype=\"sourcetype5\";"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| table _raw
| extract pairdelim="," kvdelim="="
| eval key=hostname."-".domain."-".ipaddress
| fields - hostname domain ipaddress
| chart count over key by sourcetype
| foreach sourcetype1,sourcetype2,sourcetype3,sourcetype4,sourcetype5 [eval <<FIELD>>=if(<<FIELD>>=1,"Yes","No")]
| makemv key delim="-"
| eval hostname=mvindex(key,0),domain=mvindex(key,1),ipaddress=mvindex(key,2)
| fields - key
| table hostname domain ipaddress sourcetype1 sourcetype2 sourcetype3 sourcetype4 sourcetype5
@vemurisurya can you add sample data from 5 sourcetype. When a field does not exist in a sourcetype will all three fields not exist or can it be any one or two missing as well?
thanks for help, here is the sample data
sourcetyep1
3/14/18
6:35:06.828 AM
2018-03-14 06:35:06.828, hostname="host1", domain="prod.com", ipaddress="10.50.45.34", clustername="APIs"
sourcetyep2
no record
sourcetype3
3/12/18
13:20:18.027 PM
2018-03-14 06:35:06.828, hostarc="host1", domain="prod.com", ipaddress="10.50.45.34", clustername="APIs" hostarc must rename as hostname
sourcetype4:
3/14/18
7:20:26.327 AM
2018-03-14 06:35:06.828, hostname="host1", domain="prod.com", ipaddress="10.50.45.34", clustername="APIs"
sourcetype5:
NOdata