Splunk Search

[USA69]

Please I need help fixing this search below. Will appreciate the help. When I run the search I don't see the results

I want the numerator for this to be "device Last Seen (q_last_seen OR cs_last_seen OR Last_Status_Time) within one to seven days AND agent has connected (last seen) in one to seven days OR device last seen (q_last_seen OR cs_last_seen OR Last_Status_Time) within eight to fifteen days AND agent connected (last seen) in eight to fifteen days. The denominator is "Total Active Devices

index=summary sourcetype=*_*station_* source="*workstation compliance summary" hostname!="*.corp" (dv_install_status IN ("In use", "In stock") OR ds="J*" OR (seen_by_cmdb="n/a" AND ds!="J*")) AND (seen_by_cs="Yes" OR seen_by_q="Yes" OR seen_by_sccm_status="Yes") AND (os=windows OR os=mac)
| eval *_host =upper(*_host) | stats latest(os) as os latest(Last_Status_time) As Last_Status_Time
by *_host
| eventstats count(eval(os="WINDOWS")) AS scope_windows_total_active_devices
count(eval(os="MAC")) AS scope_mac_total_active_devices
count as scope_total_active_devices | eval device Last Seen=case(Last_Status_Time>=relative_time(now(), "-7d@d"), "1-7 days", Last_Status_Time>=relative_time(now(), "-15d@d") AND Last_Status_Time<relative_time(now(), "-7d"), "8-15 days") | eval *_host=upper(replace(DeviceName, "\..*$", "")) join type=inner *_host [ search index="its-*-*tec-app" sourcetype="*:*:agent:reports" source="D:\\*eData\\*Agent_*.csv" earliest=-1d latest=-1m

| rename "Machine Name" as *_host
| eval *_host = upper(*_host)
| stats latest(IP) as IP latest(Version) as Version_*, latest("Last Update Received") as last_update_recieved by *_host]
| stats count(eval(os="WINDOWS")) AS windows_total_active_devices
count(eval(os="MAC")) AS mac_total_active_devices
count as total_active_devices
by scope_total_active_devices, scope_windows_total_active_devices, scope_mac_total_active_devices

| eval completness = round(total_active_devices/scope_total_active_devices * 100, 2)
| eval win_completness = round(windows_total_active_devices/scope_windows_total_active_devices * 100, 2)
| eval mac_completness = round(mac_total_active_devices/scope_mac_total_active_devices * 100, 2)

| eval win_incompletness = scope_windows_total_active_devices - windows_total_active_devices
| eval win_incompletness_perc = round(win_incompletness/scope_windows_total_active_devices * 100, 2)

| eval mac_incompletness = scope_mac_total_active_devices - mac_total_active_devices
| eval mac_incompletness_perc = round(mac_incompletness/scope_mac_total_active_devices * 100, 2)

[bowesmana]

Your construct of *_host is not valid Splunk. Firstly you cannot use wildcards on the left hand side of eval, nor can you split by *_host in these two statements.

| eval *_host =upper(*_host) 
| stats latest(os) as os latest(Last_Status_time) As Last_Status_Time
    by *_host 

You do do something like this

| foreach *_host [ eval <<FIELD>>=upper('<<FIELD>>') ]

 to uppercase all fields that end in _host, however, without seeing your data, it's difficult to know what the right solution is.

Does each event have a single XXX_host field, but XXX may be different for each of your different event type - you have a large number of search criteria. You are also using lots of leading wildcards in your search, which will make it very inefficient.

If each event has a single XXX_host, but you don't know what XXX might be, then 

| foreach *_host [ eval actual_host=coalesce(host, upper('<<FIELD>>')) ]

will get the value of that XXX_host field and assign it to a new field actual_host. The you should split by actual_host

It looks like you are treating Last_Status_time as an epoch, as you are using it in relative_time calculations. In that case, you should consider latest(Last_Status_time) and perhaps use max(Last_Status_time) which is a little bit different to latest().

This statement is not valid

