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.
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
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
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
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.
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
Hmm, close. The columns look good but the math isn't executing the AnsysFeature*GACostPerDay to add amount to Total cost per session.
can you post a sample event
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
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.
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
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
You're absolutely correct and thanks, was eating the elephant bite by bite.
Thanks again!
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 | ...