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

[kagamalai]

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 

[ITWhisperer]

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

[kagamalai]

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?

[ITWhisperer]

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

[kagamalai]

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

Please clarify 

[ITWhisperer]

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

[kagamalai]

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 ?

[ITWhisperer]

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

[kagamalai]

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 

[ITWhisperer · Accepted solution]

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

[kagamalai]

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

[ITWhisperer]

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?

[kagamalai]

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. 

[ITWhisperer]

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.

[kagamalai]

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

[ITWhisperer]

Have you added inventory.csv to your environment?

[kagamalai]

Yes I added please look the below screen shot.

[ITWhisperer]

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

[kagamalai]

I am not sure I got some error