Inventory status monitoring

[new2spl_unk]

Dear all, 

Current situation is I uploaded a inventory table to Splunk and the table is like below.

Hostname  IP 

------------------

hostname1 6.6.6.6

hostname2 7.7.7.7

And I would like to check the log collection status (eg. Is the device sending log to splunk, what is the last log time) for the device on the list and produce a list like below. 

Hostname  IP  Status Last_log_time

------------------

hostname1 6.6.6.6 Yes 2021-03-03 00:00:00

hostname2 7.7.7.7 No N/A 

I tried to use "|metadata" but seems like the metadata is not accurate and may I have some idea of how to do this task? 

[manjunathmeti]

hi @new2spl_unk ,

From splunk documentaion on metadata command,

If you specify a time range other than All Time for your search, the search results might not be precise. The metadata is stored as aggregate numbers for each bucket on the index. A bucket is either included or not included based on the time range you specify.

So you need to select All Time in time range picker to work, also you cannot use earliest in the search query.

| metadata type=hosts index=* | lookup csvfilename.csv host AS Hostname OUTPUT IP

If this reply helps you, an upvote/like would be appreciated.

[new2spl_unk]

Thanks for your reply. 

But I did select All Time in time range to check if the device on the inventory list have within the last 30 days, but turn out that some device did have log in the past 30days, but the status shown "No log". 

Can you suggest me how to write a query for checking those device on list have log or not in the past 30 days? 

[manjunathmeti]

Try this:

| metadata type=hosts index=_* | where lastTime > relative_time(now(), "-30d")

[new2spl_unk]

Thanks for your reply and by using the query I am able to locate the device that have log within the past 30days. But additionally,  I need to compare it with a CSV and then produce a new CSV with status, which means, I need to produce a list like below, by comparing the metadata table and the inventory CSV. 

Hostname   IP  status

----------------------------

deviceA 1.1.1.1 "Receiving log"

deviceB 2.2.2.2 "No log can be found from past 30days" 

[manjunathmeti]

You can try this search:

| metadata type=hosts index=* | lookup csvfilename.csv host AS Hostname OUTPUT IP | eval status=if(lastTime>relative_time(now(), "-30d"), "Receiving log", "No log can be found from past 30days") | table Hostname, IP, status

If these replies help you, upvotes/likes would be appreciated.

[new2spl_unk]

Really appreciated with your reply. But this query will miss some device  that exist on the inventory list but never sent log to Splunk, I would like to mark the status on the inventory list rather than the metadata list. 

[manjunathmeti]

Try this:

| inputlookup csvfilename.csv | eval host=Hostname | append [| metadata type=hosts index=_* ] | stats max(*) as * by host | eval status=if(lastTime>relative_time(now(), "-30d"), "Receiving log", "No log can be found from past 30days") | table host, IP, status

If this reply helps you, an upvote/like would be appreciated.