How to modify my search to add a column that sums CostPerDay field for each Feature field?

[gabarrygowin]

Hello all,

First thanks for the participation in this forum, many of your older solutions have helped greatly in my success in getting Splunk into our enterprise. But I find myself stuck searching, trying, etc.

We have an existing search to show how many engineering licenses are checked out past 24 hours and display as follows:

index=unix host=torlicvlp02* source=/var/log/messages sourcetype=syslog process=AUDIT_ANSYS_USER_LICENSE | stats count by Username,AnsysFeature,AnsysDays | sort-AnsysDays 

The challenge is that we need to add a column "Cost Per Day" using the cost of the 'Feature' from the lookup we've uploaded. The lookup is called ANSYSCOST.csv and is formatted:

PRODUCT FEATURE SplunkFeatureName    GACostPerDay 

Can someone help me get the search working to add a column that sums Cost Per Day for each Feature checked out?

Regards.

[woodcock]

Like this:

index=unix host=torlicvlp02* source=/var/log/messages sourcetype=syslog process=AUDIT_ANSYS_USER_LICENSE
| bin _time span=1d
| stats count BY _time Username AnsysFeature AnsysDays
| lookup ANSYSCOST.csv FEATURE as AnsysFeature OUTPUT GACostPerDay
| stats sum(count) AS count sum(GACostPerDay) AS TotalCost BY Username AnsysFeature AnsysDays

[sbbadri · Accepted solution]

index=unix host=torlicvlp02* source=/var/log/messages sourcetype=syslog process=AUDIT_ANSYS_USER_LICENSE | lookup ANSYSCOST.csv FEATURE as AnsysFeature OUTPUTNEW PRODUCT as Product, SplunkFeatureName GACostPerDay | stats count by Username,AnsysFeature,AnsysDays GACostPerDay | rename GACostPerDay as "Cost Per Day" | sort-AnsysDays

Note: Need to use common field from lookup and events before "OUTPUTNEW" keyword

[gabarrygowin]

You guys rock!

I just realized I didn't pose a perfect question here. I also need to compute (eval?) the AnsysDays x Cost Per Day and provide a Total cost per session to Date.

Thanks for the progress, can't tell you how much this has helped.

[sbbadri]

index=unix host=torlicvlp02* source=/var/log/messages sourcetype=syslog process=AUDIT_ANSYS_USER_LICENSE | eval totalCost = AnsysDays * GACostPerDay |bucket span=1d _time | stats sum(totalCost) as "Total cost per session" by Username,AnsysFeature

I hope this helps

[gabarrygowin]

Hmm, close. The columns look good but the math isn't executing the AnsysFeature*GACostPerDay to add amount to Total cost per session.

[sbbadri]

can you post a sample event

[gabarrygowin]

Aug 3 03:50:03 torlicvlp02 AUDIT_ANSYS_USER_LICENSE: usera has been using feature ansys since: 1 days 16 hours 59 minutes 03 seconds on the hosts: XXXX.ga.com

[gabarrygowin]

So, I'm thinking the 'days' and $ wording in AnsysDays and GACostPerDay are tripping up the eval. Taking them out so that I only have digits. Will take a few before the new csv is seen.

[gabarrygowin]

Well, it's not pretty but it works!!!! Taking your helpful inputs I got the following to work:

index=unix host=torlicvlp02* source=/var/log/messages sourcetype=syslog process=AUDIT_ANSYS_USER_LICENSE | lookup ansyscost.csv FEATURE as AnsysFeature OUTPUTNEW PRODUCT as Product, SplunkFeatureName GACostPerDay | stats count by Username,AnsysFeature,AnsysDay,GACostPerDay | eval Session Cost=AnsysDay*GACostPerDay | rename GACostPerDay as "Cost Per Day" | rename Username as User | rename AnsysFeature as "ANSYS Item" | rename AnsysDay as "Days Checked Out" | sort-"Days Checked Out" | fields - count

[sbbadri]

index=unix host=torlicvlp02* source=/var/log/messages sourcetype=syslog process=AUDIT_ANSYS_USER_LICENSE | lookup ansyscost.csv FEATURE as AnsysFeature OUTPUTNEW PRODUCT as Product, SplunkFeatureName GACostPerDay | stats count by Username,AnsysFeature,AnsysDay,GACostPerDay | eval Session Cost=AnsysDay*GACostPerDay | rename GACostPerDay as "Cost Per Day" | rename GACostPerDay as "Cost Per Day", Username as User, AnsysFeature as "ANSYS Item",AnsysDay as "Days Checked Out" | sort - "Days Checked Out" | fields - count

it enough to use single rename command

[gabarrygowin]

You're absolutely correct and thanks, was eating the elephant bite by bite.

Thanks again!

[richgalloway]

This should get you started. If the same feature might be present in more than one product then you'll need a product field to narrow the lookup.

index=unix host=torlicvlp02* source=/var/log/messages sourcetype=syslog process=AUDIT_ANSYS_USER_LICENSE | stats count by Username,AnsysFeature,AnsysDays | sort-AnsysDays | lookup ANSYSCOST.csv FEATURE as AnsysFeature OUTPUT GACostPerDay | ...