| eval *_host=upper(replace(DeviceName, "\..*$", "")) join type=inner *_host 
    [ search index="its-*-*tec-app" sourcetype="*:*:agent:reports" source="D:\\*eData\\*Agent_*.csv" earliest=-1d latest=-1m 
    | rename "Machine Name" as *_host 
    | eval *_host = upper(*_host) 
    | stats latest(IP) as IP latest(Version) as Version_*, latest("Last Update Received") as last_update_recieved by *_host] 

It looks like this could be a typo, as you have missed a | before the join.

Anyway, you should not really use join unless you understand the consequences and limitations of join.

Your SPL is full of *_host, so please explain what your data looks like regarding that field.

This statement is also not valid 

| rename "Machine Name" as *_host 

You cannot rename a single field to be a wildcard field, what are you trying to do here, please give a data example.

[USA69]

You're right I did miss | before the join. The *_host is actually sat_host and not *_host

[USA69]

the search is

index=summary sourcetype=*_*station_* source="*workstation compliance summary" hostname!="*.corp" (dv_install_status IN ("In use", "In stock") OR ds="J*" OR (seen_by_cmdb="n/a" AND ds!="J*")) AND (seen_by_cs="Yes" OR seen_by_q="Yes" OR seen_by_sccm_status="Yes") AND (os=windows OR os=mac)
| eval sat_host =upper(sat_host) | stats latest(os) as os latest(Last_Status_time) As Last_Status_Time
by sat_host
| eventstats count(eval(os="WINDOWS")) AS scope_windows_total_active_devices
count(eval(os="MAC")) AS scope_mac_total_active_devices
count as scope_total_active_devices | eval device Last Seen=case(Last_Status_Time>=relative_time(now(), "-7d@d"), "1-7 days", Last_Status_Time>=relative_time(now(), "-15d@d") AND Last_Status_Time<relative_time(now(), "-7d"), "8-15 days") | eval sat_host=upper(replace(DeviceName, "\..*$", "")) join type=inner sat_host [ search index="its-*-*tec-app" sourcetype="*:*:agent:reports" source="D:\\*eData\\*Agent_*.csv" earliest=-1d latest=-1m

| rename "Machine Name" as sat_host
| eval sat_host = upper(sat_host)
| stats latest(IP) as IP latest(Version) as Version_*, latest("Last Update Received") as last_update_recieved by sat_host]
| stats count(eval(os="WINDOWS")) AS windows_total_active_devices
count(eval(os="MAC")) AS mac_total_active_devices
count as total_active_devices
by scope_total_active_devices, scope_windows_total_active_devices, scope_mac_total_active_devices

| eval completness = round(total_active_devices/scope_total_active_devices * 100, 2)
| eval win_completness = round(windows_total_active_devices/scope_windows_total_active_devices * 100, 2)
| eval mac_completness = round(mac_total_active_devices/scope_mac_total_active_devices * 100, 2)

| eval win_incompletness = scope_windows_total_active_devices - windows_total_active_devices
| eval win_incompletness_perc = round(win_incompletness/scope_windows_total_active_devices * 100, 2)

| eval mac_incompletness = scope_mac_total_active_devices - mac_total_active_devices
| eval mac_incompletness_perc = round(mac_incompletness/scope_mac_total_active_devices * 100, 2)

[PickleRick]

OK. There are several things which catch my eye right off the bat.

hostname!="*.corp"

Two things "wrong" here (I mean, the search will run but it will be terribly inefficient).

1. Inclusion is better than exclusion. Splunk searches events by search terms and only those pre-selected events are verified against field extractions. In other words, if you do

hostname=foo 

Splunk will find all events where there is a "foo" anywhere and will check which of those has "foo" in place of the hostname field. If you do

hostname!=foo

Splunk has to check every single event to see if it contains "foo" and if it does, is it in the hostname spot or not.

2. Splunk indexes terms which come from splitting the input data on breakers (spaces, tabs and such). And keeps a lexicon of those terms with entries pointing to the events containing those terms. So if you're looking for "foo" or "foo*" Splunk can find in its lexicon all entries being "foo" or starting with "foo". If you do "*foo", there is no such magic. Splunk has to scan every single event to find this string.

