topic Re: What do I need to alter in my search to get these fields? in Splunk Search

What do I need to alter in my search to get these fields?

splunker969 — Tue, 07 Nov 2017 16:11:45 GMT

Hi ,

We have two lists of CSV files. Each one has 500 hosts and for each we need to figure out among hosts which are reporting to Splunk or not. For that I created a lookup and I'm able to see some hosts are not reporting to Splunk since I need to combine the list and also check which hosts are not reporting to the deployment server. The reason to check the deployment server is that we need to install agents on hosts which do not have among two csv files. So actually I am looking for a search that shows these columns: host, IP age , Last time reporting Splunk and agent version, reporting deployment server or not. I have two queries. Please help me search to check the lists of the servers that are reporting Splunk and the deployment.

|metadata type=hosts index=* |lookup samplehostsrecentlist.csv host output PCI host os IP  |search PCI=Y |eval age=(now()-recentTime)|search age >1|convert ctime(*Time)| append[  |inputlookup samplehostsrecentlist.csv ] | dedup host | fields host IP PCI os lastTime age | sort lastTime|  convert timeformat="%Y-%m-%d %k:%M:%S" ctime(current_time) as current_time ctime(last_login_time) as last_login_time rmunit(age) as numSecs  | eval stringSecs=tostring(numSecs,"duration")
 | eval stringSecs=case(stringSecs="00:00:00", "0+0:0:0", 0=0, stringSecs)
 | eval stringSecs = replace(stringSecs,"(\d+)\:(\d+)\:(\d+)","\1h \2min \3s") | fields - age current_time numSecs | rename stringSecs as age | sort - age

index=_internal source=*metrics.log* fwdType=uf  
| stats values(version) as Version values(os) as OS values(fwdType) as ForwarderType values(build) as Build by hostname
| join type=outer hostname [|inputlookup sample1hostsrecentlist.csv | eval hostname=host | table hostname PCI]
| join type=outer hostname [|inputlookup sample2hostsrecentlist.csv | eval hostname=host | table hostname sox]
| where PCI="y" OR sox="y" | rename hostname as Host

Re: What do I need to alter in my search to get these fields?

splunker969 — Wed, 08 Nov 2017 15:47:54 GMT

@somesoni2

Re: What do I need to alter in my search to get these fields?

somesoni2 — Wed, 08 Nov 2017 20:13:12 GMT

Does the 2nd query populate lookup samplehostsrecentlist.csv?

Re: What do I need to alter in my search to get these fields?

splunker969 — Wed, 08 Nov 2017 20:57:04 GMT

Can you allobarate.I am not getting you?@somesoni2

Re: What do I need to alter in my search to get these fields?

somesoni2 — Thu, 09 Nov 2017 02:54:34 GMT

Give this a try (assuming your deployment client phonehome period is less than an hr)

|metadata type=hosts index=* |lookup samplehostsrecentlist.csv host output PCI host os IP  |search PCI=Y |eval age=(now()-recentTime)|search age >1
| append [search index=_internal sourcetype=splunkd component=HttpPubSubConnection phonehome earliest=-1h@h | stats max(_time) as lastPhoneHomeTime by host] | stats values(*) as * by host |convert ctime(*Time)
| append[  |inputlookup samplehostsrecentlist.csv ] | dedup host | fields host IP PCI os lastTime lastPhoneHomeTime  age | sort lastTime|  convert timeformat="%Y-%m-%d %k:%M:%S" ctime(current_time) as current_time ctime(last_login_time) as last_login_time rmunit(age) as numSecs  | eval stringSecs=tostring(numSecs,"duration")
  | eval stringSecs=case(stringSecs="00:00:00", "0+0:0:0", 0=0, stringSecs)
  | eval stringSecs = replace(stringSecs,"(\d+)\:(\d+)\:(\d+)","\1h \2min \3s") | fields - age current_time numSecs | rename stringSecs as age | sort - age

Re: What do I need to alter in my search to get these fields?

splunker969 — Thu, 09 Nov 2017 15:54:13 GMT

@somesoni2 , I see no results in column lastPhoneHomeTime .Thanks.

Re: What do I need to alter in my search to get these fields?

somesoni2 — Thu, 09 Nov 2017 16:03:53 GMT

Ok. See this search alone give you result.

index=_internal sourcetype=splunkd component=HttpPubSubConnection phonehome earliest=-1h@h  [ |inputlookup samplehostsrecentlist.csv |search PCI="Y" | table host]| stats max(_time) as lastPhoneHomeTime by host

Re: What do I need to alter in my search to get these fields?

splunker969 — Thu, 09 Nov 2017 16:07:14 GMT

Got results in Epoch Time for cloumn "lastPhoneHomeTime" .I believe hosts are not "PCI=y "

Re: What do I need to alter in my search to get these fields?

somesoni2 — Thu, 09 Nov 2017 16:23:16 GMT

In your first search (| metadata .. one), you're using filter with PCI=Y, So I assume you're interested in getting inventory for PCI servers only. Am I correct?
Try change earliest=-1h@h to earliest=-7d@d.

Re: What do I need to alter in my search to get these fields?

splunker969 — Thu, 09 Nov 2017 18:53:07 GMT

Yes.Your correct .Iam especially looking for PCI=Y .

Re: What do I need to alter in my search to get these fields?

somesoni2 — Thu, 09 Nov 2017 19:08:58 GMT

Does it give you any record when you increase the time range?

Re: What do I need to alter in my search to get these fields?

splunker969 — Thu, 09 Nov 2017 19:14:33 GMT

No it does not gave result @somesoni2

Re: What do I need to alter in my search to get these fields?

somesoni2 — Thu, 09 Nov 2017 19:17:21 GMT

Thats the query I use to find which clients are phoning home to deployment server. Do you use deployment server for any of the PCI servers of yours? Can you check if internal logs are being forwarded from your universal forwarders to Indexers?

Re: What do I need to alter in my search to get these fields?

splunker969 — Thu, 09 Nov 2017 21:21:59 GMT

Hi @somesoni2,It gives Results of different servers not pci=y and When I include the same in the big search that you provided it wont work .Yes, We use deployment server for the PCI servers .Yes, internal logs are being forwarded from our universal forwarders to Indexers.