How to compare hosts in Splunk to CMDB export?

[DennisVT]

I just came to the realization that this query shows "missing" when it's either missing in Splunk or exists in Splunk but not in the export:

index=_internal

| fields host

| dedup host

| eval host=lower(host)

| append [ | inputlookup Export.csv | rename Hostname as host | eval host=lower(host)]

| stats count by host

| eval count=count-1

| eval Status=if(count=0,"Missing","OK")

| sort Status

| table host Status

What I would like is to change the query to show where it's missing.

[DennisVT]

The second gives excellent results, thanks for your help

[bowesmana]

An addition to the other comments - if you ever use dedup, it probably makes sense to lower before the dedup

[PickleRick]

| tstats count by host where index=_internal
| fields host
| eval where=1
| append [ | inputlookup  Export.csv
    | rename Hostname as host
    | eval host=lower(host)
    | stats count by host
    | fields host
    | eval where=2 ]
| stats sum(where) by host
| where where!=3
| eval status=case(where=1,"index only",where=2,"lookup only",1=1,"error")

[PaulPanther · Accepted solution]

One way could be:

index=_internal
| dedup host
| eval host=lower(host)
| append
[| inputlookup Export.csv
| rename hostname as host
| eval source="Export.csv"
| eval host=lower(host)]
| chart count over host by source

Faster is the following search with metadata command:

| metadata type=hosts index=_internal 
| eval source="metadata" 
| eval host=lower(host) 
| append 
    [| inputlookup Export.csv 
    | rename hostname as host 
    | eval source="Export.csv" 
    | eval host=lower(host)] 
| chart count over host by source