Another thing - eventstats is a relatively "heavy" command. I assume that since you're pulling data from some summary, you might have it already relatively well "compacted" but in a general case, be aware that it can be very memory-consuming since it needs to have whole result set to operate on.

And probably the main culprit - 

join type=inner sat_host
   [ search index="its-*-*tec-app" sourcetype="*:*:agent:reports" source="D:\\*eData\\*Agent_*.csv" earliest=-1d latest=-1m"
     | rename "Machine Name" as sat_host
     | eval sat_host = upper(sat_host)
     | stats latest(IP) as IP latest(Version) as Version_*, latest("Last Update Received") as last_update_recieved by sat_host]

1. You can't just write "as Version_*". Wasn't it supposed to be "latest(Version_*) as Version_*" (wildcard on right side of AS is allowed only as a match to left-side one).

2. More importantly - join with a raw event search usually ends in tears. Join command is usually best avoided altogether. Sometimes it's ok with a predictably fast search (like a small tstats or inputlookup). It practically never ends well with an index search. Join has a lot of limitations (result count, execution time) so it can get silently finalized and produce wrong/incomplete results without you ever knowing it.

3. You're joining on sat_host field which obviously (if your results before the join really look like what you're showing from the csv dump) does not exist in your data.

You're doing 

| stats latest(os) as os latest(Last_Status_time) As Last_Status_Time by sat_host

Which leaves you with fields:

- os

- Last_Status_Time

- sat_host

so when you arrive at

| eval sat_host=upper(replace(DeviceName, "\..*$", ""))

there is no DeviceName field in your result set.

[USA69]

what's can be used in place of join type=inner sat_host

* is mask the full names

[PickleRick]

If you want to anonymize your data, please use another character/string as placeholder. Asterisk has its usual meaning as a wildcard and thus is expected to be taken literally when encountered in such context.

That's one.

Two - The usual technique of avoiding join is appending two searches and doing stats over the combined result by common field. But. This requires planning since if you simply use the append command you can still hit the limits for subsearches and get incomplete/wrong results. You can use multisearch to fetch data from multiple searches but those searches must be streaming ones (i.e. cannot contain stats command).

Three - it still doesn't solve the problem that you're trying to create the sat_host field based on value of a non-existent field.

[bowesmana]

Please format your code using the code sample button. It's currently unreadable.

What exactly is not happening here?

Please give some indication of what results you have at various points of your search, e.g.

1. after the initial index search
2. after the first stats
3. after the join.
4. after the second stats

It's very hard to diagnose a 25 or so line of SPL without any comprehension of your data and any partial results of investigations you have made along the way.

[USA69]

1 after the initial index search

I see this fields like q_last_seen, cs_last_seen, Last_Status_Time and others

2 after the the first stats I see below results

3 after the join type=inner sat_host I see no result but if I use append in place of join

4 after the second stats

[bowesmana]

Your image does not align with the code you have provided. In your SPL There is a column showing "Last_Statu..." with a value of unknown. The SPL does not set that to "unknown", so that must be from your data, in which case, that means this statement will never work.

| eval device Last Seen=case(Last_Status_Time>=relative_time(now(), "-7d@d"), "1-7 days", Last_Status_Time>=relative_time(now(), "-15d@d") AND Last_Status_Time<relative_time(now(), "-7d"), "8-15 days") 

There is no field in your image "device Last Seen", only Week_bre...

In addition, your SPL does 

| eval sat_host =upper(sat_host) 
| stats latest(os) as os latest(Last_Status_time) As Last_Status_Time
    by sat_host 
...
| eval sat_host=upper(replace(DeviceName, "\..*$", ""))

so you are trying to change sat_host after you have already used it in the previous stats statement, which does not preserve DeviceName, so you will always end up with an empty sat_host at that point.

What should sat_host be? Should it be from DeviceName? If so, that eval should be before the stats, not after.

If you post an image, also show what SPL produced it because clearly the SPL you originally posted did not produce that image, so it makes our task more difficult trying to suggest a solution.

Is Last_Status_Time an epoch?