Reporting

Comparing the hosts not reporting to indexer against the CSV inventory list ( host name) lookup

kagamalai
Explorer

I wan to take the not reporting  clients or not deployed client list UF ( hostname) from  the inventory.csv lookup

Please advise how do i take the same 

for example in a distributed environment i have total 1000 PC's and i want to know what are the PC's not deployed or not reporting to indexer from the inventory list of 1000 host name 

 

 

 

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
index=*
| fields host
| dedup host
| append [|inputlookup inventory.csv | rename hostname as host | fields host]
| stats count by host
| eval count=count-1
| where count=0

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

This assumes all your hosts are in your inventory

index=*
| fields host
| dedup host
| append [|inputlookup inventory.csv | rename hostname as host]
| stats count by host
| where count=1
0 Karma

kagamalai
Explorer

Thanks for your reply,

I want  the report for which are the host not deployed the universal forwarder from my inventory file

the filed i mentioned host in the csv file & upload as lookup. 

Please guide me how to get the report for the same?

 

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Under settings, there is a lookups option. There you can upload your csv, which will then be available to use with inputlookup.

0 Karma

kagamalai
Explorer

Thank you, yes I uploaded  the inventory to lookups, can use the same above query for host comparison ? 

Please clarify 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Depending on whether you have additional fields in your inventory.csv, you may want to reduce the field set to just host

index=*
| fields host
| dedup host
| append [|inputlookup inventory.csv | rename hostname as host | fields host]
| stats count by host
| where count=1
0 Karma

kagamalai
Explorer

Thank you I am getting the outputs but its coming next row each host name very difficult to separate it,   is there any way to get only not reporting or not deployed the hosts ?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

You should only be getting hosts which only appear in one place. Assuming that all the reporting hosts are in your inventory.csv, then the non-reporting hosts will have a count of 1

index=* - search all indexes (you might want to retrict this to just the indexes you are interested in
| fields host - reduce the fields to just host
| dedup host - deduplicate the hosts, i.e. only one event for each host
| append [|inputlookup inventory.csv | rename hostname as host | fields host] - append events from the inventory.csv giving the hosts the same field name
| stats count by host - count events by host assumes that the host in the index is exactly the same as the host in the inventory
| where count=1 - only keep the events where they have come from one place (the inventory)

If you are getting more hosts than you were expecting, it could be that the way the host is recorded in the event is in a different format to the way it is recorded in the lookup

0 Karma

kagamalai
Explorer

Thank you for your detailed explain, if possible to update if we found the  host name in the index the output like hostname, Count =1, if not found the hostname output like hostname, count =0 in the index based on lookups.

if possible please let me know how we can achieve it 

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
index=*
| fields host
| dedup host
| append [|inputlookup inventory.csv | rename hostname as host | fields host]
| stats count by host
| eval count=count-1
| where count=0

View solution in original post

0 Karma

kagamalai
Explorer

Thanks, but now i am getting all 0 in count column 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Yes, these are the hosts that only appear in the inventory i.e. those not in indexes - is this not what you were interested in?

0 Karma

kagamalai
Explorer

I want  differentiate  the output based on lookup ( inventory.csv) if the host is found in index mark it count = 1 if not found the host in the index mark it 0 that host based on lookup asset. 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

This is what you should be getting

The first part gets an event for each host in the index - that's what the dedup is doing for you

The append gets an event for each host in the csv whether they exist in the index or not

The stats counts the events by host so if the host exists in both the index and the csv the count will be 2 and if it only exists in the csv the count will be 1

By subtracting 1 from these counts, you will have a count of 1 if it is in the index and the csv and 0 if it is only in the csv.

0 Karma

kagamalai
Explorer

Thank you, after some time i am getting the below error what could be the problem is there any solution ? 

kagamalai_0-1622718671718.png

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Have you added inventory.csv to your environment?

0 Karma

kagamalai
Explorer

Yes I added please look the below screen shot.

 

kagamalai_0-1622724093842.png

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

What else does the job inspector tell you about what went wrong?

0 Karma

kagamalai
Explorer

I am not sure I got some error 

kagamalai_0-1622725998233.png

 

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!