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
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
@somesoni2 , I see no results in column lastPhoneHomeTime .Thanks.
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
Got results in Epoch Time for cloumn "lastPhoneHomeTime" .I believe hosts are not "PCI=y "
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.
Yes.Your correct .Iam especially looking for PCI=Y .
Does it give you any record when you increase the time range?
No it does not gave result @somesoni2
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?
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.
@somesoni2
Does the 2nd query populate lookup samplehostsrecentlist.csv?
Can you allobarate.I am not getting you?@somesoni2