I am trying to put together a search that will incorporate two fields used in a CSV file ("RoleInstance" and "Environment") and find specific Windows metrics that are kept in an environment specific index.
The CSV file would look something like this:
|------------------|-------------------|
|RoleInstance | Environment |
|------------------|-------------------|
| abc | Dev |
| def | Dev |
| ghi | Prod |
| jkl | Sandbox |
|------------------|-------------------|
The search would need to look for all the hosts listed in the CSV file that matched a specific environment that's also listed in the CSV file.
So, for example, my base search for the Dev environment would be something like this:
index=DevMetrics source="WindowsMetrics" CounterName="\\LogicalDisk(_Total)\\Free Megabytes" | table RoleInstance CounterName Average
Using this example and the table I provided, the expectation would be for the results to show the metrics for hosts "abc" and "def" only (as they belong to the "Dev" environment).
How would I go about accomplishing this? I know that I'll need to use something like an "inputlookup" to read the CSV records in, but I'm scratching my head with respect to the rest of the logic.
There are lots of ways to accomplish this.
The quickest way would be to make a lookup with your csv. If we then do:
| inputlookup | search Environment=Dev | fields RoleInstance | rename RoleInstance as host
We should get one column called host with two rows: abc and def
I'm assuming that in your base search, the name of the systems is in the host field, so that's why I renamed RoleInstance as host.
You can then turn that search into a subsearch by putting it between [ ] What that will automatically do is change the results into (essentially, but not exactly): ( host=abc OR host=def)
We can then do our base search
index=DevMetrics source="WindowsMetrics" CounterName="\\LogicalDisk(_Total)\\Free Megabytes" [ | inputlookup | search Environment=Dev | fields RoleInstance | rename RoleInstance as host ]
| table RoleInstance CounterName Average
which becomes:
index=DevMetrics source="WindowsMetrics" CounterName="\\LogicalDisk(_Total)\\Free Megabytes" ( host=abc OR host=def )
| table RoleInstance CounterName Average
The advantage is you just need to update the lookup and you can add or remove hosts and you can use that lookup in a number of searches.
The caveat is I am typing this from basic memory so there might be slight typos; I typically like to simulate data, but in this case I don't have the time right this second, so hopefully this gets you closer to your goal.
Long term, you could do an automatic lookup on the data so you can do a search like:
index=DevMetrics source="WindowsMetrics" CounterName="\\LogicalDisk(_Total)\\Free Megabytes" Environment=Dev
Or you could add an event type or tag to denote the different environments.
Thank you for your response; I will try this out. The RoleInstance value in the lookup table may vary slightly from the RoleInstance value that's part of the source. For example: server "abc" in the lookup table may be known as "abc.com" in the source. I understand that using the square brackets around the subsearch implies an exact match (i.e. host=abc). How would I adjust this to account for the server having a FQDN? Can a wildcard be used somehow?
You're exactly right that you would introduce a wild card.
So the short answer is try:
index=DevMetrics source="WindowsMetrics" CounterName="\LogicalDisk(_Total)\Free Megabytes"
[
| inputlookup
| search Environment=Dev
| fields RoleInstance
| rename RoleInstance as host
| eval host=host."*"
]
| table host CounterName Average
Basically its just a simple eval inside the sub-search that adds a * to the end. Following the above logic of how this works that search turns into:
index=DevMetrics source="WindowsMetrics" CounterName="\\LogicalDisk(_Total)\\Free Megabytes" **( host=abc* OR host=def* )**
| table host CounterName Average
Assuming your familiar with Splunk searching you should then be comfortable saying it will match a FQDN like abc.example.com The obvious pitfall is if you have a production server like www and a development server like wwwdev, then this isn't going to work well was when searching for production www* will also match wwwdev
Did you try your base search | inputlookup <yourfilename.csv> RoleInstance OUTPUT Environment | search Environment=="Dev"
this will give you matched records between your events and lookup file and output env and filter only Dev
I will try this out. The RoleInstance in the lookup table may not be an exact match to the RoleInstance that's part of the source. In other words, server "abc" in the lookup table may be identified as "abc.com" in the source. How would I configure the search to account for this? Can I use a wildcard someplace?